谢了,我现在调用DBMS_SQL,编译错误:package 'DBMS_SQL' used as variable reference 在all_objects中我找到'DBMS_SQL' 包,为什么不能用?
GRANT EXECUTE ON DBMS_SQL TO username
代码如下,是在delete到指定行数时自动commit的sql: create or replace procedure delete_commit (p_statement in varchar2,p_commit_batch_size in number default 10000) as cid integer; changed_statement varchar2(2000); finished boolean; nofrows integer; rowcnt integer; errc integer; errm varchar2(2000); begin if ( upper(p_statement) like '%WHERE%') then changed_statement:=p_statement||' and rownum < ' ||to_char(p_commit_batch_size+1); else changed_statement:=p_statement||' where rownum < ' ||to_char(p_commit_batch_size+1); end if; begin cid:=dbms_sql.open_cursor; dbms_sql.parse(cid,changed_statement,dbms_sql,native); rowcnt:=dbms_sql.last_row_count; exception when others then errc:=SQLCODE; errm:=SQLERRM; dbms_output.put_line('Error '||to_char(errc)); raise_application_error(-20000,errm); end; finished:=FALSE; while not (finished) loop begin nofrows:=dbms_sql.execute(cid); rowcnt:=dbms_sql.last_row_count; exception when others then errc:=SQLCODE; errm:=SQLERRM; dbms_output.put_line('Error '||to_char(errc)); raise_application_error(-20000,errm); end; if nofrows=0 then finished:=TRUE; else finished:=FALSE; end if; commit; end loop; begin dbms_sql.close_cursor(cid); exception when others then errc:=SQLCODE; errm:=SQLERRM; dbms_output.put_line('Error '||to_char(errc)); raise_application_error(-20000,errm); end; end; /
报错如下: 21/5 PL/SQL: Statement ignored 21/42 PLS-00226: package 'DBMS_SQL' used as variable reference
在all_objects中我找到'DBMS_SQL' 包,为什么不能用?
create or replace procedure delete_commit
(p_statement in varchar2,p_commit_batch_size in number default 10000)
as
cid integer;
changed_statement varchar2(2000);
finished boolean;
nofrows integer;
rowcnt integer;
errc integer;
errm varchar2(2000);
begin
if ( upper(p_statement) like '%WHERE%') then
changed_statement:=p_statement||' and rownum < '
||to_char(p_commit_batch_size+1);
else
changed_statement:=p_statement||' where rownum < '
||to_char(p_commit_batch_size+1);
end if;
begin
cid:=dbms_sql.open_cursor;
dbms_sql.parse(cid,changed_statement,dbms_sql,native);
rowcnt:=dbms_sql.last_row_count;
exception
when others then
errc:=SQLCODE;
errm:=SQLERRM;
dbms_output.put_line('Error '||to_char(errc));
raise_application_error(-20000,errm);
end;
finished:=FALSE;
while not (finished)
loop
begin
nofrows:=dbms_sql.execute(cid);
rowcnt:=dbms_sql.last_row_count;
exception
when others then
errc:=SQLCODE;
errm:=SQLERRM;
dbms_output.put_line('Error '||to_char(errc));
raise_application_error(-20000,errm);
end;
if nofrows=0 then
finished:=TRUE;
else
finished:=FALSE;
end if;
commit;
end loop;
begin
dbms_sql.close_cursor(cid);
exception
when others then
errc:=SQLCODE;
errm:=SQLERRM;
dbms_output.put_line('Error '||to_char(errc));
raise_application_error(-20000,errm);
end;
end;
/
21/5 PL/SQL: Statement ignored
21/42 PLS-00226: package 'DBMS_SQL' used as variable reference
应该为
dbms_sql.native
应该为
dbms_sql.native
还是dbms_sql.native(cid,changed_statement,dbms_sql)
改了后,还是没能通过。