dynamic sql question



Hi!
I'm trying to use dynamic sql to call a procedure. The procedure that
is called depends on the value of a certain variable (l_source in my
case). The called procedure has two output parameters
(l_number_inserted and l_record_message, see example below).
First, I create a string which is then used in the dynamic sql.
However, It doesn't work and I don't know how to get it right. How do I
handle the output parameters in dynamic sql? It works fine with a
procedure using only one input parameter.

Please help me out!

Wouter


Examples:

/* part that calls the procedure with the two output parameters*/
l_string := 'begin '||'stat_insert_'||l_source||'.insert_handler
(p_number_inserted => '||l_number_inserted||', p_record_message =>
'||l_record_message||'); end;';

/* l_string results in: begin stat_insert_viris.insert_handler
(p_number_inserted => 0, p_record_message => ); end;
*/


dbms_sql.parse(exec_cursor, l_string, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );

/*declaration of the called procedure*/
PROCEDURE INSERT_HANDLER
(P_NUMBER_INSERTED OUT NUMBER
,P_RECORD_MESSAGE OUT VARCHAR2
)

/* error messages after running the procedure*/

ORA-06550: line 1, column 85:
PLS-00103: Encountered the symbol ")" when expecting one of the
following:

( - + mod not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
The symbol "null" was substituted for ")" to continue.
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "VISSTAT_OWNER.STAT_IMPORT_FILE", line 289
ORA-06512: at line 1

.