#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Tulsa, OK
    Posts
    90
    Rep Power
    13

    Exclamation Oracle SELECT INTO OUTFILE problem


    Hello, I am using Oracle and trying to output the returned query data to a TAB Delimited file. I tried using

    "SELECT columns FROM tables INTO OUTFILE 'dir/filename.txt' FIELDS TERMINATED BY '\t' WHERE conditions"

    But Oracle just gives the Error:
    "Warning: OCIStmtExecute: ORA-00905: missing keyword"

    Am I doing this incorrectly or does Oracle just not support it, or is there another way to send the data to a file? I am currently sending it to an outfile by looping through each row, storing it in a variable, and then writing it to a file, but it takes quite a while, since the query returns a LOT of data. Thanks in advance.

    -Chris C.
  2. #2
  3. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    14
    here is what i have done when i need to output a query to a tab delimited file. basically just use the spool command. if you really want to get crazy...put the following into a shell script:


    . /etc/profile
    ORACLE_SID=<instancename>;export ORACLE_SID
    . oraenv

    sqlplus name/pass << EOF

    SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 term off

    SPOOL /directory/name/outputfile.dat

    SELECT TO_CHAR(id) || chr(9) || name || chr(9) || type
    FROM tbl_something;

    SPOOL OFF

    EXIT;
    EOF

IMN logo majestic logo threadwatch logo seochat tools logo