Link to home
Start Free TrialLog in
Avatar of devguru001
devguru001Flag for South Africa

asked on

Dynamic SQL - String too long

Hi

My web application calls an Oracle function. The function uses dynamic SQL which is in part built by a parameter passed from the web application.
At times, the string parameter passed across is too long and I receive the following error:
[ORA-01704: string literal too long].
I tried changing the parameter type to CLOB and I still get the error.

I know I should be using bind variables but am not familiar with them and hope that one of you could assist me in editing my code to use bind variables:
My code is attached.
ExpertsQuery.sql
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I would look at creating a global temporary table.

These are special tables in Oracle that are created once just like a regular table.  The difference is:  Only the session that inserts data into them can see the data and once the session terminates the data is cleaned up automatically.

This way you don't have to have a long list.  You can insert the ids into the global temp table then perform a join or an IN with a select.  The SQL should be much smaller.

There are examples of global temp tables in the online docs and in various places on the web.
What DB version?

Have you checked the max length of your generated SQL statement?! I suppose you're hitting the 32k limit (within PL/SQL context).
If you want to use (and probably you should use) CLOBs, then you'll have to use DBMS_SQL and change/adept your code to work with this built-in packages (quite easy, no rocket science)...

... whereas slightwv's solution is very elegant, too ;-)
Regardless of whether the variable is a CLOB, I believe that there is a limit to the length of a SQL statement.  The last number I recall is 64K (but I cannot find it in the current doc).  Also, what is the maximum number of items that could be in the in list?  In lists have a maximum number of allowed values (1000 is the number I recall but cannot find in the doc).

My suggestion, would be to have a global temporary table and load  your in list into that.  Then instead of sending in a long in list, you would send in  'AND employeeNumber in (select employeenumber from global_temporary_table)'.  That should get around both limits that you could be hitting.
You may eventually run into another issue using an IN list:  There is a maximum limit of 1000 items.  The Global Temp table doesn't have this problem.
The last number I recall is 64K (but I cannot find it in the current doc).

I saw this limit, too, but I suppose it depends on the DB version...

The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory

from

http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits003.htm#i288032
Also, the problem may not be in the function at all.
It could be in the invocation of the function.

Is the function called with a variable? or is it called with a string literal?  If the latter then the problem isn't the function, it's the invocation
Avatar of devguru001

ASKER

To those who asked, my sql query has 6920 characters.
There are currently less than 1000 items in my IN list. If I do encounter more, I will do the following:
select * from table1 where ID in (1,2,3,4,...,1000) or
ID in (1001,1002,...,2000)

Based on the comments above, can the problem be somewhere else?
@sdstuber, you may assume that the function is called as shown in my attachment. If I understand you correctly, this is with a String literal.
I will try to create a variable and then call it in my db tool, however, this is certainly not possible from my web application.
Perhaps I am not understanding the simplicity very well, but I believe creating temp tables for each of my users who access my application is a bit of overkill. I would then rather try to send multiple DB requests.. I am however hoping for a simple solution here without me changing the existing program too much.
>>but I believe creating temp tables for each of my users who access my application is a bit of overkill

You don't create temp tables for each user.  You create it once and each session uses the same table.  There is ZERO chance of data crossing over between sessions.
@slightwv
So I insert 1000 rows into the table and then link to the table?
That is probably what I would do.  It may even eliminate the need for dynamic SQL all together.

You can even pass the list in as XML and make the load of the temp table even better.
Anyone have simpler solutions or is slightwv's solution my only option?
That's the way I would do it (and the way I suggested).

My guess is that you are running into a string can only hold 4000 characters issue.  You would need to create a LOB variable on the app side and then bind that with the procedure call so that you could exceed the 4000 characters.  To me, way too much work when the global temp table should solve it all.
>>> you may assume that the function is called as shown in my attachment

Then, if that string is 4001 bytes or larger it will fail with the error you are seeing before your function even tries to execute


Another option would be to build a collection and pass that in instead of a string
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Anyone have simpler solutions or is slightwv's solution my only option?

I doubt it.
Go for glob temp tables as slightwv has suggested and try to eliminate the dyn sql stuff ;-)

ststuber's way with the help of collections is also suitable, but the larger your "in list" grows, the larger the collection(s) become(s), thus "more" memory usage. And if you find yourself uncomfortable und "unstable" with collections and their usage, just use the glob temp tables...
@sdstuber: I think your solution is good since it meets my requirements of not changing too much code.