Path: news.easynews.com!easynews!feedwest.news.agis.net!aleron.net!sfo2-feed1.news.algx.net!allegiance!news-hog.berkeley.edu!ucberkeley!enews.sgi.com!sdd.hp.com!news-west.eli.net!not-for-mail
Message-ID: <3CCEB646.75159D2C@exesolutions.com>
From: Daniel Morgan <dmorgan@exesolutions.com>
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: problem retrieving data from session based temporary table
References: <4b270a4e.0204292001.72fde5a@posting.google.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 69
Date: Tue, 30 Apr 2002 15:20:40 GMT
NNTP-Posting-Host: 156.74.250.7
X-Complaints-To: yvonne.tracy@ci.seattle.wa.us
X-Trace: news-west.eli.net 1020180040 156.74.250.7 (Tue, 30 Apr 2002 09:20:40 MDT)
NNTP-Posting-Date: Tue, 30 Apr 2002 09:20:40 MDT
Organization: City of Seattle NewsReader Service
Xref: easynews comp.databases.oracle.server:145173
X-Received-Date: Tue, 30 Apr 2002 09:14:09 MST (news.easynews.com)

Paul Fell wrote:

> 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

I think what you are doing wrong is using a temp table. Unlike in SQL
Server, it almost always serves no useful purpose in Oracle.

Daniel Morgan