Re: Temporary table, DynamicSQL Cursor

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.
> 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. Received on Tue Dec 14 1999 - 11:20:54 CST