DBMS_SQL question. Help.
From: <alan_psb_at_my-deja.com>
Date: Thu, 23 Mar 2000 13:52:15 GMT
Message-ID: <8bd7i9$oc5$1_at_nnrp1.deja.com>
[Quoted] I developed a PL/SQL procedure as follows: create or replace procedure retrieve_count as cursor c1 is select user_name from auditing_tbl; user_name auditing_tbl.user_name%type;
cursor_name number;
string1 varchar2(1000) := '';
t number;
ignore number;
begin
open c1;
cursor_name := dbms_sql.open_cursor;
loop
fetch c1 into user_name;
string1 := 'select count(*) from (select * from ' || user_name || '_tbl minus select * from ' || user_name || _tbl2')'; dbms_sql.parse(cursor_name, string1, DBMS_SQL.V7); dbms_sql.define_column(cursor_name, 1, t); ignore := dbms_sql.execute(cursor_name); if dbms_sql.fetch_rows(cursor_name) > 0 then dbms_sql.column_value(cursor_name, 1, t); end if;
dbms_output.put_line(to_char(t));
exit when c1%notfound;
end loop;
dbms_sql.close_cursor(cursor_name);
close c1;
end;
/
The procedure is OK to compile. When I type "exec retrieve_count" in SQL*Plus, sometims the procedure cannot execute successfully (due to not [Quoted] enough space in rollback segment). I have two questions now: 1) Which tablespace is used by a SQL statement using MINUS? User temporary tablespace or rollback segment? 2) Is it possible to place "set transaction use ..." in PL/SQL procedure? If yes, refer to retrieve_count procedure, where should this [Quoted] statement be placed? I know that this statement should be placed immediately before the DML in SQL*Plus, however, I don't know where should this statement be placed in a PL/SQL procedure with dbms_sql package.
Thanks,
Alan
Date: Thu, 23 Mar 2000 13:52:15 GMT
Message-ID: <8bd7i9$oc5$1_at_nnrp1.deja.com>
[Quoted] I developed a PL/SQL procedure as follows: create or replace procedure retrieve_count as cursor c1 is select user_name from auditing_tbl; user_name auditing_tbl.user_name%type;
cursor_name number;
string1 varchar2(1000) := '';
t number;
ignore number;
begin
open c1;
cursor_name := dbms_sql.open_cursor;
loop
fetch c1 into user_name;
string1 := 'select count(*) from (select * from ' || user_name || '_tbl minus select * from ' || user_name || _tbl2')'; dbms_sql.parse(cursor_name, string1, DBMS_SQL.V7); dbms_sql.define_column(cursor_name, 1, t); ignore := dbms_sql.execute(cursor_name); if dbms_sql.fetch_rows(cursor_name) > 0 then dbms_sql.column_value(cursor_name, 1, t); end if;
dbms_output.put_line(to_char(t));
exit when c1%notfound;
end loop;
dbms_sql.close_cursor(cursor_name);
close c1;
end;
/
The procedure is OK to compile. When I type "exec retrieve_count" in SQL*Plus, sometims the procedure cannot execute successfully (due to not [Quoted] enough space in rollback segment). I have two questions now: 1) Which tablespace is used by a SQL statement using MINUS? User temporary tablespace or rollback segment? 2) Is it possible to place "set transaction use ..." in PL/SQL procedure? If yes, refer to retrieve_count procedure, where should this [Quoted] statement be placed? I know that this statement should be placed immediately before the DML in SQL*Plus, however, I don't know where should this statement be placed in a PL/SQL procedure with dbms_sql package.
Thanks,
Alan
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Mar 23 2000 - 14:52:15 CET