小弟菜鸟一只,刚开始学存储过程,建立如下过程时没有报错,执行时报"ora-00900,无效的SQL语句"错误,SQL语句我试过没有问题,请大虾们赐教。create or replace procedure insert_records
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where saddr not in(select saddr from records);
commit;
end insert_records
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where saddr not in(select saddr from records);
commit;
end insert_records
is
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where saddr not in(select saddr from records);
commit;
end insert_records;
另外,编译通过后确定你用户有v$session的select权限。否则又报
PL/SQL: ORA-00942: 表或视图不存在
定义存储过程的语句有问题
begin之前少个is另:一个简单的insert语句就没必要用存储过程来做了
create or replace procedure insert_records
is
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where saddr not in(select saddr from records);
commit;
end insert_records;
/
最后加个/试试
records 表存不存在?
records表结构和select中列出来的是一样的吗?
有v$session的select权限吗?
records表不存在或者不在当前模式下
is
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where saddr not in(select saddr from records);
commit;
end insert_records;ora-00900:无效SQL语句
-- 必须给当前用户授予dba的权限,
-- 或者创建一个用于查询v$session的普通视图,然后把这个普通视图的权限授予普通用户-- 解决方法一:
-- 比如说:你当前是在scott用户下操作的话,先授权:
conn / as sysdba;
grant dba to scott;
conn scott/tiger;create table records as select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where 1=2; --创建表create or replace procedure insert_records
is
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from v$session where saddr not in(select saddr from records);
commit;
end insert_records;-- 解决方法二:
conn / as sysdba;
revoke dba from scott; -- 从scott用户收回dba权限
create view v_session as select * from v$session;
grant select on v_session to scott;
conn scott/tiger;
create table records as select saddr,schemaname,osuser,machine,program,action,logon_time from sys.v_session where 1=2; --创建表create or replace procedure insert_records
is
begin
insert into records select saddr,schemaname,osuser,machine,program,action,logon_time from sys.v_session where saddr not in(select saddr from records);
commit;
end insert_records;exec insert_records;select * from records;
我试过了