Re: Calling table functions using named notation returns ORA-00907 (Oracle 9i)

kula_at_int.tele.dk (Kurt Laugesen) wrote in message news:<7c804feb.0205220346.73eea22d_at_posting.google.com>...
> except you cannot - as far as my tests show - use table functions i
> PL/SQL
> I just tried:
> SQL> declare
> 2 cursor cu(pSearch in varchar) is
> 3 select * from table(get_emp(pSearch));
> 4
> 5 begin
> 6
> 7 for cr in cu('A%') loop
> 8 dbms_output.put_line(cr.ename);
> 9 end loop;
> 10 end;
> 11 /
> declare

You can only use nested table collection as TABLE in sql statement.

> *
> ERROR at line 1:
> ORA-22905: cannot access rows from a non-nested table item
> ORA-06512: at line 3
> ORA-06512: at line 7
> Funny though - if you hardcode the search parameter in the cursor it
> works. This is Oracle 9.2. In 9.0.1 I coudn't make it work at all
> Regards
> Kurt
>
> billia <member_at_dbforums.com> wrote in message news:<3ceb377e$1_at_usenetgateway.com>...
> > Named notation is only available in PL/SQL, not SQL...
> >
> > create or replace function f (i number) return number as begin
> > return i; end; /
> >
> > select f(10) from dual;
> >
> > F(10)
> > ----------
> > 10
> >
> > 1 row selected.
> >
> > -----------------------------------------
> >
> > select f(i=>10) from dual;
> >
> > select f(i=>10)) from dual * ERROR at line 1: ORA-00907: missing right
> > parenthesis
> >
> > -----------------------------------------
> >
> > declare v number := f(i=>10); begin
> > dbms_output.put_line(to_char(v)); end; /
> >
> > 10
> >
> > PL/SQL procedure successfully completed.
> >
> > -----------------------------------------
> >
> >
> > Regards
> >
> > Adrian
Received on Wed May 22 2002 - 14:33:00 CDT