Home » SQL & PL/SQL » SQL & PL/SQL » Commit and Ref cursor from GLOBAL TEMPORARY TABLE (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445097] Thu, 25 February 2010 22:05 Go to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Hi,

Problem : print ref cursor gives error (ORA-08103: object no longer exists)/ and message "no rows selected" if ref cursor is based on GTT and commit is called in procedure.

CREATE GLOBAL TEMPORARY TABLE gtt_RB
(
 A number 
)
/

CREATE  TABLE t_RB
(
 A number 
)
/

CREATE  TABLE t1_RB
(
 A number 
)
/

insert into t_RB(A) values (1);
insert into t_RB(A) values (2);
insert into t_RB(A) values (3);

insert into t_RB(A) values (1);
insert into t_RB(A) values (2);
insert into t_RB(A) values (3);

insert into t1_RB(A) values (1);

commit;


create or replace procedure p_RB ( p_in in number , c out sys_refcursor)
as
begin

update t1_RB set a = p_in ;

insert into gtt_RB(A) values (1);
insert into gtt_RB(A) values (2);
insert into gtt_RB(A) values (3);

open c for select A.A 
   from t_rb A, 
  gtt_rb b , t1_rb c
   where a.a = b.a 
and  c.a = a.a ;
end ;

Var z refcursor;

exec p_RB (2,:Z);

print :Z

         A
----------
         2
         2




now with commit ;

create or replace procedure p_RB ( p_in in number, c out sys_refcursor)
as
begin

update t1_RB set a = p_in ;

insert into gtt_RB(A) values (1);
insert into gtt_RB(A) values (2);
insert into gtt_RB(A) values (3);

open c for select A.A from t_rb A, gtt_rb b , t1_rb c
where a.a = b.a 
and  c.a = a.a ;
commit;
end ;

exec p_RB (3,:Z);
print :Z

no rows selected


Questions :
1/ Once values are selected in ref cursor why it does not shows result set outside ?

2/ Are ref cursors are binded with temp tables ?

3/ if 'YES' is that should be the same case with normal tables ?

4/ Commit is necessary preserve incremental value in t1_rb how we commit with using ref cursors ?

5/ Is it a Bug?







Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445102 is a reply to message #445097] Thu, 25 February 2010 22:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> set serveroutput on size 10000
SQL> exec p_RB (2,:Z);

PL/SQL procedure successfully completed.

SQL> print :Z

         A
----------
         2
         2
         2
         2

SQL> select * from gtt_rb;

         A
----------
         1
         2
         3
         1
         2
         3

6 rows selected.

SQL> create or replace procedure p_RB ( p_in in number, c out sys_refcursor)
  2  as
  3  begin
  4
  5  update t1_RB set a = p_in ;
  6
  7  insert into gtt_RB(A) values (1);
  8  insert into gtt_RB(A) values (2);
  9  insert into gtt_RB(A) values (3);
 10
 11  open c for select A.A from t_rb A, gtt_rb b , t1_rb c
 12  where a.a = b.a
 13  and  c.a = a.a ;
 14  commit;
 15  end ;
 16  /

Procedure created.

SQL> select * from gtt_rb;

no rows selected

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','GTT_RB') from dual;

DBMS_METADATA.GET_DDL('TABLE','GTT_RB')
--------------------------------------------------------------------------------

  CREATE GLOBAL TEMPORARY TABLE "SCOTT"."GTT_RB"
   (    "A" NUMBER
   ) ON COMMIT DELETE ROWS



SQL>



SQL> CREATE GLOBAL TEMPORARY TABLE "SCOTT"."GTT_RB"
  2   (    "A" NUMBER
  3   ) ON COMMIT preserve ROWS
  4  /

Table created.

SQL> create or replace procedure p_RB ( p_in in number , c out sys_refcursor)
  2  as
  3  begin
  4
  5  update t1_RB set a = p_in ;
  6
  7  insert into gtt_RB(A) values (1);
  8  insert into gtt_RB(A) values (2);
  9  insert into gtt_RB(A) values (3);
 10
 11  open c for select A.A
 12     from t_rb A,
 13    gtt_rb b , t1_rb c
 14     where a.a = b.a
 15  and  c.a = a.a ;
 16  end ;
 17  /

Procedure created.

SQL>  exec p_RB (2,:Z);

PL/SQL procedure successfully completed.

SQL> print :Z

         A
----------
         2
         2

SQL> select * from gtt_rb;

         A
----------
         1
         2
         3

SQL> create or replace procedure p_RB ( p_in in number, c out sys_refcursor)
  2  as
  3  begin
  4
  5  update t1_RB set a = p_in ;
  6
  7  insert into gtt_RB(A) values (1);
  8  insert into gtt_RB(A) values (2);
  9  insert into gtt_RB(A) values (3);
 10
 11  open c for select A.A from t_rb A, gtt_rb b , t1_rb c
 12  where a.a = b.a
 13  and  c.a = a.a ;
 14  commit;
 15  end ;
 16  /

Procedure created.

SQL> exec p_RB (2,:Z);

PL/SQL procedure successfully completed.

SQL> select * from gtt_rb;

         A
----------
         1
         2
         3
         1
         2
         3

6 rows selected.

SQL> print :Z

         A
----------
         2
         2
         2
         2

SQL>


Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445104 is a reply to message #445097] Thu, 25 February 2010 22:35 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Thanks ramoradba

records are being selected in Refcursor before commit, so even there are recods in gtt or not should it matter ?
Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445113 is a reply to message #445104] Thu, 25 February 2010 23:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There now you see why you must NOT commit inside the procedure.
ONLY the caller knows if commit must be done not the procedure.
This is Oracle not Sybase.

Regards
Michel
Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445116 is a reply to message #445097] Thu, 25 February 2010 23:22 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ramoradba if i use preserve rows data will be available for other session ..
Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445117 is a reply to message #445116] Thu, 25 February 2010 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ramoradba if i use preserve rows data will be available for other session ..

Don't ask here.

TEST for yourself!
Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445119 is a reply to message #445116] Thu, 25 February 2010 23:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rahulvb wrote on Fri, 26 February 2010 06:22
ramoradba if i use preserve rows data will be available for other session ..

No.
I advice you to read:
Database Concepts
Chapter 5 Schema Objects
Section Overview of Tables
Paragraph Temporary Tables

Regards
Michel

Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445123 is a reply to message #445116] Fri, 26 February 2010 00:54 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
> ramoradba if i use preserve rows data will be available for other session ..

Quote:
ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session
.

TemporaryTables

sriram Smile
Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445125 is a reply to message #445104] Fri, 26 February 2010 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rahulvb wrote on Fri, 26 February 2010 05:35
Thanks ramoradba

records are being selected in Refcursor before commit, so even there are recods in gtt or not should it matter ?

NO!
The records are NOT selected into memory when you open a cursor. (How would you think it would be possible to open a cursor that will retrieve a zillion rows, if they are all selected into memory?)
icon14.gif  Re: Commit and Ref cursor from GLOBAL TEMPORARY TABLE [message #445166 is a reply to message #445097] Fri, 26 February 2010 05:12 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ON COMMIT PRESERVE ROWS Solved the problem.
Previous Topic: Analytic Functions
Next Topic: logic and cosed
Goto Forum:
  


Current Time: Sun Dec 04 02:55:18 CST 2016

Total time taken to generate the page: 0.10676 seconds