From: "Ulf Heyder" Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc Subject: Re: Urgent: PL/SQL with table()-Operator Date: Tue, 10 Oct 2000 21:28:48 +0200 Organization: T-Online Lines: 59 Message-ID: <8rvqp7$ugl$02$1@news.t-online.com> References: <8rlevn$a6e$15$1@news.t-online.com> <970914435.1746.0.pluto.d4ee154e@news.demon.nl> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: news.t-online.com 971206248 02 31253 c1iGFoXSDAkLD 001010 19:30:48 X-Complaints-To: abuse@t-online.com X-Sender: 320097887223-0001@t-dialin.net X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Sybrand Bakker schrieb in im Newsbeitrag: 970914435.1746.0.pluto.d4ee154e@news.demon.nl... > Could you please post the not only the complete *exact* code but also the > *exact* error message. Like I wrote in my posting, I repeated it from my mind, means I didn't carry it home from work. Sorry. > Most likely the hint to this problem > is 'packing this into a stored function *inside a package*' This IS a stored function in a package. > I'm not going to bet, but I'm almost sure this is the same problem I answer > several times per week: what I find very nice of you :-) > Roles are not being used when compiling packages, as roles are volatile. If > you don't have direct access to the object it will result in an error > message 'identifier needs to be declared'. > The resolution to this problem is either > - issue a direct grant It's not a problem of access since I have have any grants. > - or (in Oracle 8i, and you don't specify your version, could you please do > that in the future), I wrote: > > Using the SQL Worksheet (Oracle8i, V8.1.5) a SQL-Query like > Sybrand Bakker, Oracle DBA Thanks for your help anyway. After sniffing more and more hours inside this huge pile of HTML-Doc I found a small note in the chapter about Dynamic SQL where they say, that since in PL/SQL the table-operator doesn't work Dynamic SQL is a solution to work around that problem. Why don't they put an important informations like inside the PL/SQL-documentation? *sigh* I solved my problem using EXECUTE IMMEDIATE 'SQL-Statement with table-Operator'... and/or REF CURSORS, wich can fetch multiline results from a query with table-OP. Thank and bye, Ulf Heyder, Oracle Developer, always learning ;-)