Restriction for length of the sql query

[minal]

Is there any restriction for length of the sql query (size of the query) in oracle?

Any advice will be greatly appreciated

Configuration is as follows


1. Web server and oracle client on one machine,

2. Oracle database on one machine.
I am searching on the internate bou not getting any information.Please advice.
Thank you,
Minal

[malay_biswal]

Size of the SQL query, Never heard this question. I think there is no size limit, but why do u ask this? Are you going to right some SQL query which will be in GB?

[minal]

Hi ,

Actually one developer asked me about it. He wanted to know what will be the impact on memory at webserver and database server if he is increasing the length of the query as per requirement(customer requirement).

Thanks for reply.


Minal

[hrishy]

Hi

There would not be any noticeable difference on the web server or the database server.

Web server would pass thsi string to the database server and database server needs to parse it once (I dobt it will take more then a few millisonds and some bytes of ram)

regards

[PAVB]

Is there any restriction for length of the sql query (size of the query) in oracle?

I suppose it should be some physical limit... taking into consideration all SQL statements show up on v$sqltext we just have to find out if there is a limit there.

SQL statements on v$sqltext are organized in chunks -or pieces- of 64 characters each so we just have to find out how many "pieces" are allowed... "piece" column is of the NUMBER datatype which lets you store positive numbers in the range 1 x 10^-130 to 9.99...9 x 10^125 with up to 38 significant digits so... just do the math.

Good luck testing/troubleshooting such a monster

[LKBrwn_DBA]

Stored Packages Maximum size: PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.