Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Exclamation Unanswered: Writing A Script

    Hi all!
    I'm very new to this field. I've to write a script to transfer a data from a oracle table to a text file . Once done this , in the second step i have to read data from that text file into another Oracle table. It is not neceesary that the structures of the two tables will be same. But i think i will address this issue later on. At the moment i want a script to read from table and dump it into a text file and then read from the text file and dump it to a table.Assume the structure of table..
    Thanks in advance,
    GuytoRemember.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Script that selects data from a database table and writes output into a file should be something like this:
    Code:
    SPOOL filename.txt
    
    SELECT column1, ..., columnx
    FROM table;
    
    SPOOL OFF;
    To insert data from this file into database table, you'll need SQL*Loader utility.
    Read something about it on these pages:
    http://www.orafaq.com/faqloadr.htm
    http://www.oraxcel.com/

    However ... why don't you simply select data from one table and insert it into another? Such as (simplified)
    Code:
    INSERT INTO table2
    SELECT * FROM table1;
    Or use EXPORT / IMPORT utility?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Or if the table is in another database, but they can talk to each other through the listeners then use the copy command.


    usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
    <db> : database string, e.g., scott/tiger@d:chicago-mktg
    <opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
    <table>: name of the destination table
    <cols> : a comma-separated list of destination column aliases
    <sel> : any valid SQL SELECT statement
    A missing FROM or TO clause uses the current SQL*Plus connection.

    Export/import will make an exact copy of the table. Copy can be used to build up a new table using select. If the table is in the same database, then simply use something like:

    insert into new_table select col2,col1,... from old_table where ...

  4. #4
    Join Date
    Jul 2004
    Posts
    2
    Quote Originally Posted by guytoremember
    Hi all!
    I'm very new to this field. I've to write a script to transfer a data from a oracle table to a text file . Once done this , in the second step i have to read data from that text file into another Oracle table. It is not neceesary that the structures of the two tables will be same. But i think i will address this issue later on. At the moment i want a script to read from table and dump it into a text file and then read from the text file and dump it to a table.Assume the structure of table..
    Thanks in advance,
    GuytoRemember.

    hi!
    The structures of the two tables is not similar so a direct import and export cannot be used. Also if a script is wriiten , it will be more generic ans could also be used in another later stage of our project. I've been specifically directed NOT to use import & export. We' need to edit the text files generated and then dump the data into another table. So , i now need to find out how to read it from a file and put it in a table. Proc is a good option which will make things easier but I need to write a Sql Script . Any suggestions folks??
    Thanks in advnace
    Guytoremember

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    As littlefoot already said, use sqlloader. He even gave links for documentation.

  6. #6
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21

    Utl_file

    Hi there,
    You can use UTL_FILE utility to write and read file in different structure and different format. Its a predefined oracle package. You can find about UTL_FILE package in pl/sql packages type reference document in OTN.

Posting Permissions

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