Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Calling table functions using named notation returns ORA-00907 (Oracle 9i)
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;
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