Re: dbms_sql

hello,

In article <3485D30D.5D97_at_mit.edu>, Eleanor Shavell <ele_at_mit.edu> writes:
> hi,
> I'm trying to use dbms sql to call a procedure dynamically. The
> procecure returns a value in an 'out' variable. (I couldn't get this to
> work with a function for some reason). When I hard code the procedure
> name, it works , and
> I get the result back:
>
> ls_result varchar2(10);
> ls_block varchar2(1000);
> v_dummy integer;
> v_cursor integer;
> ls_argument varchar2(10);
>
> begin
>
> ls_argument := '12345';
> ls_result := '1';
> v_cursor:= dbms_sql.open_cursor;
>
> ls_block :=
> 'BEGIN
> MYPROC (:ls_prop,:ls_res);
> END;';
>
> dbms_sql.parse(v_cursor,ls_block,dbms_sql.v7);
> dbms_sql.bind_variable(v_cursor, ':ls_prop', ls_argument,10);
> dbms_sql.bind_variable(v_cursor, ':ls_res',ls_result,10);
>
> v_dummy:= dbms_sql.execute(v_cursor);
>
> dbms_sql.variable_value (v_cursor,':ls_res', ls_result);
>
> dbms_sql.close_cursor(v_cursor);
>
> BUT, I don't know the name of the procedure , so I want to construct the
> block
> to be something like:
> 'BEGIN
> :myproc (:ls_prop,:ls_res);
> END;';
>
> I can't get this to work.
> Any ideas?

try:

        sql_stmt := 'BEGIN ' || procname || ' (:param1, ...) and so on END;';

and compile (parse) this statement. this will work.

HTH
        peter
>
> Thanks a lot,
> Eleanor Shavell
> 'BEGIN
Received on Thu Dec 04 1997 - 00:00:00 CST