Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Temporary table, DynamicSQL Cursor

Re: Temporary table, DynamicSQL Cursor

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 14 Dec 1999 13:03:47 -0500
Message-ID: <881d5scor390s43n6mcktq5shen94hst6s@4ax.com>


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

>In article <j4hc5s8mvegj7nhi3k6cnvpf0hs877rh8c_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to jeanch_at_my-deja.com
>> (if that email address didn't require changing)
>> On Tue, 14 Dec 1999 10:18:55 GMT, you wrote:
>>
>> >Folks
>> >
>> >I am fairly knew to the PL/SQL Oracle world. I face the following
>> >problem:
>> >returning a cursor from a function or stored procedure AND dynamic
>SQL
>> >AND temp table.
>> >
>> >I am using Oracle 8.0.4
>> >
>> >This problem has been discussed here before and the solution
>suggested
>> >by couple of people is the following:
>> >
>>
>> new suggestion:
>>
>> 1) create the 'temp table' once per database. Make it a permanent
>table.
>The touble with that is that before the function call, I don't know
>what columns the caller is interested in. Therefore I cannot create a
>permanent table. I have to dynamically create the temp table according
>to parameters passed to the foo function.

then create a table like:

create table T ( c1 varchar2(4000), c2 varchar2(4000), c3 ...., cN varchar2(4000) );

and use it. the calling application will always get a fixed number of columns but it will ignore the ones it does not want.

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"

>> 2) never drop the table
>Is it possible to create a table with no column data; and then one
>could add column at will and truncate the table after use ??
>> 3) use the builtin multi-versioning to do your work...
>>
>>
>> the concept here is:
>>
>> o everyone uses the same table (no more DDL in the procedure - much
>much
>> faster).
>>
>> o concurrency is not an issue -- no one will ever block on inserts
>into temp
>> table. No one will ever see anyone elses data -- never.
>>
>> o outside of the procedure TEMP_TABLE always appears empty. if the
>caller
>> commits, the delete got rid of all data. if the caller rollsback --
>the insert
>> never happened anyway.
>>
>> o the ref cursor result set is 'preordained' at the time the ref
>cursor is
>> opened. Using our read consistency -- the result set is preserved in
>the ref
>> cursor (even though you deleted all of the rows in the table). As
>long as the
>> caller of this procedure does not commit or rollback -- the result
>set will be
>> available to the ref cursor. If they do commit or rollback they
>MIGHT get
>> ORA-1555 snapshot too old if they wait too long to fetch from the
>cursor.
>>
>> You might consider truncating this table every now and again to lower
>the high
>> water mark.
>>
>> In Oracle8i, release 8.1, you will replace the "create table"
>with "create
>> global temporary table .... on commit delete rows" and you could
>recode the
>> procedure to be simply:
>>
>> create or replace procedure get_result_set( p_cursor in out types.rc )
>> is
>> begin
>> execute immediate 'insert into TEMP_TABLE select * from emp where
>rownum <6';
>> open p_cursor for select * from TEMP_TABLE;
>> end;
>> /
>>
>Thanks for your reply so far it's been really useful
>
>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 Tue Dec 14 1999 - 12:03:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US