Re: Dynaminc PL/SQL problem.

A copy of this was sent to jameshalliday_at_bigfoot.com (James Halliday) (if that email address didn't require changing) On 24 May 2001 06:47:57 -0700, you wrote:

>I've trying to write a procedure that to impose different naming
>conventions for object creation in my database. The intention was to
>accept the inputs into my procedure, use these to select the name of
>the correct convention to use from a table, and use the correct
>convention procedure name and the input parameters in a dynamic PL/SQL
>statement.
>
>eg.
>
>v_TSCNstmt := 'BEGIN
>PKGTimeSlotCustomNaming.'||temp||'(:o_TSCN_ErrorCode,:o_TSCN_ErrorText,:o_name,:i_TimeSlotNumber,:i_CircuitBandwidth,:i_CircuitDef);
>END; ';
>
>the temp value (naming convention procedure) is inserted fine, but I
>am having a trouble with the parameters for the Custom Naming
>Procedure. I've tried binding them - but this doesn't seem to do any
>good. If I remove the parameters it works fine - but is useless. Any
>help would be much appreciated, especially if it could be mailed to me
>along with the news post
>
>thanks,
>
>James

not following you 100% here but if you are asking how to dynamically execute a string with bind variables it would look like this:

  execute immedaite v_tscnstatm using  OUT o_TSCN_ErrorCode,
                                       OUT o_TSCN_ErrorText,
                                       OUT o_name,                     
                                        IN i_TimeSlotNumber,
                                        IN i_CircuitBandwidth,
                                        IN i_CircuitDef;

assuming that :o is your way of saying "this is an out parameter" and :i means this is an IN parameter (and that you have plsql variables named o_tscn_errorCode and so on)

That'll be the 8i syntax. In 8.0, we'll have to use dbms_sql.parse, dbms_sql.bind_variable, dbms_sql.variable_value....

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp