| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Temporary table, DynamicSQL Cursor
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:
code the procedure as such:
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> create table TEMP_TABLE storage ( freelists 20 ) as select * from emp
where 1=0
2 /
Table created.
(freelists to let lots of people insert concurrently with minimum wait for space
requests)
tkyte_at_8.0> tkyte_at_8.0> tkyte_at_8.0> create or replace procedure get_result_set( p_cursor in out types.rc)
3 exec_cursor integer default dbms_sql.open_cursor; 4 rows_processed number default 0; 5 begin 6 dbms_sql.parse(exec_cursor, 7 'insert into TEMP_TABLE select * from emp where rownum <6', 8 dbms_sql.native ); 9 rows_processed := dbms_sql.execute(exec_cursor); 10 dbms_sql.close_cursor( exec_cursor ); 11 11 open p_cursor for select * from TEMP_TABLE; 12 12 delete from TEMP_TABLE;
Procedure created.
tkyte_at_8.0> tkyte_at_8.0> variable x refcursor tkyte_at_8.0> exec get_result_set( :x )
PL/SQL procedure successfully completed.
tkyte_at_8.0> print x
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 900 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES ANALYST 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
(you could use a static insert in the procedure if you want as well -- I used dynamic insert to show that you can).....
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;
/
to achieve the same result.
>...
>
>function foo(param_1, param_2) return ref cursor is
>
>o drop the temp table is any exists
>o create a new temp table (using dynamic SQL) as select bla bla bla
>based on the parameters passed to that function, the temp table created
>is different every time the function is invoked;
>o open a cursor and make it point to that temp table
>o return the cursor to that caller of our function
>
>...
>
>The problem is when foo function is invoked many time at a very high
>rate say every 3 Sec
>the package foo is in becomes 'corrupted' and my temp table
>is gone (I must have one created all the time to allow the
>function/package to compile).
>The diagnostic I draw from this is the foo function catches up on itself
>While the temp table from a previous invocation is being created another
>invocation of foo drop the table etc..
>
>Now what I am looking for a way of safely returning 'dynamic' data to a
>function caller
>at a very high rate, in a multi-threaded environment; so it could be
>
>o a solution that does not require temp table at all, but still using
>cursor,
>o have a slick way of having a separate temp table per user, per session
>per foo invocation
>o any other ???
>
>I would be greatful if any of you could help me on this
>
>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 - 07:34:05 CST
 |
 |