0

I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building an INSERT file with such a method: Oracle PL/SQL: Dump query result into file

But I am wondering if there is some tool already existing to export data based on a query. If this is the case, I might as well be happy with some binary file, as long as importing this file afterward is straight forward. I see there is exp, but it doesn't seem to really be able to handle queries, only basic WHERE clauses.

3 Answers 3

1

Have you looked into using the UTL_FILE package ? Using that, you can write to ( and read from ) files really easily and directly from PL/SQL, with great robustness. You need to configure a Directory object in your database, and then you can write to it with ease.

Here's some material on the subject : http://psoug.org/reference/utl_file.html , http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm

2
  • No sure what you mean by 'insert file' but i'll assume you mean a file with insert statements. For exporting data you have multiple options. You don't tell us your oracle version. Let's assume it is 10g or later. So you can use oracle/sql developer or most other client gui tools which can export query result sets as 'insert statements'. Or you can get creative and use your query to create a external table, and using oracle_datapump driver you can create a file that can be read as external table on destination. You can also use sqldeveloper to generate nice csv output if you prefer that.
    – Raj
    May 8, 2014 at 19:32
  • It all depends on how frequently you will want to do this and how hands-on/automated you will want to make this.
    – Raj
    May 8, 2014 at 19:33
1

There is a tool, Oracle SQL Developer or Oracle SQLcl (command line).

SELECT /*insert*/ * FROM ...`

execute with F5. You'll get the output in the form of INSERT statements.

In SQLcl, you can use SET SQLFORMAT insert - and the output will default to insert statements until you change the mode, so no need to change your queries.

There are lots of formats avail, including csv, xml, json, and html.

Using either tool, wrap your query with a SPOOL ON|OFF - this will write the file to your local machine where SQL Dev or SQLcl are running.

set sqlformat

0

try this:

(You might want to write the output to a file ...)

PROCEDURE DUMP_DATA_AS_INSERT (p_table_name        in varchar2,
                               p_include_pk_flag   in varchar2 default 'Y') AS

  l_sql_str        varchar2(1000);   
  l_insert_into    varchar2(4000);  
  l_col_list       varchar2(4000);   
  l_data           varchar2(32000);   
  l_num_rows       pls_integer;

  type aa is table of varchar2(1000) index by varchar2(128);          
  a_pk aa;  

BEGIN

  l_insert_into := 'insert into '||p_table_name||' (';

  for c1 in (select column_id, column_name
               from user_tab_columns
              where table_name = p_table_name
            order by column_id ) loop

    -- assume pk is a single column and the first ....
    -- may wish to exclude if ultimate source has pk populated by a sequence
    if p_include_pk_flag = 'N' and c1.column_id = 1 then continue; end if;

    l_insert_into := l_insert_into ||c1.column_name || ', ';
    l_col_list := l_col_list || '||'''''',''''''|| replace('||c1.column_name|| ','''''''','''''''''''')';

  end loop;

  l_insert_into := substr(l_insert_into,1, length(l_insert_into) -2) || ') ';   
  l_col_list    := '''''''''||' || substr(l_col_list, 12);

  --dbms_output.put_line ( l_insert_into);   
  --dbms_output.put_line ( l_col_list);

  l_sql_str := 'select count(*) from '||p_table_name;   
  execute immediate l_sql_str into l_num_rows;

  --dbms_output.put_line(l_num_rows);


  -- setup a hash on the pk.
      for c1 in ( select rownum, SUBR_SETTING_ID 
                    from CUST_SUBSCRIBER_SETTINGS) loop

    a_pk(c1.rownum) := c1.subr_setting_id;

  end loop;

  for i in 1 .. l_num_rows loop

    l_sql_str := 'select '|| l_col_list ||' from '||p_table_name || ' where subr_setting_id = ' || a_pk(i);
    execute immediate l_sql_str into l_data;
    l_data := l_insert_into|| chr(10) || 'values ('|| l_data ||''');';

    dbms_output.put_line(l_data);

  end loop;

END DUMP_DATA_AS_INSERT;
1
  • Code only answers are not useful to everyone. Please explain it as well. Dec 26, 2023 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.