Re: Temporary table, DynamicSQL Cursor

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Dec 1999 09:23:58 -0500
Message-ID: <dd8f5sskji9pgo33nhfgv4qjjm9361epnh@4ax.com>

A copy of this was sent to jeanch_at_my-deja.com (if that email address didn't require changing) On Wed, 15 Dec 1999 14:04:26 GMT, you wrote:

>In article <881d5scor390s43n6mcktq5shen94hst6s_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>> >
>> I cannot see any other way to do this. I'd be interested to hear how
>you did
>> the trick of:
>>
>> execute( 'drop table T' );
>> execute( create table T as select' );
>> open some_cursor for select * from t;
>>
>> in one procedure.... i cannot see anyway to make this work as the
>dynamic drop
>> would try to invalidate the procedure that does the "open some_cursor
>for select
>> * from t"
>>
>I don't get what you mean when you say "the dynamic drop would try to
>invalidate the procedure that..."
>Are you saying that using a drop in a procedure can have consequences
>further down the like ?
>Could you clarify this please.
>At the moment when one invokes my foo function at a reasnable rate, it
>works fine.
>By the way the error I get when a high rate invocation of foo function
>is
>ORA-000600 internal error code [kcbgcur_3], [4], [5],[25185326],
>[14196,],[14198], [1]
>follwed by
>ORA-04068: existing state of packageds has been discarded
>

THAT is exactly what I mean. the drop invalidates anything that references the dropped object. The procedure that has "open some_cursor for select * from T" is invalidated by the dynamic drop. it must be recompiled before it can be executed.

Here is what I mean:

tkyte_at_8.0> create or replace procedure execute_immediate( sql_stmt in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
  7  
  7          dbms_output.put_line( sql_stmt );
  8          dbms_output.put_line( 'Listing invalid objects in execute
immediate' );
  9          for x in ( select * from user_objects where status <> 'VALID' )
loop
 10                  dbms_output.put_line( x.object_name );
 11          end loop;
 12          dbms_output.put_line( 'Done Listing....' );
 13  
 13      rows_processed := dbms_sql.execute(exec_cursor);
 14      dbms_sql.close_cursor( exec_cursor );

 15 end;
 16 /

Procedure created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace package types
  2  as
  3          type rc is ref cursor;

  4 end;
  5 /

Package created.

tkyte_at_8.0>
tkyte_at_8.0> drop table t;

Table dropped.

tkyte_at_8.0> create table t ( something date );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace procedure demo( p_cursor in out types.rc )   2 as
  3 begin

  4          dbms_output.put_line( 'Listing invalid objects in demo' );
  5          for x in ( select * from user_objects where status <> 'VALID' )
loop
  6                  dbms_output.put_line( x.object_name );
  7          end loop;
  8          dbms_output.put_line( 'Done Listing....' );
  9  
  9          execute_immediate( 'drop table t' );
 10          execute_immediate( 'create table t as select * from all_users where

rownum < 5' );
 11
 11 open p_cursor for select * from t;  12

 12          dbms_output.put_line( 'Listing invalid objects in demo' );
 13          for x in ( select * from user_objects where status <> 'VALID' )
loop
 14                  dbms_output.put_line( x.object_name );
 15          end loop;
 16          dbms_output.put_line( 'Done Listing....' );

 17 end;
 18 /

Procedure created.

tkyte_at_8.0> 
tkyte_at_8.0> variable x refcursor
tkyte_at_8.0> exec demo(:x)

Listing invalid objects in demo
Done Listing....
drop table t
Listing invalid objects in execute immediate DEMO
Done Listing....
create table t as select * from all_users where rownum < 5 Listing invalid objects in execute immediate DEMO
Done Listing....
Listing invalid objects in demo
DEMO
Done Listing....

PL/SQL procedure successfully completed.

tkyte_at_8.0> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 20-AUG-99
SYSTEM                                  5 20-AUG-99
DBSNMP                                 17 20-AUG-99
WVELASQU                               19 20-AUG-99

4 rows selected.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> break on object_type skip 1
tkyte_at_8.0> column status format a10
tkyte_at_8.0> select object_type, object_name, status

  2 from user_objects
  3 where status = 'INVALID'
  4 order by object_type, object_name
  5 /

OBJECT_TYPE     OBJECT_NAME                    STATUS
--------------- ------------------------------ ----------
PROCEDURE       DEMO                           INVALID

During the execute - the procedure went INVALID, it needs to be recompiled. If someone else executes it while this is executing -- it will autorecompile and Bamm -- the error.

Also, if you drop the table that someone has an open cursor on -- that'll do bad things as well.

Bottom line -- don't do the drop / create. We must use one consistent table for everyone.
1 row selected.

>Cheers
>JC
>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 15 1999 - 08:23:58 CST