Solved

Dynamic SQL - String too long

Posted on 2014-02-04
17
1,059 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
17 Comments
 
LVL 77

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 35

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 77

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 74

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
 
LVL 77

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 77

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 35

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 74

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 74

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

734 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