Solved

Dynamic SQL - String too long

Posted on 2014-02-04
17
939 Views
Last Modified: 2014-02-05
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
0
Comment
Question by:devguru001
  • 5
  • 4
  • 3
  • +2
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39832381
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.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39832383
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 ;-)
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39832391
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39832393
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.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39832414
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39832457
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
0
 

Author Comment

by:devguru001
ID: 39832474
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?
0
 

Author Comment

by:devguru001
ID: 39832490
@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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39832498
>>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.
0
 

Author Comment

by:devguru001
ID: 39832503
@slightwv
So I insert 1000 rows into the table and then link to the table?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39832516
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.
0
 

Author Comment

by:devguru001
ID: 39832529
Anyone have simpler solutions or is slightwv's solution my only option?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39832534
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39832588
>>> 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
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39832613
using a collection, your code wouldn't use dynamic sql anymore


CREATE OR REPLACE FUNCTION getemployeenumberlocation(p_list IN SYS.ora_mining_varchar2_nt)
    RETURN coldynamicsearchreturntype
    PIPELINED
IS
    TYPE r_cursor IS REF CURSOR;

    searchcurse r_cursor;

    col1        NVARCHAR2(200);
    col2        NVARCHAR2(200);
    col3        NVARCHAR2(200);
    col4        NVARCHAR2(200);
    col5        NVARCHAR2(200);
    col6        NVARCHAR2(200);
BEGIN
    OPEN searchcurse FOR
        SELECT employeenumber,
               locationname,
               companynumber,
               name1,
               entityname,
               projectname
          FROM employee
         WHERE locationname IN (SELECT col1 FROM TABLE(pwcdw.getlocations(' ')))
           AND employeenumber IN (SELECT COLUMN_VALUE FROM TABLE(p_list));

    LOOP
        FETCH searchcurse
            INTO col1,
                 col2,
                 col3,
                 col4,
                 col5,
                 col6;

        EXIT WHEN searchcurse%NOTFOUND;
        PIPE ROW (objdynamicsearchreturn(
                      col1,
                      col2,
                      col3,
                      col4,
                      col5,
                      col6,
                      '',
                      '',
                      '',
                      '',
                      -1
                  ));
    END LOOP;

    CLOSE searchcurse;

    RETURN;
EXCEPTION
    WHEN OTHERS
    THEN
        RAISE_APPLICATION_ERROR(
            -20001,
               'An error was encountered - '
            || SQLCODE
            || ' -ERROR- '
            || SQLERRM
            || ' - sqlWhere: '
            || sqlwhere
            || ' - Function:pwcdw.getemployeenumberLocation '
        );
END getemployeenumberlocation;
/

Open in new window


and then invoked like this...

SELECT *
  FROM TABLE(
           pwcdw.getemployeenumberlocation(
               sys.ora_mining_varchar2_nt(
                   'emp1',
                   'emp2',
                   'emp3',
                   'emp4',
                   'emp5'
               )
           )
       )

Open in new window


note each string is only a few characters long

your function could be simplified even further with a cursor for loop


CREATE OR REPLACE FUNCTION getemployeenumberlocation(p_list IN SYS.ora_mining_varchar2_nt)
    RETURN coldynamicsearchreturntype
    PIPELINED
IS
BEGIN
    FOR x
        IN (SELECT employeenumber,
                   locationname,
                   companynumber,
                   name1,
                   entityname,
                   projectname
              FROM employee
             WHERE locationname IN (SELECT col1 FROM TABLE(pwcdw.getlocations(' ')))
               AND employeenumber IN (SELECT COLUMN_VALUE FROM TABLE(p_list)))
    LOOP
        PIPE ROW (objdynamicsearchreturn(
                      x.employeenumber,
                      x.locationname,
                      x.companynumber,
                      x.name1,
                      x.entityname,
                      x.projectname,
                      '',
                      '',
                      '',
                      '',
                      -1
                  ));
    END LOOP;

    RETURN;
EXCEPTION
    WHEN OTHERS
    THEN
        RAISE_APPLICATION_ERROR(
            -20001,
               'An error was encountered - '
            || SQLCODE
            || ' -ERROR- '
            || SQLERRM
            || ' - sqlWhere: '
            || sqlwhere
            || ' - Function:pwcdw.getemployeenumberLocation '
        );
END getemployeenumberlocation;
/

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39832664
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...
0
 

Author Comment

by:devguru001
ID: 39834966
@sdstuber: I think your solution is good since it meets my requirements of not changing too much code.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now