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

Home -> Community -> Usenet -> c.d.o.server -> Re: ROWTYPE variable need to be passed in DBMS_SQL

Re: ROWTYPE variable need to be passed in DBMS_SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Feb 1999 13:59:08 GMT
Message-ID: <36bd491f.2302260@192.86.155.100>


A copy of this was sent to ram_at_shlden.com (if that email address didn't require changing) On Thu, 04 Feb 1999 21:13:59 GMT, you wrote:

>All Gurus, I have requirement to pass a bind_variable as ROWTYPE in dbms_sql
>the problem is dbms_sql.bind_variable does not allow ROWTYPE parameter.
>
>Is there any other way of passing a table record other than individual fields
>to a dbms_sql creating a anonymous pl/sql procedure ?
>

No -- it wouldn't help if you could as bind variables are only known to be scalar.

What it sounds like you want to do, is to pass a record to a pl/sql block to be assigned to some variable. for example, you are trying to parse a block like:

'declare

      l_rec emp%rowtype := :bind_variable;  begin

      ....
 end;'

or something like that. What I do in this case is:

SQL> create package emp_row_data as
   >      g_rec emp%rowtype;
   > end;
   > /

and then, execute code such as:

begin

    emp_row_data.g_rec := my_record;
    parse-and-execute( 'declare

                            l_rec emp%rowtype := emp_row_data.g_rec;
                        begin
                             ....
                        end;' );

end;

that is, put the complex variables into a package spec and reference them in the block rather then binding them.

>Would appreciate you help.
>
>RAM
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Feb 06 1999 - 07:59:08 CST

Original text of this message

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