"Jeremy" <jeremy0505@gmail.c­om> a Г©crit dans le message de news: MPG.1eb7036f3bac3c2­e98a191@news.individ­ual.net...> |

|> | 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.....> |>

Why not just:>

vr.procname(p_id=>1­,p_name=>l_name);

"Jeremy" <jeremy0505@gmail.c­om> a йcrit dans le message de news: MPG.1eb7036f3bac3c2­e98a191@news.individ­ual.net...> |

|> | 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.....> |>

Why not just:>

vr.procname(p_id=>1­,p_name=>l_name);

So now we know you really need dynamic SQL, what does mean> "The pl/sql with the "execute immediate" statement doesn't compile"?> I never saw Oracle displaying such an error message.

Target procedure is defined thus:>

create or replace package vr is> procedure form_1> (p_idinnumber default null,> p_nameinutil.array default util.empty_array,> p_valueinutil.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.....>

Something along the line of:> declare> g_proc varchar2(40) := 'vr.procname';> g_name varchar2(40) := 'l_name';> begin> execute immediate (> ':l_procname(p_id=>­1,p_name=>:l_name'> )> using g_proc, g_name;> end;>

?>

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.....>

--> jeremy>

Oracle 9iR2> Solaris 8> Oracle HTTP Server and mod_plsql

stephan>

execute immediate> 'begin vr.procname(p_id=>1­,p_name=>:b1); end;'> using l_name;>

create pacakge AAA is> l_name util.array;> procedure set_collection(p_na­me in util.array default> util.empty_array);>­ procedure form_1(); -- this is using l_name> procedure form_2(); -- this is also using l_name> end AAA;> />

then you can call your procedures dynamically - you only need to set> l_name beforehand. but of course i don't now if this is applicable in> your situation ...>

"Jeremy" <jeremy0505@gmail.c­om> wrote in message > news:MPG.1eb988b5dc­6e5f9898a196@news.in­dividual.net...> : In article <1145949635.345961.­308770@u72g2000cwu.g­ooglegroups.com>,> : steph says...> : > as far as i know you can't pass collections as parameter of execute> : > immediate. maybe you can change your procedure(s) in a way that it> : > don't needs the parameter - with a package that handles these> : > collections and your procedure(s) accessing this package (or being part> : > of this package).

:> : I don't see how it can be done - trying to compile proc where it refers> : to an array variable in the using clause results in an oracle error ora-> : 00457 - expressions have to be of SQL types.> :> : I am a bit stuck at the moment....> :> :> : cheers> :> :> : -- > :> : jeremy>

a varray or nested table would qualify as a SQL type -- have you tried that?>

"Jeremy" <jeremy0505@gmail.c­om> wrote in message > news:MPG.1eb99ab4ef­05cb798a199@news.ind­ividual.net...> : In article <itGdnQyI2so2H9LZnZ­2dnUVZ_tmdnZ2d@comca­st.com>, Mark C. Stock> : says...> ...> : >> : > a varray or nested table would qualify as a SQL type -- have you tried > that?> : >> :> : Hi Mark,> :> : I haven't tried that - that is creating a type in the database first,> : yes?> :> : I wanted to explore all avenues which didn't require me to define a type> : in the database (as to be honest it is because I haven't been down that> : path before).> :>

it may be doable just in the package without creating a persistent type in > the database ... worth a try>

Any examples of this (new area for me) - or keywords to search oracle > doc on?>

Not sure exactly waht you're getting at here Frank - doesn't that just > literally pass the string value 'l_name' into the p_name param of > vr.procname?>

I need to find a way to pass an array param to a stored procedure using > some form of dynamic SQL. Perhaps it is not possible with "execute > immediate" - perhaps only with DBMS_SQL?>

1) It uses bind variables.> 2) it passes the value (contents) of g_name (in this example> that would be "l_name" indeed).> 3) Haven't tested with arrays, but here's your framework; pass the> array.>

In article <444F77CB.5F13@yaho­o.com>, Connor McDonald says...> > Jeremy wrote:> > >

This is my example:>

create or replace procedure p> is> l_name wd_util.array;> begin> begin> execute immediate> 'begin myproc(p_web_site_i­d=>3,p_name=>:b1); end;'> using in l_name;> exception> when others then> dbms_output.put_lin­e(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