Friday, April 10, 2009

ORACLE - SPOOL COMMAND

What is SPOOL ?
Spool Command in ORACLE is used to transfer data from oracle tables into other files.

How to SPOOL from ORACLE in CSV format ??

Set echo off;
Set define Off;
Set feedback Off;
Set serveroutput On;
SET PAGESIZE 0
SET LINESIZE 1000
Spool c:\file.csv

SELECT EMPNAME||','||EMPID||','||EMPSAL FROM EMPLOYEE;

Spool Off;
Set define On;
Set feedback On;

9 comments:

  1. Just curious about it......can we have phpmyadmin working for oracle.....as we have for mysql......that way it will be easier to export to file

    ReplyDelete
  2. No idea about this. We do SPOOL in csv format so that the data can be imported to another location ie creating a back up or replica....

    ReplyDelete
  3. Brief and apt.. thanks...

    ReplyDelete
  4. good for learning purpose and short but knowledgeable

    ReplyDelete
  5. i used sqlDeveloper and the result show me this:
    > SELECT
    TO_CHAR(Employee_ID) || ',' ||
    '"' || REPLACE(First_Name, '"', '""') || '"' ||
    ',"' || REPLACE(Last_Name, '"', '""') || '",' ||
    ',' || TO_CHAR(Hire_Date, 'MM/DD/YYYY') ||
    ',' || TO_CHAR(Salary)
    FROM HR.Employees
    Spool Off
    > set define On
    > set feedback On

    WHY show me this?

    ReplyDelete
  6. Can we create a dynamic output file name based on the date it runs?
    In the given example, we have Spool c:\file.csv.
    I need something like Spool c:\file_2_21_2014.csv
    So the format is c:\file_(current date).csv

    Can anyone help me with this?

    ReplyDelete