#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    1
    Rep Power
    0

    Oracle sqlplus query output into file


    Hello,

    How can I put an Oracle sqlplus query output into file?

    SQL> select myfield from userDB where IMSI like '22803%' and rownum < 11;

    I would like to put the output in a file, say '/tmp/mydata.dat'

    Thanks for your help.

    phil
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    you can use the SPOOL command to save the output into file and send it directly to the printer, for file follow the instruction:

    SQL>SPOOL /tmp/mydata.dat

    SQL>select myfield from userDB where IMSI like '22803%' and rownum < 11;

    SQL>SPOOL OFF

    now go to specified location and open the file, if you find anything garbage , remove it.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    8
    Rep Power
    0
    @shafique

    But I can't find the folder tmp as you mentioned there in my work.

    How can I save my output file in folder I need?

    [I am a novice in database. Hope you can keep your patience to answer my silly questions for some days ]

    Originally Posted by shafique
    you can use the SPOOL command to save the output into file and send it directly to the printer, for file follow the instruction:

    SQL>SPOOL /tmp/mydata.dat

    SQL>select myfield from userDB where IMSI like '22803%' and rownum < 11;

    SQL>SPOOL OFF

    now go to specified location and open the file, if you find anything garbage , remove it.
  6. #4
  7. Meatball Surgeon
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 2004
    Location
    Elbow deep in code
    Posts
    2,056
    Rep Power
    1321
    well, assuming the tmp folding exists (it does, right?) I find sqlplus has some issues with folders names (those with spaces really mess it up) As a test, try having it put the file in the root of your c drive.
    Three gigs for the secretaries fair
    Seven gigs for the system source
    Nine gigs for the coders in smoky lairs
    One disk to rule them all, one disk to bind them
    One disk to hold the files, and in the darkness grind'em
    ---------------------------------------------------
    It is by caffeine alone that I set my mind in motion.
    It is by the beans of Java, that my thoughts acquire speed.
    The hand acquire shakes; the shakes become a warning.
    It is by caffeine alone that I set my mind in motion.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    8
    Rep Power
    0
    Ok dude. I got it. Then, there should not be "/" symbol, before tmp(in the code shafique wrote).

    Am i right?

    Originally Posted by Ebot
    well, assuming the tmp folding exists (it does, right?) I find sqlplus has some issues with folders names (those with spaces really mess it up) As a test, try having it put the file in the root of your c drive.

    Comments on this post

    • Ebot agrees : Cool
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    1
    Rep Power
    0
    Originally Posted by niranjan087
    Ok dude. I got it. Then, there should not be "/" symbol, before tmp(in the code shafique wrote).

    Am i right?
    Hey man, to be even more specific than shafique, what it basically comes down to is that in your Oracle SQL*plus editor you need to type SPOOL, then a space, then the full path of wherever file you want your editor text and results outputted to. And as a side note, the file does not need to already exist. Oracle will create it if it doesn't exist. And, it will not add to an existing file either. If the file already exists, the file will be overwritten.

    So, if you want your output to go to the file at location c:\tmp\mydata.dat, then you type:

    SQL>SPOOL c:\tmp\mydata.dat

    SQL>select myfield from userDB where IMSI like '22803%' and rownum < 11

    SQL>SPOOL OFF

    Enjoy!
    (HawksAndVikes)
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Location
    Alberta, Canada
    Posts
    1
    Rep Power
    0
    Originally Posted by hawksandvikes
    Hey man, to be even more specific than shafique, what it basically comes down to is that in your Oracle SQL*plus editor you need to type SPOOL, then a space, then the full path of wherever file you want your editor text and results outputted to. And as a side note, the file does not need to already exist. Oracle will create it if it doesn't exist. And, it will not add to an existing file either. If the file already exists, the file will be overwritten.

    So, if you want your output to go to the file at location c:\tmp\mydata.dat, then you type:

    SQL>SPOOL c:\tmp\mydata.dat

    SQL>select myfield from userDB where IMSI like '22803%' and rownum < 11

    SQL>SPOOL OFF

    Enjoy!
    (HawksAndVikes)
    Your code works fine! The only problem is sends all the queries along with the results. Is there anyway to only send the results to the file but not the queries?
    Thanks in advance......

IMN logo majestic logo threadwatch logo seochat tools logo