Home » SQL & PL/SQL » SQL & PL/SQL » length of SQL query
length of SQL query [message #287991] Fri, 14 December 2007 04:36 Go to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
Hi,
I have oracle 10g R2 Enterprise Edition on an Linux OS.
I'm using dynamic SQL to generate an SELECT query, my query is getting bigger due to the project requests, so my question is if anyone knows about any size limitation (number of characters, bytes or blocks ) of an SELECT statement, I've search on Google but i found nothing relating this.
Thank you!
Re: length of SQL query [message #287993 is a reply to message #287991] Fri, 14 December 2007 04:43 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

you could use DBMS_SQL.varchar2a


regards,
Re: length of SQL query [message #287998 is a reply to message #287991] Fri, 14 December 2007 04:53 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
What for should I use DBMS_SQL.varchar2a ? for the type for may variable holding the SELECT query ? if yes, then you did not understood my question, anyway i use CLOB for the type, but, the question is : Is there any limitation regarding the size of SELECT statement? does the SQL parser has any restriction about this?
Re: length of SQL query [message #288002 is a reply to message #287998] Fri, 14 December 2007 04:59 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

check this link.LINK


regards,
Re: length of SQL query [message #288034 is a reply to message #287998] Fri, 14 December 2007 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes there is a limitation of ALL PL/SQL variables to 32K.
If you want greater you have to use DBMS_SQL.varchar2a as it has been said or upgrade to 11g.

Regards
Michel
Re: length of SQL query [message #288166 is a reply to message #287998] Sat, 15 December 2007 04:33 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

you can also refer this

DBMS_SQL.VARCHAR2A


regards,
Previous Topic: sql case tool
Next Topic: Help needed
Goto Forum:
  


Current Time: Fri Dec 09 13:54:15 CST 2016

Total time taken to generate the page: 0.06770 seconds