Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> dynamic sql question

dynamic sql question

From: kwadratus <patberg_at_gmail.com>
Date: 31 Jan 2006 02:52:03 -0800
Message-ID: <1138704723.177889.298180@o13g2000cwo.googlegroups.com>


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
Received on Tue Jan 31 2006 - 04:52:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US