Results 1 to 12 of 12

Thread: Sql * Loader

  1. #1
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36

    Unanswered: Sql * Loader

    Hi,

    How can i export data from many table into a single .csv file using SQL Loader or any other equivalent scripts?

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: Sql * Loader

    If u have sql server installed in your machine. You can used sqlserver
    dts facility. You can connect to oracle from it and transfer data to
    any file.
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    From SQLPlus, select the data into a spool file .... ie,

    set linesize 120 ---- Or whatever the linesize will be
    set header off
    set pagesize 0
    set feedback off
    set trimspool on

    spool c:\test.txt

    select username||chr(9)||sid||chr(9)||serial#||chr(9)||pr ogram from v$session;

    spool off



    This creates a tab delimited file called test.txt

    HTH
    Gregg

  4. #4
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    I've tried using this way but it is not supporting for multiple table export.
    What can i do for this?

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    in sqlplus
    why not set colsep = ','??

    spool c:\test.csv


    OR if you don't need to do this many times TOAD has a great export to csv/xls utility.

    Originally posted by gbrabham
    From SQLPlus, select the data into a spool file .... ie,

    set linesize 120 ---- Or whatever the linesize will be
    set header off
    set pagesize 0
    set feedback off
    set trimspool on

    spool c:\test.txt

    select username||chr(9)||sid||chr(9)||serial#||chr(9)||pr ogram from v$session;

    spool off



    This creates a tab delimited file called test.txt

    HTH
    Gregg
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Hi,
    Actually i have to pick up whole data from many tables and i've apply some changes on those data,then atlast i've to put them into a single .csv file.
    i think,TOAD export utility don't support to do this.So,that i'm saking for some scripts which will solve this purpose.
    Thanks for your time and efforts in advance.

  7. #7
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    In addition,
    Is there any possibity to acheive this using UT_FILE PL/SQL package???
    If Yes,then pls tell me the steps to be followed...

  8. #8
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Yes it can be achive by UTL_FILE
    STEPS:
    1. Create Directory
    Create directory utl_file_dir '/db/utl_file'
    2.Modify the init.ora file
    utl_file_dir /db/utl_file
    3.Grant read ,write acces to the schema.
    Grant read on directory utl_file_dir to scott;
    4.
    CREATE OR REPLACE PROCEDURE utl_file_write AS
    id UTL_FILE.FILE_TYPE;
    name VARCHAR2(20) := 'customer.dat';
    err VARCHAR2(100);
    num NUMBER;
    BEGIN
    id := UTL_FILE.FOPEN('/db/utl_file',name, 'a');

    FOR cust IN (Select custname from customer)
    LOOP
    utl_file.PUT_LINE( id, cust.custname );

    END LOOP;

    utl_file.fCLOSE(id);

    EXCEPTION
    WHEN utl_file.invalid_path THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
    WHEN utl_file.invalid_mode THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
    WHEN utl_file.invalid_filehandle THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
    WHEN utl_file.invalid_operation THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
    WHEN utl_file.read_error THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
    WHEN utl_file.write_error THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
    WHEN utl_file.internal_error THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
    END;

    Good Luck!
    Pagnint
    (No need to search web before posting new question)

  9. #9
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Hi,

    I hope it'll solve my problem..but i don't know the a,bc..s of UTL_FILE..
    where can i find the Stuff for the same....
    ThanX in advance.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    where oh where have the documents gone ...

    http://tahiti.oracle.com/pls/db92/db92.homepage
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Yeah..I got the concept of UTL_FILE...

    Can you give me an example code to fetch data from two different tables and putting them into a single txt/csv file...???
    i very much thankful to you....for this.

  12. #12
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36

    UTL_FILE code help

    Yeah..I got the concept of UTL_FILE...

    Can you give me an example code to fetch data from two different tables and putting them into a single txt/csv file...???

    i will be very much thankful to you....for this.

Posting Permissions

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