Re: Urgent: PL/SQL with table()-Operator

Could you please post the not only the complete *exact* code but also the *exact* error message.
Most likely the hint to this problem
is 'packing this into a stored function *inside a package*' I'm not going to bet, but I'm almost sure this is the same problem I answer several times per week:
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 <indentifier name> needs to be declared'. The resolution to this problem is either - issue a direct grant
- or (in Oracle 8i, and you don't specify your version, could you please do that in the future), you can run packages with invokers rights as opposed to the default definers rights.

Regards,

Sybrand Bakker, Oracle DBA

"Ulf Heyder" <esther.ulf.heyder_at_t-online.de> wrote in message news:8rlevn$a6e$15$1_at_news.t-online.com...
> Hi,
>
> I have following problem:
>
> Using the SQL Worksheet (Oracle8i, V8.1.5) a SQL-Query like
>
> select ref(p) from persons p, table(p.childs) c
> where p.sex='male' and c.name='PETER';
>
> spends some nice and correct refs on the screen.
>
> Packing this into a PL/SQL-Stored Function inside a package and trying to
> CREATE it fails with the message that I have to declare "p.childs" (or
> something alike, this is out of my mind).
>
> To be exact the PL/SQL Code looks like:
>
> ...
> create or replace function malePersonsWithChildWithName(inname in
 varchar2)
> return person_ref_nt_type is
> person_ref_nt person_ref_nt_type;
> begin
> select ref(p) bulk collect into person_ref_nt
> from persons p, table(p.childs) c
> where p.sex='male' and c.name=inname;
>
> return person_ref_nt;
> end malePersonsWithChildWithName;
> ...
>
> Any idea how to get this going? Since this join with the table()-operator
 is
> MUCH faster than building a collection out of the desired REF(t)-s using
> for-loops or something similar I die for an idea how to get this right.
 The
> original Query is a more complicated but similar.
>
> Thanks in advance for quick answers to ulf.heyder_at_gmx.de
> Ulf
>
>
>
>
Received on Sat Oct 07 2000 - 05:29:03 CDT