Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unhappy Unanswered: SP2-0027: Input is too long (> 2499 characters) - line ignored

    I'm trying to run a sql-file with sqlplus. i use oracle 9.x. I have a few lines which are longer then 2500 characters. This number of lines can become a lot more. For example if you have clob-columns where reports are stored. A line with more then 2500 characters results in the next error-message.

    SP2-0027: Input is too long (> 2499 characters) - line ignored

    I typed it in in google. but no proper solution. Has anyone here an idea?

    beforehand thanks.

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: SP2-0027: Input is too long (> 2499 characters) - line ignored

    Chk if these helps you:

    http://www.dotcomsolutionsinc.net/su...027_error.html

    http://dbforums.com/arch/46/2003/6/816273
    http://dbforums.com/arch/50/2003/9/911169


    Originally posted by thepercival
    I'm trying to run a sql-file with sqlplus. i use oracle 9.x. I have a few lines which are longer then 2500 characters. This number of lines can become a lot more. For example if you have clob-columns where reports are stored. A line with more then 2500 characters results in the next error-message.

    SP2-0027: Input is too long (> 2499 characters) - line ignored

    I typed it in in google. but no proper solution. Has anyone here an idea?

    beforehand thanks.
    Oracle can do wonders !

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    already checked

    I already checked these links. I have to use sqlplusw. I can put each columndata on another line, but it still doesn't solve the problem if even one columndata has more then 2500 characters.

    greetz cdk

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: already checked

    Could you please elaborate on what you are using at sqlplusw, where is the data taken from and where are you trying to insert.

    Originally posted by thepercival
    I already checked these links. I have to use sqlplusw. I can put each columndata on another line, but it still doesn't solve the problem if even one columndata has more then 2500 characters.

    greetz cdk
    Oracle can do wonders !

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    impexp

    I execute the following commandline:

    sqlplusw "ish/ish@ultimodm.ishbv.nl" @c:\standard\sql\test5.sql

    test5.sql
    ------------------
    SET ECHO OFF;
    SET VERIFY OFF;
    COMMIT;
    update ish.job set jobtext = 'coendunninkcoendunninkcoendunninkcoendunninkcoend unninkcoendunninkcoendunninkcoendunninkcoendunnink coendunninkcoendunninkcoendunninkcoendunninkcoendu nninkcoendunninkcoendunninkcoendunninkcoendunninkc oendunninkcoendunninkcoendunninkcoendunninkcoendun ninkcoendunninkcoendunninkcoendunninkcoendunninkco endunninkcoendunninkcoendunninkcoendunninkcoendunn inkcoendunninkcoendunninkcoendunninkcoendunninkcoe ndunninkcoendunninkcoendunninkcoendunninkcoendunni nkcoendunninkcoendunninkcoendunninkcoendunninkcoen dunninkcoendunninkcoendunninkcoendunninkcoendunnin kcoendunninkcoendunninkcoendunninkcoendunninkcoend unninkcoendunninkcoendunninkcoendunninkcoendunnink coendunninkcoendunninkcoendunninkcoendunninkcoendu nninkcoendunninkcoendunninkcoendunninkcoendunninkc oendunninkcoendunninkcoendunninkcoendunninkcoendun ninkcoendunninkcoendunninkcoendunninkcoendunninkco endunninkcoendunninkcoendunninkcoendunninkcoendunn inkcoendunninkcoendunninkcoendunninkcoendunninkcoe ndunninkcoendunninkcoendunninkcoendunninkcoendunni nkcoendunninkcoendunninkcoendunninkcoendunninkcoen dunninkcoendunninkcoendunninkcoendunninkcoendunnin kcoendunninkcoendunninkcoendunninkcoendunninkcoend unninkcoendunninkcoendunninkcoendunninkcoendunnink coendunninkcoendunninkcoendunninkcoendunninkcoendu nninkcoendunninkcoendunninkcoendunninkcoendunninkc oendunninkcoendunninkcoendunninkcoendunninkcoendun ninkcoendunninkcoendunninkcoendunninkcoendunninkco endunninkcoendunninkcoendunninkcoendunninkcoendunn inkcoendunninkcoendunninkcoendunninkcoendunninkcoe ndunninkcoendunninkcoendunninkcoendunninkcoendunni nkcoendunninkcoendunninkcoendunninkcoendunninkcoen dunninkcoendunninkcoendunninkcoendunninkcoendunnin kcoendunninkcoendunninkcoendunninkcoendunninkcoend unninkcoendunninkcoendunninkcoendunninkcoendunnink coendunninkcoendunninkcoendunninkcoendunninkcoendu nninkcoendunninkcoendunninkcoendunninkcoendunninkc oendunninkcoendunninkcoendunninkcoendunninkcoendun ninkcoendunninkcoendunninkcoendunninkcoendunninkco endunninkcoendunninkcoendunninkcoendunninkcoendunn inkcoendunninkcoendunninkcoendunninkcoendunninkcoe ndunninkcoendunninkcoendunninkcoendunninkcoendunni nkcoendunninkcoendunninkcoendunninkcoendunninkcoen dunninkcoendunninkcoendunninkcoendunninkcoendunnin kcoendunninkcoendunninkcoendunninkcoendunninkcoend unninkcoendunninkcoendunninkcoendunninkcoendunnink coendunninkcoendunninkcoendunninkcoendunninkcoendu nninkcoendunninkcoendunninkcoendunninkcoendunninkc oendunninkcoendunninkcoendunninkcoendunninkcoendun ninkcoendunninkcoendunninkcoendunninkcoendunninkco endunninkcoendunninkcoendunninkco' where jobid = '00001';
    SELECT jobid, jobtext FROM ish.job WHERE jobid = '00001';
    --------------------
    the update-part is all on one line ofcourse.
    jobid is varchar2(12)
    jobtext is clob

    if you want to know more, just say it.

  6. #6
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: impexp

    Ok..let me explain my question.
    You are trying to insert data into a table. This data lenght is more than what is accepted.

    - what is the source of data? If it is a datafile, option you have is SQLLDR
    - if it is database itself, you can do insert/update without having to ue this data in your DML statement.
    - But I dont understand when you have it hard-coded in your SQL file.

    What I mean to ask is, where is it coming in you update statement in SQL file? is it manually done?
    Oracle can do wonders !

  7. #7
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    dbconverter

    We have a database converter from sybase to oracle and sybase to sqlserver. It is done automatically. the whole file is usually 10 to 15 mb.
    We work per table. first drop a table than put on the primary key. set some indexes. insert the data. and if all tables are done, all relationships will be made. if it is neccesarely I can change the c++ code.

    gr c

  8. #8
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: dbconverter

    In such a case, instead of having C++ to read the datafile and create SQL statement, try directly using SQL-LOADER to load from data-file to Oracle. Thie SQL-LDR executiong can be trigerred from your C++ code.


    Originally posted by thepercival
    We have a database converter from sybase to oracle and sybase to sqlserver. It is done automatically. the whole file is usually 10 to 15 mb.
    We work per table. first drop a table than put on the primary key. set some indexes. insert the data. and if all tables are done, all relationships will be made. if it is neccesarely I can change the c++ code.

    gr c
    Oracle can do wonders !

  9. #9
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Arrow cannot be done

    We made our own tool for our consultants. so cannot do such big change, only because a number of lines cannot be read. i just want import data through a sql-file with sqlplusw. shouldn't be that hard. I assume lots of people also do this.

    gr c

  10. #10
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: cannot be done

    I understad that point.

    There is a workaround I can think of. Though I know this is not a good way of doing it... Maybe someone else can suggest a better way to handle this..

    What I can think of is:

    Let us C++ read the entire data, substr it and update in parts.. i.e.

    update table
    set col = part1
    where key=key1;

    update table
    set col = col || part2
    where key=key1;

    .. and so on, till the last part ..

    I REPEAT - this is just a patch - a dirty workaround.

    Maybe someone with better experience here can suggest better !!!


    Originally posted by thepercival
    We made our own tool for our consultants. so cannot do such big change, only because a number of lines cannot be read. i just want import data through a sql-file with sqlplusw. shouldn't be that hard. I assume lots of people also do this.

    gr c
    Oracle can do wonders !

  11. #11
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Angry ok

    ok thanks,

    it is a dirty trick. but it will work. Too bad sqlplusw doesn't have an option or something to read more than 2500 characters per line in a sql-file.

    greetings Coen

  12. #12
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: SP2-0027: Input is too long (> 2499 characters) - line ignored

    HI,

    Open the file in a text editor and then resave the file as a different file type. In Microsoft Word, you click on File, Save As, then select file type 'text only with line breaks' and save the file. Once this is done, you can successfully run the script from SQL*Plus or Server Manager.



    Originally posted by thepercival
    I'm trying to run a sql-file with sqlplus. i use oracle 9.x. I have a few lines which are longer then 2500 characters. This number of lines can become a lot more. For example if you have clob-columns where reports are stored. A line with more then 2500 characters results in the next error-message.

    SP2-0027: Input is too long (> 2499 characters) - line ignored

    I typed it in in google. but no proper solution. Has anyone here an idea?

    beforehand thanks.
    SATHISH .

Posting Permissions

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