Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: What am I doing wrong???

    Hi Gurus,

    I am trying to extract an oracle table into a flat file but why it is just generating
    a zero size file (nothing in it)??? What am I doing wrong here? When I run the select statement itself it returns all the data.

    #!/usr/bin/ksh

    OUTPUT="TABLE_NAME.dat"

    sqlplus -s usser/password@DBINSTANCE <<EOF

    SET HEADINGS OFF
    SET PAGESIZE 50000
    SET COLSEP "|"
    SET LINESIZE 200
    SET FEEDBACK OFF

    SPOOL $FILE

    select * from TABLE_NAME

    SPOOL OFF
    EXIT
    EOF

  2. #2
    Join Date
    Apr 2014
    Posts
    2

    cannot extract data to a flat file

    Seems my earlier post did not go through…

    I am trying to extract data from oracle to a flat file via shell script. Script runs fine but it only generates an empty file (zero byte) but when I run the sql itself within database it generates the result. Can someone tell me why my output file comes empty?

    #!/bin/ksh

    FILE="myfile.txt"

    sqlplus -s usser/password@DBINSTANCE <<EOF

    SET HEADINGS OFF
    SET PAGESIZE 50000
    SET COLSEP "|"
    SET LINESIZE 200
    SET FEEDBACK OFF

    SPOOL $FILE
    select * from table

    SPOOL OFF
    EXIT
    EOF

  3. #3
    Join Date
    Dec 2013
    Posts
    14
    set echo on;
    set serveroutput on size unlimited;

    Include these so that the results are written in your spool file.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT statement requires some termination character; either a semicolon or trailing slash character on the next line
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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