RE: bind variables & dbms_sql in procedure/packages

From: Lord David <DLord_at_ironmountain.co.uk>
Date: Wed, 31 Mar 2004 08:51:52 +0100
Message-ID: <64C5AA1FF5364B409CC917BC616E8C24011EAFE2@harsvr28.INTERNAL.HAYSIMS.CO.UK>

Okay, I'll bite,  

If you don't know the number of bind variables or selected columns at compile time, you can't use native dynamic sql.  

Here's an example. This one may be a bit convoluted, but we use the basic logic all the time: -  

declare

   l_cursor integer;
   l_result integer;
   l_statement varchar2(32767);
   l_parameters dbms_sql.varchar2_table;
   l_first boolean := true;
   l_view_name varchar2(30);

begin

• Add some view names l_parameters(1) := 'USER_TABLES'; l_parameters(2) := 'USER_TAB_COLUMNS'; l_statement := 'select view_name from all_views where view_name in ('; for i in l_parameters.first .. l_parameters.last loop if(l_first) then l_first := false; else l_statement := l_statement || ','; end if; l_statement := l_statement || ':b_' || trim(to_char(i)); end loop; l_statement := l_statement || ')'; l_cursor := dbms_sql.open_cursor; dbms_sql.parse(l_cursor, l_statement, dbms_sql.native); dbms_sql.define_column(l_cursor, 1, l_view_name, 30); for i in l_parameters.first .. l_parameters.last loop dbms_sql.bind_variable(l_cursor, 'b_' || trim(to_char(i)), l_parameters(i)); end loop; l_result := dbms_sql.execute(l_cursor); while(dbms_sql.fetch_rows(l_cursor) > 0) loop dbms_sql.column_value(l_cursor, 1, l_view_name); dbms_output.put_line(l_view_name); end loop; dbms_sql.close_cursor(l_cursor); end; / show errors;

--

David Lord

-----Original Message-----

From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: 30 March 2004 23:01
To: oracle-l_at_freelists.org
Subject: Re: bind variables & dbms_sql in procedure/packages

IIRC, you can't do that.

Consider the preparatory steps in using DBMS_SQL.

parse a cursor, define and\/or bind the columns and execute.

An unkown number of parameters kind of precludes the use of DBMS_SQL.

Better to use native dynamic SQL in this case IMO.

That's not to say that there isn't a convoluted method that could be used to accomplish this with DBMS_SQL, I think it *could* be done.

In the end though it would be very complex and offer no benefits over native dynamic sql, which would also execute faster.

I'm not offering any proof. Let someone else prove this is wrong, which is much easier than me proving it is correct. :)

HTH Jared

• *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** This e-mail and its attachments are intended for the author's addressee only and may be confidential.

     If they have come to you in error you must take no 
     action based on them, nor must you copy or show 
     them to anyone; please reply to this e-mail and  
     highlight the error. 

     Please note that this e-mail has been created in the
     knowledge that Internet e-mail is not a 100% secure 
     communications medium. We advise that you 
     understand and observe this lack of security when 
     e-mailing us. Steps have been taken to ensure this 
     e-mail and attachments are free from any virus, but 
     advise the recipient to ensure they are actually virus 
     free. 

     The views, opinions and judgments expressed in this 
     message are solely those of the author. The message 
     contents have not been reviewed or approved by Iron 
     Mountain.

• *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***

  ---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Wed Mar 31 2004 - 01:48:38 CST