Re: problem retrieving data from session based temporary table

Is the temp table defined to empty on commit or end of session?

Why are you inserting into a temp table and not just getting the stuff out of the cursor?
Jim

"Paul Fell" <novaweb_at_iinet.net.au> wrote in message news:4b270a4e.0204292001.72fde5a_at_posting.google.com...
> 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) :
>
> >>>>>>>>>>>>>>>>>>>>>
> 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
Received on Tue Apr 30 2002 - 00:27:09 CDT