Re: Is this possible with "execute immediate"

Jeremy wrote:
>
> In article <444F77CB.5F13_at_yahoo.com>, Connor McDonald says...
> > Jeremy wrote:
> > >
> > > Want to call target procedure using dynamic SQL.
> > >
> > > Target procedure is defined thus:
> > >
> > > create or replace package vr is
> > > procedure form_1
> > > (p_id in number default null,
> > > p_name in util.array default util.empty_array,
> > > p_value in util.array default util.empty_array);
> > >
> > > Now I want to call this using dynamic sql e.g.
> > >
> > > execute immediate
> > > 'begin vr.procname(p_id=>1,p_name=>'||l_name||'); end;';
> > >
> > > where l_name is defined as
> > >
> > > l_name util.array;
> > >
> > > And util.array is
> > > type array is table of varchar2(4000) index by binary_integer;
> > >
> > > The pl/sql with the "execute immediate" statement doesn't compile - and
> > > I understand why - but the question is can anyone tell me how (if) this
> > > can be done? It does need to be dynamic.....
> >
> > execute immediate
> > 'begin vr.procname(p_id=>1,p_name=>:b1); end;'
> > using l_name;
> >
>
> This is my example:
>
> create or replace procedure p
> is
> l_name wd_util.array;
> begin
> begin
> execute immediate
> 'begin myproc(p_web_site_id=>3,p_name=>:b1); end;'
> using in l_name;
> exception
> when others then
> dbms_output.put_line(sqlerrm(sqlcode));
> end;
> end p;
> /
>
> Fails to compile with a
> PLS-00457: expressions have to be of SQL types
>
> If I change the data type of l_name to say varchar2 then it compiles
> fine.
>
> Is there a method ny which I might pass an array like this into a
> procedure executed via dynamic SQL?
>
> --
>
> jeremy

Can you recast wd_util.array as a nested table type (I assume its a plsql table currently).

Cheers
Connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------