Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Reading
    Posts
    8

    Question Unanswered: SQL statement too large! Apparently!?

    Hi all I'm having a problem with a create table request. I am passing the request from a Windows based application through Oracle ODBC driver. I am running Oracle 9i Release 9.2.0.1.0 . The application outputs the sql commands and any errors it encounters to a file on the hard disk. The error message is 'missing left parenthesis'. When I copy the sql statement from this file and paste in a SQL Plus Worksheet the latter section of the query is highlighted in red. And I get the same error message (sometimes 'missing right parenthesis') its seems that part of the query is chopped of some reason. Does anyone know why such a thing would happen? What can I do to prevent it. The sql in question has about 1300 characters. Is there somekind of limit to the number of characters an sql statement can contain?

    thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    There is a limit on a single line of a sql statement of 1024 characters, The total max size of a sql procedure is 32K. I suspect that the sql command is being passed as one big line.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Reading
    Posts
    8
    Yes. We're passing it as one line. We build up the sql string in the application and send to Oracle via the ODBC API calls as on line/string of text. Is there a way to avoid the problem?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    There may be ways around it, but the first question is why you have to create a dynamic table? If you are looking to create and delete a work table for some procedure, I would strongly suggest you look into GTT's (Global Temporary Tables), they are specifically designed as multi-user work files. As a matter of fact, that is all they can be used for.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    As Bill said, the line size limit is lower than the command size limit. Try inserting some line breaks into the create table command.

    Code:
    Command := 'something';
    Command := Command || 'Else';
    Command := Command || chr(13) || chr(10)l
    Command := 'this on a different line';
    Ascii chars 13,10 in sequence inserted or appended into your command at appropriate points might help.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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