Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: problem retrieving data from session based temporary table
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_IDOrder 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;
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;
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 CorpReceived on Tue Apr 30 2002 - 12:35:07 CDT