Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2013
    Posts
    14

    Unanswered: ORA-00933: SQL command not properly ended

    Hi folks
    I am trying to export the table data to a text file using the following sql query :

    SELECT * FROM departments INTO OUTFILE C:/outfile.txt ;

    When the command is run on a sql editor , it gives the following error -

    Error code 933, SQL state 42000: ORA-00933: SQL command not properly ended

    Please help sort out this error.

    Thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please read the manual. There is no option "into outfile" in Oracle
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2013
    Posts
    14
    Quote Originally Posted by shammat View Post
    Please read the manual. There is no option "into outfile" in Oracle
    But I can see
    Code:
    SELECT * INTO  OUTFILE filename ... FROM tablename
    do exist . I have searched for it on various sites.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Ritesh@ti View Post
    But I can see
    Code:
    SELECT * INTO  OUTFILE filename ... FROM tablename
    do exist . I have searched for it on various sites.
    No it doesn't.

    Don't look in "various sites", read the manual: http://docs.oracle.com/cd/B28359_01/...2.htm#i2065646
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Mar 2013
    Posts
    14
    okay & thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "INTO OUTFILE" is MySQL, not Oracle.

  7. #7
    Join Date
    Mar 2013
    Posts
    14
    Thanks LittleFoot for your concern

    But could you please tell me what's wrong in it when I try to execute the following sql command :

    Code:
    SPOOL C:/outfile.txt CREATE
    select * from hr.departments;
    SPOOL off
    I am getting following error :

    Error code 900, SQL state 42000: ORA-00900: invalid SQL statement

    Line 1, column 1

    Error code 900, SQL state 42000: ORA-00900: invalid SQL statement

    Line 3, column 1

    Execution finished after 0 s, 2 error(s) occurred.

    I am logged in as admin.
    I have tried a lot to solve it , but ended up hopeless .
    please help

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try replacing the / in your pathname with a \ and see if that fixes the problems. If you are running on a Windows machine, the use of a slash will confuse things!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    SPOOL is SQL*Plus command, it may not be supported in other environments (e.g. "sql editor" - is it real/official name of your tool?).

    You should explore the abilities of "sql editor" to write data to text file instead of presenting them in the common way (whichever it is).

    Alternatively, you may decide to use SQL*Plus for running Oracle SQL queries.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example (SQL*Plus, as you'd want to SPOOL the result):
    Code:
    C:\>sqlplus scott/tiger@ora10
    
    SQL*Plus: Release 11.2.0.2.0 Production on Pon Ožu 25 12:54:09 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> spool c:\temp\departments.txt
    SQL>
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> spool off;
    SQL>
    OK, let's see the result ("type" is DOS command; "host" tells SQL*Plus to run operating system command):
    Code:
    SQL> host type c:\temp\departments.txt
    SQL>
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> spool off;
    
    SQL>

  11. #11
    Join Date
    Mar 2013
    Posts
    14
    Quote Originally Posted by flyboy View Post
    SPOOL is SQL*Plus command, it may not be supported in other environments (e.g. "sql editor" - is it real/official name of your tool?).

    You should explore the abilities of "sql editor" to write data to text file instead of presenting them in the common way (whichever it is).

    Alternatively, you may decide to use SQL*Plus for running Oracle SQL queries.
    Hi Flyboy
    Im using netbeans 7.0.1 and sql editor is an inbuilt feature where i can write sqlplus (hopefully as I checked pl/sql's even simple code dint work there) codes.

    When I run the command on sql command line , I get the result correctly ; but this is not working on netbeans. Can u please help me ?

  12. #12
    Join Date
    Mar 2013
    Posts
    14
    Quote Originally Posted by Littlefoot View Post
    Here's an example (SQL*Plus, as you'd want to SPOOL the result):
    Code:
    C:\>sqlplus scott/tiger@ora10
    
    SQL*Plus: Release 11.2.0.2.0 Production on Pon Ožu 25 12:54:09 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> spool c:\temp\departments.txt
    SQL>
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> spool off;
    SQL>
    OK, let's see the result ("type" is DOS command; "host" tells SQL*Plus to run operating system command):
    Code:
    SQL> host type c:\temp\departments.txt
    SQL>
    SQL> select * from dept;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> spool off;
    
    SQL>
    Hi Littlefoot
    These commands are working very fine on OS command line or SQL command line. I got the output file. One thing I noted that the indentation is not maintained among filed values i.e the columns are not aligned well , they are jumbled. I am attaching the output file here.

    While these are not working when I'm trying to execute them on the sql editor of NetBeans 7.0.1. I have tried to search for the compatibility of sql*plus with NetBeans, but I dint find any result.

    Also I tried to execute it using java code on Netbeans IDE as :
    Code:
    stmt.executeQuery("SPOOL \""  + filename );
               stmt.executeQuery("select * from  \"" +tablename + ";\"");
               stmt.executeQuery("spool off;");
    but the output shows java.sql.SQLException: ORA-00900: invalid SQL statement

    Please help
    Attached Files Attached Files

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    SPOOL is a SQL*Plus command and thus can only be run in SQL*Plus
    It is NOT a regular SQL statement.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Ritesh@ti View Post
    indentation is not maintained among filed values i.e the columns are not aligned well , they are jumbled.
    Where? TXT document you attached seems OK to me.

  15. #15
    Join Date
    Mar 2013
    Posts
    14
    Quote Originally Posted by Littlefoot View Post
    Where? TXT document you attached seems OK to me.
    I mean that values in the rows are not aligned well vertically under the specific column names.

Tags for this Thread

Posting Permissions

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