Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Calling table functions using named notation returns ORA-00907 (Oracle 9i)

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

From: Kurt Laugesen <kula_at_int.tele.dk>
Date: 23 May 2002 13:28:28 -0700
Message-ID: <7c804feb.0205231228.34c899a1@posting.google.com>


That is not the whole truth.
Take this example
create or replace type EMP_T as object
(EMPNO number,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9)
);
/
create or replace type EMP_TAB_t as table of EMP_T; /

create or replace function get_emp(pIn varchar2) return  emp_tab_t pipelined is
aRow emp_t;
cursor cu is
SELECT empno, ename, job from emp where ename like pIn; begin
 for cr in cu loop
   aRow := emp_t(cr.empno, cr.ename, cr.job);    pipe row (aRow);
 end loop;
 return;
end;
/
SQL> set serverout on
SQL> declare
  2 cursor cu is
  3 select * from table(get_emp('A%'));   4
  5 begin

  6      for cr in cu loop
  7       dbms_output.put_line(cr.ename);
  8      end loop;

  9 end;
 10 /
ALLEN
ADAMS as you see it works fine if you hardcode the search criteria in the cursor, but not (as the first example shows) if you use a parametrized cursor.
Can anyone explain this difference in behaviour?

gvpatel_at_hotmail.com (Gaurang Patel) wrote in message news:<14133c52.0205221133.6035c8b7_at_posting.google.com>...
> 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 Thu May 23 2002 - 15:28:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US