Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Question Unanswered: Export Data in DDL format?

    Hi Im new to oracle!

    I need some help!
    I look for a way to export the, content of my oracle tables, in DLL format.
    Like:
    insert into table values ('....,...');
    ....

    I only know the exp, imp tool, but this is no right readable DDL;

    thanks for your answer
    Micha

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Export Data in DDL format?

    Originally posted by Soldout
    Hi Im new to oracle!

    I need some help!
    I look for a way to export the, content of my oracle tables, in DLL format.
    Like:
    insert into table values ('....,...');
    ....

    I only know the exp, imp tool, but this is no right readable DDL;

    thanks for your answer
    Micha
    The only way I know of to do this is tro write your own SQL statement like this:

    SELECT 'INSERT INTO tab (id, name, start_date) VALUES ('
    || id || ',''' || name || ''', TO_DATE( '''
    || TO_CHAR( start_date, 'YYYYMMDDHH24MISS' )
    || ''', ''YYYYMMDDHH24MISS'')'
    || ');'
    FROM tab;

    I have used an example of a NUMBER, VARCHAR2 and DATE column to show how you would need to handle these. The method is:

    NUMBER:
    Just concatenate the value: || id ||

    VARCHAR2:
    Enclose the value in quotes - need to double up embedded quotes:
    || '''' || name || ''''

    DATE:
    Need to convert to VARCHAR2 in a format that will not lose any time information, and then tell the insert statement to convert back to a DATE using the same format:
    || 'TO_DATE(''' || TO_CHAR(start_date,'YYYYMMDDHH24MISS') || ''',''YYYYMMDDHH24MISS'')'

    To run this in SQL Plus you would need to:

    SET LINESIZE <large enough>
    SET PAGESIZE 0
    SPOOL insert_script.sql
    /
    SPOOL OFF

Posting Permissions

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