Home » SQL & PL/SQL » SQL & PL/SQL » array version of dbms_sql (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
array version of dbms_sql [message #407219] Tue, 09 June 2009 02:54 Go to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
Hi

I would like to create a query dynamically as

dbms_sql.parse(c,'SELECT :xfields from :xtable where xwhere_clause',dbms_sql.native);

As i see from dbms_sql.bind_array it looks i can only bind variables as a varchar table but here i have fields as varchar2. Can i bind varchar2 instead of varchar table?

Thanks in advance.
Re: array version of dbms_sql [message #407223 is a reply to message #407219] Tue, 09 June 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot bind an object name (table or field).
You have to concatenate it in the string.

Regards
Michel
Re: array version of dbms_sql [message #407234 is a reply to message #407223] Tue, 09 June 2009 04:07 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member

As I mentioned on my previous post when i concatanete dbms_sql.parse gives me an error as buffer too small. and i cannot execute the package. you advised to use array version of dbms_sql and we see that its not the solution. Therefore how can i execute a package as a string ? I have to do it as a string.

Thanks alot.
Re: array version of dbms_sql [message #407237 is a reply to message #407234] Tue, 09 June 2009 04:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you have misunderstood what @Michel said.

He was pointing out the error in your code:
dbms_sql.parse(c,'SELECT :xfields from :xtable where :xwhere_clause',dbms_sql.native); 

You may not bind column names or table names as bind variables.
I strongly suspect that you can't bind an entire where clause like that either.
Instead you need to concatenate the values into the sring before you call Dbms_Sql.Parse.

In addition, when I look at Dbms_Sql.Bind_Array it looks like it takes a table of varchar2(2000)
Previous Topic: Count( )
Next Topic: SQL & PL/SQL (merged 2)
Goto Forum:
  


Current Time: Sat Dec 03 01:22:48 CST 2016

Total time taken to generate the page: 0.08027 seconds