Re: DBMS_SQL question. Help.

From: <michael_bialik_at_my-deja.com>
Date: Thu, 23 Mar 2000 20:43:14 GMT
Message-ID: <8bdvku$kfc$1_at_nnrp1.deja.com>


Hi.

  1. Which tablespace is used by a SQL statement using MINUS? User temporary tablespace or rollback segment?

   Both.
   MINUS implies sort, so temporary tablespace is used.    Rollback segment is used to supply your routine read-consistent    data ( Probably you get procedure "bumps" when relatively high    number of concurrent users/processes updating your DB and/or    you have a lot of data in some <user>_tbl/tbl2 tables.

2) Is it possible to place "set transaction use ..." in PL/SQL

   procedure?
   Yes. Use Oracle supplied package DBMS_TRANSACTION to do it.    Insert it before "OPEN c1;" line.
   On the other hand there is another solution:    Close c1 cursor every n users and re-open it again:

   DECLARE

     start_id      auditing_tbl.user_name%type;
     neof          BOOLEAN := TRUE;
     cursor c1 ( uid auditing_tbl.user_name%type ) is
        select user_name from auditing_tbl
        WHERE username > uid
        ORDER BY username;
     user_name     auditing_tbl.user_name%type;
     i             NUMBER;
   BEGIN
     neof := TRUE;
     start_id := '0';
     WHILE neof LOOP
       i := 0;
       OPEN c1 ( start_id );
       FETCH c1 INTO user_name;
       WHILE c1%FOUND LOOP
         i := i + 1;
         /*
         || Insert your dynamic SQL here
         */
         EXIT WHEN i = 10;
         FETCH c1 INTO user_name;
       END LOOP;
       CLOSE c1;
       IF i = 10 THEN
         start_id := user_name;
       ELSE
         neof := FALSE;
       END IF;
     END LOOP;
     CLOSE c1;

   END;    It closes and re-opens main cursor for each group of 10 users,    so the necessary size of rollback is going to be smaller.

  HTH. Michael.
In article <8bd7i9$oc5$1_at_nnrp1.deja.com>, alan_psb_at_my-deja.com wrote:
> 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
> 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
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 23 2000 - 21:43:14 CET

Original text of this message