problem retrieving data from session based temporary table

From: Paul Fell <novaweb_at_iinet.net.au>
Date: 29 Apr 2002 21:01:37 -0700
Message-ID: <4b270a4e.0204292001.72fde5a@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 Mon Apr 29 2002 - 23:01:37 CDT