This discussion is archived
3 Replies Latest reply: Jun 8, 2011 7:02 PM by 858930 RSS

SP2-0027: Input is too long (> 2499 characters)"  Alternatives

858930 Newbie
Currently Being Moderated
The story is like this. I have a bunch of insert statement. and currently doing @importable1.sql AT sqlplus


importable1.sql
INSERT INTO T_CIMS_TEMPLATE (ID, INFO) Values (1, 'SOME VERY LONG STRING THAT MAKES ..................THIS LINE MORE THAN 2500 LENGTH' );
------------------------------------------

I have done some research.

Some of the suggestion 4 solutions are
1. Break the line manually to become <2500

INSERT INTO T_CIMS_TEMPLATE (ID, INFO) [break the line at here]
Values (1, 'SOME VERY LONG STRING THAT MAKES ..................THIS LINE MORE THAN 2500 LENGTH' );

2. Use SQL*Loader

3. insert T_CIMS_TEMPLATE values ('string < 2000 chars' ||
'another piece of the string ' ||
'another' ......

4. Insert into T_CIMS_TEMPLATE... then UPDATE t_CIMS_TEMPLATE

5. Use SQL developer to execute the importable1.sql

Problem is must i have a lot of insert script and it cause too much man power to do a manual job.

I have tried SQL Developer 3. It hangs on large tables.

Is there an alternative ( beside sqlplus ) to overcome this limitation of 2500 lines?

Thank you very much for ur remark and time.
  • 1. Re: SP2-0027: Input is too long (> 2499 characters)"  Alternatives
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    855927 wrote:
    The story is like this.
    That's part of the story. What's the bigger picture? Are you generating INSERT statements in database A, so that you can replicate the data in database B? There are lots of ways to do that without generating INSERT statements. Database links, data pump (or the older exp and imp utilities) come to mind right away.
    I have a bunch of insert statement. and currently doing @importable1.sql AT sqlplus
    How are these INSERT statements being created?
    importable1.sql
    INSERT INTO T_CIMS_TEMPLATE (ID, INFO) Values (1, 'SOME VERY LONG STRING THAT MAKES ..................THIS LINE MORE THAN 2500 LENGTH' );
    ------------------------------------------

    I have done some research.

    Some of the suggestion 4 solutions are
    1. Break the line manually to become <2500

    INSERT INTO T_CIMS_TEMPLATE (ID, INFO) [break the line at here]
    Values (1, 'SOME VERY LONG STRING THAT MAKES ..................THIS LINE MORE THAN 2500 LENGTH' );

    2. Use SQL*Loader

    3. insert T_CIMS_TEMPLATE values ('string < 2000 chars' ||
    'another piece of the string ' ||
    'another' ......

    4. Insert into T_CIMS_TEMPLATE... then UPDATE t_CIMS_TEMPLATE

    5. Use SQL developer to execute the importable1.sql

    Problem is must i have a lot of insert script and it cause too much man power to do a manual job.

    I have tried SQL Developer 3. It hangs on large tables.
    What exactly happens? Can you post a test script that people can run and re-create the problem?
    This might be a reason why you can't use solution 5. What ahppens when you try solutions 1, 2, 3 and 4?
    Is there an alternative ( beside sqlplus ) to overcome this limitation of 2500 lines?
    Is the threshold 2500 lines , or 2500 characters on a single line?
  • 2. Re: SP2-0027: Input is too long (> 2499 characters)"  Alternatives
    858930 Newbie
    Currently Being Moderated
    Hey again Frank Kulash

    I will be more specific this time.

    That's part of the story. What's the bigger picture? Are you generating INSERT statements in database A, so that you can replicate the data in database B? There are lots of ways to do that without generating INSERT statements. Database links, data pump (or the older exp and imp utilities) come to mind right away.

    The bigger picture is i am doing a data migration from mssql 2000 to oracle 11g.
    i have done Data Transformation Services (DTS) and the SQL Developer 3's data migration tools but it's taking way too long. Hence the raw insert scripts.


    -----
    How are these INSERT statements being created?
    A Coldfusion code(just think it as PHP) to retrieve data and convert it to oracle syntax compliance and send it into a sql file. So i have roughly 200 insert files.
    -----


    What exactly happens? Can you post a test script that people can run and re-create the problem?
    This might be a reason why you can't use solution 5. What ahppens when you try solutions 1, 2, 3 and 4

    I did not try solution 1,2,3,4 and currently doing solution 5

    solution 1 - can't cater for all case ( lines might still >2500 char length ) * i will try again.

    solution 2 - SQL*Loader i need time to rnd on this as i am new to oracle. but i hope to stick on simpler way.

    solution 3 - This can`t be done as i use coldfusion to retrieve the value for the insert scripts. To check the length and separate it will be logic hell.

    solution 4 - Even crazier idea and does not cater fully

    For solution 5. i noticed that if u include files size with 60mb and execute it. SQL developer 3 will prompt an error after a while ( suspect out of memory).So i chop that file into 2 parts 30mb and another 30mb and ran it and it works fine!


    -----
    Is the threshold 2500 lines , or 2500 characters on a single line?
    2500 character on a single line.

    I show an example of 2 insert statement below

    INSERT INTO TABLEA ( COLUMN1) VALUES ( 'VALUE1'); //this has 50 character lines

    now imagine if i have a table with 100 columns with many CLOB datatype

    INSERT INTO TABLEB ( COLUMN1, ... COLUMN99 ) VALUES ( 'VALUE1'.....'VALUES99'); // this will surely be over 2500 characters

    -----


    The problem is sqlplus has limitation until 2500 characters per line. and my Insert statement has >2500

    I was hoping for a simpler solution or some settings that i am not aware of ( sorry i am totally new to oracle ) like you help me earlier with SET SQLBLANKLINES ON.
    or other some tools/method that can handle >2500. I actually just got Toad installed and playing it around

    What i am currently doing is Solution 5 but the manual work is too much. If i were to split into smaller files. i would have 100 over tables to run in SQL Developer. File by file. Open,execute & commit very taxing indeed.

    Any suggestions or comments ? I will try solution 1 again the next morning and pray it's less than 2500
  • 3. Re: SP2-0027: Input is too long (> 2499 characters)"  Alternatives
    858930 Newbie
    Currently Being Moderated
    Updates - Solution 1 fixed it for my case

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points