Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: How To Get Data From Tables Via Script

    Hi, all.

    Would like to know how to get scripts of data from oracle tables to generate inserts automatically.

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What are you trying to do? If all you want to do is insert the contents of one table into another then use

    insert into table2(col1,col2,col3)
    select col1,col2,col3 from table1
    where col1 = 'whatever';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    Well, i've used SQLTools 1.4 that gets DDL script from Oracle, but i do not found DML tools. Just i want is generate script from Oracle to keep a mass of data to test, since i need work with 22 tables. If i can get the Inserts with these data, i dont need to type everything after some changes.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could use the export/import utility to copy tables from one DB to another.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    Thanks, LKBrwn_DBA, but i need the script, really. Some people will use these scripts outside my environment.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    SQL*Plus scripts?
    Generating a script with INSERT statements would take a long time to load the data on the other database.
    If you are not using export/import utility, then better generate a delimited data file.
    The following script generates for any table: a 'pipe' delimited datafile, and sql loader control and parameter files:
    Code:
    -- dump_pipe.sql 
    --
    -- dump a table to a 'pipe' delimited ascii file
    -- only drawback is line length is likely to be padded with
    -- quite a few spaces if the 'set trimspool on' option is
    -- not in your version of SQLPLUS
    --
    -- also builds a control file and a parameter file for SQL*LOADER
    --
    
    
    set trimspool on
    set serverout on
    clear buffer
    
    def tmpdir=$ORATMP
    
    var maxcol number
    var linelen number
    var dumpfile char(40)
    
    col column_id noprint
    
    set pages 0 feed off termout on echo off verify off
    
    --accept dumpowner char prompt 'Owner of table to dump: '
    def dumpowner=&1
    --accept dumptable char prompt 'Table to dump: '
    def dumptable=&2
    --prompt 'Replace single quotes in the where condition with ~' --accept wherecond char prompt 'Where condition: ' def wherecond=''
    
    set term off feed off
    col cdumpowner noprint new_value dumpowner
    select '&&dumpowner' cdumpowner from dual;
    
    col cdumptable noprint new_value dumptable
    select '&&dumptable' cdumptable from dual;
    set term on feed off embedded on
    
    define squote=chr(39)
    define dquote=chr(34)
    --define comma=chr(44)
    define comma=chr(124)
    
    var hdrlin varchar2(4000);
    begin  
      select max(column_id) into :maxcol
        from all_tab_columns
       where table_name = rtrim(upper('&dumptable'))
         and owner = rtrim(upper('&dumpowner'));
      select sum(data_length) + ( :maxcol * 3 ) into :linelen
        from all_tab_columns
       where table_name = rtrim(upper('&dumptable'))
         and owner = rtrim(upper('&dumpowner'));
      :hdrlin:='';
      for col in (
          select column_name
            from all_tab_columns
           where table_name = rtrim(upper('&dumptable'))
             and owner = rtrim(upper('&dumpowner'))) 
      loop
        :hdrlin:=:hdrlin||col.column_name||'|';
      end loop;    
    end;
    /
    print linelen
    print maxcol
    spool &tmpdir/_dump.sql
    
    set trimspool on termout off pages 0 lin 2048
    prompt set trimspool on termout off pages 0 lin 1024
    prompt set heading off echo off ver off feed off emb on recsep off select 'set line ' || :linelen from dual; select 'spool &tmpdir/' || lower('&dumptable') || '.txt' from dual; select 'prompt '||:hdrlin from dual; select 'select'|| chr(10) from dual;
    
    select
      decode(data_type,'DATE','TO_CHAR('||column_name||','
             ||&&squote ||'YYYY-MM-DD'|| &&squote ||')',
      decode(data_type,'VARCHAR2','RTRIM(','')
             ||'replace(' || column_name ||','|| &&squote ||&&comma|| &&squote
             ||','||&&squote ||'/'|| &&squote || ') ' ||
      decode(data_type,'VARCHAR2',')','')) || ' ||' ||
      decode(column_id,:maxcol, &&squote || &&comma || &&squote
                              , &&squote || &&comma || &&squote || ' || '),
      column_id
    from all_tab_columns
    where table_name = upper('&dumptable')
    and owner = upper('&dumpowner')
    order by 2
    /
    select 'from &dumpowner..&dumptable' from dual;
    select Decode('&&wherecond',Null,'/'
                 ,'Where '||replace('&&wherecond','~',chr(39))
                 ||chr(10)||'/')
      from dual;
    
    select 'spool off' from dual;
    
    spool off
    
    @@&tmpdir/_dump
    
    set line 79
    -- build a basic control file
    spool &tmpdir/_dtmp.sql
    select 'spool &tmpdir/' || lower('&dumptable') || '.par' from dual; spool off @@&tmpdir/_dtmp
    
    select 'userid = ' ||upper('&dumpowner')  || chr(10) ||
           'control = ' || lower('&dumptable') || '.ctl' || chr(10) ||
           'log = ' || lower('&dumptable') || '.log' || chr(10) ||
           'bad = ' || lower('&dumptable')|| '.bad' || chr(10)
    from dual;
    
    spool &tmpdir/_dtmp.sql
    select 'spool &tmpdir/' || lower('&dumptable') || '.ctl' from dual; spool off @@&tmpdir/_dtmp select 'load data' || chr(10) ||
           'infile '||&&squote||lower('&dumptable')||'.txt'||&&squote||chr(10)||
           'append into table &dumpowner..&dumptable' || chr(10) ||
           'fields terminated by ' || &&squote || &&comma || &&squote ||
           ' optionally enclosed by ' || &&squote || &&dquote || &&squote  || chr(10
    )
    from dual;
    
    select '(' from dual;
    
    select '   ' || column_name ||
      decode(data_type,'DATE',' Date '||&&squote||'YYYY-MM-DD'||&&squote,'') ||
      decode(column_id,:maxcol, '', ','),
      column_id
    from all_tab_columns
    where table_name = upper('&dumptable')
    and owner = upper('&dumpowner')
    order by 2
    /
    
    select ')' from dual;
    
    spool off
    --exit
    Once the data file is created, you can load into any database using the corresponding bulk-load utility.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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