Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    20

    Question Unanswered: running Statements > 1024 Chars - ORA-00933

    Hi,
    i'm using Oracle Release 8.1.7.0.0 - Production on linux and have a problem with sqlplus/ PHP-OCI8-connection.

    I have some statements which are bigger than 1024 characters. It seems that Oracle truncates everything after the first 1024 characters which lead to an ORA-00933 SQL command not properly ended- error. Is there a parameter/way to tell oracle to accept statements which are bigger than 1024 chars?

    The example is reproduceable with sqlplus and PHP. Other databases like DB2, mySQL and postgres don't seem to have such a small statement length.

    In the eample I could use a lot of inserts instead of one big insert, but what can I do If I want to insert into a varchar2(2000) where the text is bigger than 1024 chars. As the application should also run on those other databases using variable binds in the statement is no real option, as it would require a lot of changes in the application.

    Example:
    SQL> insert into ORC_Value (Field, Value, FK_Loss_hist, histtop) values (100215, NULL, 100000, 'Y'),(100216, NULL, 100000, 'Y'),(100217, NULL, 100000, 'Y'),(100218, NULL, 100000, 'Y'),(100219, NULL, 100000, 'Y'),(100220, NULL, 100000, 'Y'),(100221, NULL, 100000, 'Y'),(100222, NULL, 100000, 'Y'),(100223, NULL, 100000, 'Y'),(100224, '0', 100000, 'Y'),(100225, '0', 100000, 'Y'),(100226, NULL, 100000, 'Y'),(100227, NULL, 100000, 'Y'),(100228, NULL, 100000, 'Y'),(100229, 'http://', 100000, 'Y'),(100230, NULL, 100000, 'Y'),(100231, NULL, 100000, 'Y'),(100232, NULL, 100000, 'Y'),(100233, NULL, 100000, 'Y'),(100234, NULL, 100000, 'Y'),(100235, '5.00', 100000, 'Y'),(100237, '5.00', 100000, 'Y'),(100238, '0.00', 100000, 'Y'),(100239, '0.00', 100000, 'Y'),(100240, '0.00', 100000, 'Y'),(100241, NULL, 100000, 'Y'),(100242, '0.00', 100000, 'Y'),(100243, '0.00', 100000, 'Y'),(100244, NULL, 100000, 'Y'),(100245, '0.0', 100000, 'Y'),(100246, NULL, 100000, 'Y'),(100247, '0.00', 100000, 'Y'),(100248, '0', 100000, 'Y'),(100249, '0', 100000, 'Y'),(100250, NULL, 100000, 'Y'),(100251, NULL, 100000, 'Y'),(100252, NULL, 100000, 'Y'),(100253, '0.000', 100000, 'Y'),(100254, NULL, 100000, 'Y'),(100255, 'http://', 100000, 'Y'),(100256, NULL, 100000, 'Y'),(100257, NULL, 100000, 'Y'),(100258, NULL, 100000, 'Y'),(100259, NULL, 100000, 'Y');
    insert into ORC_Value (Field, Value, FK_Loss_hist, histtop) values (100215, NULL, 100000, 'Y'),(100216, NULL, 100000, 'Y'),(100217, NULL, 100000, 'Y'),(100218, NULL, 100000, 'Y'),(100219, NULL, 100000, 'Y'),(100220, NULL, 100000, 'Y'),(100221, NULL, 100000, 'Y'),(100222, NULL, 100000, 'Y'),(100223, NULL, 100000, 'Y'),(100224, '0', 100000, 'Y'),(100225, '0', 100000, 'Y'),(100226, NULL, 100000, 'Y'),(100227, NULL, 100000, 'Y'),(100228, NULL, 100000, 'Y'),(100229, 'http://', 100000, 'Y'),(100230, NULL, 100000, 'Y'),(100231, NULL, 100000, 'Y'),(100232, NULL, 100000, 'Y'),(100233, NULL, 100000, 'Y'),(100234, NULL, 100000, 'Y'),(100235, '5.00', 100000, 'Y'),(100237, '5.00', 100000, 'Y'),(100238, '0.00', 100000, 'Y'),(100239, '0.00', 100000, 'Y'),(100240, '0.00', 100000, 'Y'),(100241, NULL, 100000, 'Y'),(100242, '0.00', 100000, 'Y'),(100243, '0.00', 100000, 'Y'),(100244, NULL, 100000, 'Y'),(100245, '0.0', 100000, 'Y'),(100246, NULL, 100000, 'Y'),(100247, '0.00', 100000, 'Y'),(100248, '0', 100000, 'Y'),(100249, '0', 100000, 'Y'),(100250, NULL, 100000, 'Y'),(100251, NULL, 100000, 'Y'),(100252, NULL, 100000, 'Y'),(100253, '0.000', 100000, 'Y'),(100254, NULL, 100000, 'Y'),(100255, 'http://', 100000, 'Y'),(100256, NULL, 100000, 'Y'),(100257, NULL, 100000, 'Y'),(100258, NULL, 100000, 'Y'),(100259, NULL, 100000, 'Y')
    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended


    Any suggestions ?? Thx in advance.
    Jürgen

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: running Statements > 1024 Chars - ORA-00933

    It's not the statement length that is the problem, it is the syntax. Oracle does not permit inserting multiple rows with the VALUES clause like you have here. In Oracle, it has to be:

    insert into ORC_Value (Field, Value, FK_Loss_hist, histtop) values (100215, NULL, 100000, 'Y');

    insert into ORC_Value (Field, Value, FK_Loss_hist, histtop) values (100216, NULL, 100000, 'Y');

    ...

    While you are changing your application for this, you can change it to use bind variables at the same time. Writing an Oracle application without using bind variables is a seriously bad idea.

  3. #3
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    20

    Re: running Statements > 1024 Chars - ORA-00933

    Originally posted by andrewst
    It's not the statement length that is the problem, it is the syntax. Oracle does not permit inserting multiple rows with the VALUES clause like you have here. In Oracle, it has to be:

    insert into ORC_Value (Field, Value, FK_Loss_hist, histtop) values (100215, NULL, 100000, 'Y');

    insert into ORC_Value (Field, Value, FK_Loss_hist, histtop) values (100216, NULL, 100000, 'Y');
    ok thanks this works, I thought Oracle has such a feature as DB2 and MySQL. The error-msg produced was not what I expected. For such a thing I would have expected a Syntax error.

    While you are changing your application for this, you can change it to use bind variables at the same time. Writing an Oracle application without using bind variables is a seriously bad idea.
    Yes but using bind Variables for Oracle in PHP has a lot of overhead, but at the moment there isn't enough time to implement this in the correct way. Escaping of charackters is done manually, as for different db's different escaping is needed.

    Thx Jürgen

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: running Statements > 1024 Chars - ORA-00933

    Originally posted by hernst
    Yes but using bind Variables for Oracle in PHP has a lot of overhead, but at the moment there isn't enough time to implement this in the correct way. Escaping of charackters is done manually, as for different db's different escaping is needed.
    Thx Jürgen
    As long as you are aware of the MASSIVE overhead of NOT using bind variables in Oracle? Performance will suffer badly, and not just for your application - ANY user on the same Oracle instance will be affected.

    Wouldn't using bind variables remove the escaping problem anyway?

    I am in a situation myself where I support an ASP application that runs on SQL Server and Access, and now (where I came in) Oracle. All the SQL statements use literal values. I have not managed to persuade the client to let me re-write ALL the code to use bind variables, but I make a point of changing the code to use bind variables whenever I have to make other changes to a piece of code (with the client's consent). It's not that difficult once you get the hang of it - and it simplifies the hell out of the SQL statements: I'd much rather have to read (and type):

    strSQL = "SELECT a,b,c FROM t WHERE name=? AND type=? AND status=?"
    Bind( "name", strName )
    Bind( "type", strType )
    Bind( "status", strStatus )

    than:

    strSQL = "SELECT a,b,c FROM t WHERE name='" & strName & "' AND type='" & strType & "' AND status='" & strStatus & "'"

    If I WAS the client, I would insist on changing ALL the code to use bind variables!

  5. #5
    Join Date
    Nov 2009
    Posts
    2

    Thumbs up Oracle workaround for inserting multiple rows with the VALUES clause

    Even though using bind variables is recommended (and is also executed faster in the ORACLE database), there may still be cases when you may want to insert multiple rows with the VALUES clause.
    For example, the network overhead of sending each query from the application server to the Oracle server can slow down the application a lot (when they are located on different servers).

    In such a case, you may use this syntax as a workaround:

    Code:
    insert into ORC_Value (Field, Value, FK_Loss_hist, histtop)
    (select 100215, NULL, 100000, 'Y' from dual
    union
    select 100216, NULL, 100000, 'Y' from dual
    union
    select 100217, NULL, 100000, 'N' from dual
    ........
    )
    Hope this helps, although I do NOT recommend that you make a habit of regularly using this workaround in your code.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Gee, how did you manage to find that ancient thread, dating in 2003?

    Anyway: (multitable) INSERT ALL might also provide certain functionality (although the OP has probably solved that problem ages ago):
    Code:
    insert all
      into orc_value (field, fk_loss_hist, histtop) values (100215, 100000, 'Y')
      into orc_value (field, fk_loss_hist, histtop) values (100216, 100000, 'Y')
      into orc_value (field, fk_loss_hist, histtop) values (100217, 100000, 'N')
      into dept (deptno, dname, loc) values (1, 'abc', 'abc')
      into dept (deptno, dname, loc) values (2, 'def', 'def')  
    select * from dual;

  7. #7
    Join Date
    Nov 2009
    Posts
    2

    Thumbs up

    Thanks, I didn't know about the multitable insert all. I think it's actually better than my workaround.

    As for your remark that my answer will not be of any help to the original poster, (and also how did I manage to find this old post), my answer is this:
    Usually when I encounter a problem while developing an application, I immediately do a google search to see if someone else encountered the same problem, and if they found any solution.
    As it was in this case, I found this (old) thread. (Actually I didn't even look at the date of the post)
    If I don't find a solution on the forums, but I later find a solution on my own, then I consider that it's only fair that I also post my solution, so that others will benefit from it in the future.
    So my solution (and yours) is not actually a response to the OP, but is intended to help other developers that might encounter the same problem in the future (even if it will happen many years from now).

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, I'm not complaining about answering old questions! Personally, I don't see anything wrong about it. However, I was amazed with the fact that someone managed to find it and post an answer, that's all. I hope you'll participate more recent discussions, too.

Posting Permissions

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