Re: Is this possible with "execute immediate"

From: Jeremy <jeremy0505_at_gmail.com>
Date: Wed, 26 Apr 2006 15:52:34 +0100
Message-ID: <MPG.1eb99981fa62a68498a197@news.individual.net>

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

Received on Wed Apr 26 2006 - 09:52:34 CDT