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

Home -> Community -> Usenet -> c.d.o.server -> Re: problem retrieving data from session based temporary table

Re: problem retrieving data from session based temporary table

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 30 Apr 2002 10:35:07 -0700
Message-ID: <aamkkb01ol2@drn.newsguy.com>


In article <4b270a4e.0204292001.72fde5a_at_posting.google.com>, novaweb_at_iinet.net.au says...
>
>Oracle 9i.
>
>I have a procedure that returns a refcursor. Prior to this I do some
>sorting using a temporary table. When I pick out chunks of code and
>execute in SQL Plus, all works well (get data). But if I call the
>procedure in SQL Plus, I don't get any rows. Using ADO in VB I get a
>message saying object doesn't exist.
>
>Here's the block of code (I've missed out a lot eg exception
>handling) :
>

entire procedure should just be:

>execute immediate 'insert into tbltmpNewsSearch ' || psql;
>
>update tbltmpNewsSearch
>set "Group_Order" = 2
>where "Subject" <> '' or "Subject" is not null;
>

open ref_cursor for
 select DISTINCT decode(subject,NULL,1,2) As "Group_Order" ,

              "Title",
              "ID",
	     "Business_Group",
             "Subject",
             "Publish_Date",
             "Employee_ID",
             "Preferred",
             "Surname", 
             substr(a.Article,1,300) "Article"
    from     tblNews a
    left join tblLUSubject b on
             a.Subject_Code = b.Code
    inner join tblNewsDepartmentsLink d on
             a.ID = d.News_ID
    left join tblLUCorporateNewsApplic e on
             d.Business_Group_Code = e.Code
    left join tblLUDivision f on
             d.Division_Code = f.Code
    left join tblStaffDirectory c on
             a.Employee_ID = c.Employee_ID
 Order By decode(subject,NULL,1,2), "Publish_Date" DESC, "Title";

Period,

NO dynamic sql (where none is needed)...

NO temporary table (where none is needed)...

NO commit -- you do NOT want transaction logic in a query, transactions are good, they are not evil. You should let the client control the transactional logic.

If you can do it in a sql statement DO IT.

I can show this does not work even in sqlplus -- you have the global temporary table defined as "on commit delete rows". When you commit -- bamm -- it is all gone (truncated) object no longer exists:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create global temporary table t ( x int ) on commit delete rows;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x refcursor
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> begin
  2          insert into t select user_id from all_users;
  3          open :x for select * from t;
  4          commit;

  5 end;
  6 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> print x ERROR:
ORA-08103: object no longer exists

no rows selected

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create global temporary table t ( x int ) on commit PRESERVE rows;

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x refcursor
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> begin
  2          insert into t select user_id from all_users;
  3          open :x for select * from t;
  4          commit;

  5 end;
  6 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> print x

         X


         0
         5
        11
       373
       364

.....

>>>>>>>>>>>>>>>>>>>>>>
>pSQL :=
>'select DISTINCT 1 As "Group_Order" ,
> "Title",
> "ID",
> "Business_Group",
> "Subject",
> "Publish_Date",
> "Employee_ID",
> "Preferred",
> "Surname",
> substr(a.Article,1,300) "Article"
> from tblNews a
> left join tblLUSubject b on
> a.Subject_Code = b.Code
> inner join tblNewsDepartmentsLink d on
> a.ID = d.News_ID
> left join tblLUCorporateNewsApplic e on
> d.Business_Group_Code = e.Code
> left join tblLUDivision f on
> d.Division_Code = f.Code
> left join tblStaffDirectory c on
> a.Employee_ID = c.Employee_ID';
>
>execute immediate 'insert into tbltmpNewsSearch ' || psql;
>
>update tbltmpNewsSearch
>set "Group_Order" = 2
>where "Subject" <> '' or "Subject" is not null;
>
>open pCursor for --this works (no need to use fetch with refcursors)
>select "Title",
> "ID",
> "Business_Group",
> "Subject",
> "Publish_Date",
> "Employee_ID",
> "Preferred",
> "Surname",
> "Article"
> from tbltmpNewsSearch
> Order By "Group_Order", "Publish_Date" DESC, "Title";
>
>commit; --empties table
>
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>What am I doing wrong?
>
>Thanks,
>Paul

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Tue Apr 30 2002 - 12:35:07 CDT

Original text of this message

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