Dynamic SQL and the ORA-01489: result of string concatenation is too long
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Dynamic SQL and the ORA-01489: result of string concatenation is too long

Hybrid View

  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Question Dynamic SQL and the ORA-01489: result of string concatenation is too long

    I have a database instance that is nothing but a linked server place.
    In it, I can do something like this...

    select acctkey,acctno from imsv7.account@HVDEV.world
    or
    select acctkey,acctno from imsv7.account@HVPRD.world

    Each time I substitute the Database environment I want to query out of.

    The problem I have occurs when the SQL that I build is too great in length.

    Here is my little template Stored Procedure (NOTE: I would be adding more fields to pass into the call for the where clause):

    CREATE OR REPLACE PROCEDURE IMSV7.Template_LinkedServer2
    (ORACLE_DB_ENVIRONMENT IN varchar2
    ,p_recordset1 OUT SYS_REFCURSOR)
    AS
    hold_query varchar2(2000);
    BEGIN
    hold_query := 'select acctkey,acctno from imsv7.account@'
    || oracle_db_environment|| '.world';
    OPEN p_recordset1 FOR hold_query;
    END Template_LinkedServer2;
    /

    When I try to increase this varchar2 past 4000, I get the:
    ORA-01489: result of string concatenation is too long
    this comes with the ever so helpful instruction to make my string shorter.

    I can't! I have a query that is 7000 characters long. So I tried blobs and clobs, and nothing seems to make peace with Oracle.

    I tried putting my SQL in a function in each database environment that returned a cursor so that I could do something like this in each environment..

    CREATE OR REPLACE FUNCTION IMSV7.fctn_RETURN_CURSOR RETURN SYS_REFCURSOR
    AS l_cursor SYS_REFCURSOR;
    BEGIN
    OPEN l_cursor FOR select cast(ACCTKEY as INT) as ACCTKEY, cast(trunc(STOPDTTM) as DATE) as STOPDTTM, cast(trunc(STARTDTTM) as DATE) as STARTDTTM
    from IMSV7.acctsrv;
    RETURN l_cursor;
    END;
    /

    Then from the linked environment, just call the function from a select statement. That was a 6 hour trap that ended up fruitless as my DBA said it was impossible. Something about my call was calling the function in the target database but that the target database function ended up calling a table in the linked database, which of course wasn't there.

    Is there a way for me to do this and get huge query strings to run against all db environments that I link to?

    Thanks for your help,

    Keith

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    VARCHAR2 datatype max size is 4,000 bytes.

    If you really have queries larger than 4000 characters I would suggest to rethink your strategy.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2007
    Posts
    2

    That's why I am here.

    Do you have a suggestion?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Yes. Work it out with your DBA.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966
    Have you tried creating views in each database where you have a db link with?

    While the limit for a VARCHAR2 in PL/SQL is 32767, the limit for a VARCHAR2 in SQL is only 4000. When you store the query in PL/SQL you don't have a problem, but when you run it the SQL engine takes over, and you find the limit for query length is 4000. But creating a view will hopefully help you to make the query much smaller.

    SQL> set serveroutput on

    declare
    v_varchar2 varchar2(32767);
    begin
    v_varchar2 := RPAD('Itworks', 8000, '!');
    dbms_output.put_line(substr(v_varchar2, 1, 254));
    end;
    /

    create table delme ( c1 varchar(4001));

    SQL> SQL> 2 3 4 5 6 7 Itworks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!

    PL/SQL procedure successfully completed.

    SQL> SQL> create table delme ( c1 varchar(4001))
    *
    ERROR at line 1:
    ORA-00910: specified length too long for its datatype
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width