Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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 1Received on Tue Jan 31 2006 - 04:52:03 CST