[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Generate INSERT statement

Posted on 2004-03-28
5
Medium Priority
?
9,288 Views
Last Modified: 2012-06-21
Hi,

On Oracle 8.1.7
I am looking for a script or PL that will generate INSERT statement for data in that resides in Oracle tables.
TOAD has this feature per table. I'm looking for somthing that I will be able to execute on many tables in a loop.
Unfotunatly, We also use lob data.

Thanks.
0
Comment
Question by:peledc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 10698486
Peledc:

what are you trying to achieve here? just transport data from one place to another?

insert statement is too much a hassle for transferring data around esp when you have LOB data...


Choose EXPORT and IMPORT utility for this task.

for example, to get all the data in a schema (including all the tables) run this command at the prompt sign:

exp userid=<user/*****@db_name> file=expdat.dmp owner=<schema_owner> direct=Y

then transfer this dump file to the destination database run the exp counterpart:

imp userid=<user/*****@db_name> file=expdat.dmp full=Y
0
 
LVL 8

Accepted Solution

by:
Danielzt earned 1000 total points
ID: 10701169
here is example, hope it can give you some ideas.


SQL> create table foo (c_name varchar2(20), c_contact varchar2(20), c_address varchar2(20));
|
|Table created.
|
|SQL> CREATE OR REPLACE PROCEDURE dynamic_insert_test
| 2 ( p_c_name IN foo.c_name%TYPE,
| 3 p_c_contact IN foo.c_contact%TYPE,
| 4 p_c_address IN foo.c_address%TYPE )
| 5 IS
| 6 str_statement VARCHAR2 (1000);
| 7 str_fields VARCHAR2 (1000);
| 8 str_values VARCHAR2 (1000);
| 9
| 10 -- only need this one cursor --
| 11 CURSOR cur_column_def IS
| 12 SELECT column_name AS columnname
| 13 FROM user_tab_columns
| 14 WHERE table_name = 'FOO'
| 15 ORDER BY column_id;
| 16
| 17 -- this is used to put comma's in the dynamic SQL string --
| 18 cnt PLS_INTEGER;
| 19
| 20 BEGIN
| 21
| 22 cnt := 0;
| 23
| 24 FOR rec_column_def IN cur_column_def
| 25 LOOP
| 26 -- if we're on the second loop, put in our comma's --
| 27 if cnt > 0 then
| 28 str_fields := str_fields||', ';
| 29 str_values := str_values||', ';
| 30 else
| 31 cnt := cnt + 1;
| 32 end if;
| 33
| 34 /* when using code generated from metadata, you should make a habit of
| 35 surrounding them with double-quotes, just to bullet-proof your code */
| 36 str_fields := str_fields || '"'||rec_column_def.columnname||'"';
| 37 str_values := str_values || ':"p_'||rec_column_def.columnname||'"';
| 38
| 39 END LOOP;
| 40
| 41 -- build the final statement --
| 42 str_statement := 'INSERT INTO FOO ('||str_fields||') values ('||str_values||')';
| 43
| 44 -- let's look at it --
| 45 DBMS_OUTPUT.put_line (str_statement);
| 46
| 47 /* notice that we are using bind variables here, instead of
| 48 hard-coding the data directly into the SQL string. This
| 49 reduces server overhead */
| 50 EXECUTE IMMEDIATE str_statement
| 51 USING IN p_c_name,
| 52 IN p_c_contact,
| 53 IN p_c_address;
| 54
| 55 COMMIT;
| 56
| 57 END dynamic_insert_test;
| 58 /
|
|Procedure created.
|
|SQL> show errors
|No errors.
|SQL> set serveroutput on
|SQL> exec dynamic_insert_test('my name', 'my contact', 'my address');
|INSERT INTO FOO ("C_NAME", "C_CONTACT", "C_ADDRESS") values (:"p_C_NAME", :"p_C_CONTACT", :"p_C_ADDRESS")
|
|PL/SQL procedure successfully completed.
|
|SQL>

0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10710892
Hi,

Here's a package which does the same. U need to execute following statement.


SQL*PLUS> EXECUTE utility.generate_all('c:\datasql');

say there are tables table1, table2, table3 etc. in the database, this procedure will generate INSERT statements in the folder
c:\datasql as

table1.sql
table2.sql
table3.sql
..
..
..
.. and so on.


Hope this is what u require ...
Rgds
Nazim M
0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10710898
Hi,

Here's a package which does the same. U need to execute following statement.


SQL*PLUS> EXECUTE utility.generate_all('c:\datasql');

say there are tables table1, table2, table3 etc. in the database, this procedure will generate INSERT statements in the folder
c:\datasql as

table1.sql
table2.sql
table3.sql
..
..
..
.. and so on.


Hope this is what u require ...
Rgds
Nazim M






CREATE OR REPLACE PACKAGE utility IS
      FUNCTION change_datatype(prm_value     IN VARCHAR2,
                                                                               prm_data_type IN VARCHAR2) RETURN VARCHAR2;

      PROCEDURE generate_stmt(prm_table_name    IN VARCHAR2,
                                                                              prm_where_clause  IN VARCHAR2,
                                                                              prm_output_folder IN VARCHAR2,
                                                                              prm_output_file   IN VARCHAR2);
                         
      PROCEDURE generate_all(prm_output_folder IN VARCHAR2);
END utility;




CREATE OR REPLACE PACKAGE BODY utility IS

      -- VARIABLES USED by PROCEDURE generate_stmt
      --      File Related PACKAGE Variable
      cmn_file_handle UTL_FILE.file_type;
      --
      --

      PROCEDURE close_file IS
      BEGIN
            UTL_FILE.FCLOSE(cmn_file_handle);
      EXCEPTION
      
            WHEN UTL_FILE.INVALID_FILEHANDLE THEN
                  RAISE_APPLICATION_ERROR(-20003, 'File handle was invalid');
            WHEN UTL_FILE.INVALID_PATH THEN
                  RAISE_APPLICATION_ERROR(-20004, 'Invalid path for file');
            WHEN OTHERS THEN
                  RAISE_APPLICATION_ERROR(-20005,
                                                                                          'CLOSE_FILE Error in creating file. Message: ' ||
                                                                                          SQLERRM);
      END close_file;

      PROCEDURE open_file(prm_output_folder IN VARCHAR2,
                                                                  prm_output_file   IN VARCHAR2) IS
      
      BEGIN
            cmn_file_handle := UTL_FILE.FOPEN(prm_output_folder,
                                                                                                                  prm_output_file,
                                                                                                                  'a',
                                                                                                                  32767);
      EXCEPTION
            WHEN UTL_FILE.INVALID_FILEHANDLE THEN
                  close_file;
                  RAISE_APPLICATION_ERROR(-20000, 'File handle was invalid');
            WHEN UTL_FILE.INVALID_PATH THEN
                  close_file;
                  RAISE_APPLICATION_ERROR(-20001, 'Invalid path for file');
            WHEN OTHERS THEN
                  close_file;
                  RAISE_APPLICATION_ERROR(-20002,
                                                                                          'OPEN_FILE Error in creating file. Message: ' ||
                                                                                          SQLERRM);
      END open_file;

      FUNCTION change_datatype(prm_value     IN VARCHAR2,
                                                                               prm_data_type IN VARCHAR2) RETURN VARCHAR2 IS
      BEGIN
            IF prm_value IS NULL THEN
                  RETURN('NULL');
            END IF;
      
            IF prm_data_type = 'C' THEN
                  IF INSTR(prm_value, CHR(10)) > 0 THEN
                        RETURN('REPLACE(' || '''' || REPLACE(prm_value, CHR(10), CHR(977)) || '''' ||
                                           ', CHR(977), CHR(10))');
                  
                  END IF;
            ELSIF prm_data_type = 'D' THEN
                  RETURN('TO_DATE(' || '''' || prm_value || '''' || ', ' || '''' ||
                                     'DD-MON-YYYY HH24:MI:SS' || '''' || ')');
            ELSIF prm_data_type = 'N' THEN
                  RETURN(prm_value);
            END IF;
            RETURN('''' || prm_value || '''');
      EXCEPTION
            WHEN OTHERS THEN
                  RAISE_APPLICATION_ERROR(-20002,
                                                                                          'CHANGE_DATATYPE Error in Converting DataType. Message: ' ||
                                                                                          SQLERRM);
      END change_datatype;

      PROCEDURE generate_stmt(prm_table_name   IN VARCHAR2,
                                                                              prm_where_clause IN VARCHAR2,
                                                                              
                                                                              prm_output_folder IN VARCHAR2,
                                                                              prm_output_file   IN VARCHAR2) IS
            TYPE ref_cols IS REF CURSOR;
            mmy_ref_cols ref_cols;
      
            mmy_column_name      VARCHAR2(100);
            mmy_column_data_type VARCHAR2(1);
            mmy_col_string       VARCHAR2(32767);
            mmy_query_col_string VARCHAR2(32767);
      BEGIN
            IF prm_table_name IS NULL OR prm_output_folder IS NULL OR
                   prm_output_file IS NULL THEN
                  RAISE_APPLICATION_ERROR(-20012, 'Invalid Argument Passed');
            END IF;
      
            OPEN mmy_ref_cols FOR
                  SELECT LOWER(column_name) column_name,
                                     DECODE(data_type,
                                                            'VARCHAR2',
                                                            'C',
                                                            'CHAR',
                                                            'C',
                                                            'LONG',
                                                            'C',
                                                            'NUMBER',
                                                            'N',
                                                            'DATE',
                                                            'D') data_type
                        FROM user_tab_columns
                   WHERE table_name = UPPER(prm_table_name)
                   ORDER BY column_id;
            LOOP
                  FETCH mmy_ref_cols
                        INTO mmy_column_name, mmy_column_data_type;
                  EXIT WHEN mmy_ref_cols%NOTFOUND;
                  mmy_col_string := mmy_col_string || mmy_column_name || ', ';
                  IF mmy_column_data_type = 'D' THEN
                        mmy_query_col_string := mmy_query_col_string || 'change_datatype(' ||
                                                                                                'TO_CHAR(' || mmy_column_name || ', ' || '''' ||
                                                                                                'DD-MON-YYYY HH24:MI:SS' || '''' || ')' || ', ' || '''' ||
                                                                                                mmy_column_data_type || '''' || ') || ' || '''' || ', ' || '''' ||
                                                                                                ' || ';
                  ELSIF mmy_column_data_type IN ('N', 'C') THEN
                        mmy_query_col_string := mmy_query_col_string || 'change_datatype(' ||
                                                                                                mmy_column_name || ', ' || '''' ||
                                                                                                mmy_column_data_type || '''' || ') || ' || '''' || ', ' || '''' ||
                                                                                                ' || ';
                  END IF;
            
            END LOOP;
            CLOSE mmy_ref_cols;
      
            IF mmy_col_string IS NOT NULL AND mmy_query_col_string IS NOT NULL THEN
            
                  IF NOT UTL_FILE.IS_OPEN(cmn_file_handle) THEN
                        open_file(prm_output_folder, prm_output_file);
                  END IF;
            
                  mmy_col_string       := 'INSERT INTO ' || LOWER(prm_table_name) || ' (' ||
                                                                                          CHR(10) || CHR(9) || CHR(9) || mmy_col_string;
                  mmy_col_string       := RTRIM(mmy_col_string, ', ');
                  mmy_col_string       := mmy_col_string || ')' || CHR(10) ||
                                                                                          'VALUES ( ' || CHR(9);
                  mmy_query_col_string := RTRIM(mmy_query_col_string,
                                                                                                            ' || ' || '''' || ',' || '''' || ' || ') ||
                                                                                          ' one_pare';
            
                  OPEN mmy_ref_cols FOR ' SELECT        ' || mmy_query_col_string || ' FROM  ' || prm_table_name ||
                  
                   ' ' || prm_where_clause;
                  LOOP
                        FETCH mmy_ref_cols
                              INTO mmy_query_col_string;
                        EXIT WHEN mmy_ref_cols%NOTFOUND;
                        mmy_query_col_string := mmy_query_col_string || ');';
                        UTL_FILE.put(cmn_file_handle, mmy_col_string);
                        UTL_FILE.put_line(cmn_file_handle, mmy_query_col_string);
                  END LOOP;
                  CLOSE mmy_ref_cols;
            
                  If UTL_FILE.IS_OPEN(cmn_file_handle) THEN
                        close_file;
                  END IF;
            END IF;
      EXCEPTION
            WHEN UTL_FILE.INVALID_FILEHANDLE THEN
                  IF mmy_ref_cols%ISOPEN THEN
                        CLOSE mmy_ref_cols;
                  END IF;
            
                  close_file;
                  RAISE_APPLICATION_ERROR(-20009, 'File handle was invalid');
            WHEN UTL_FILE.INVALID_PATH THEN
                  IF mmy_ref_cols%ISOPEN THEN
                        CLOSE mmy_ref_cols;
                  END IF;
                  close_file;
                  RAISE_APPLICATION_ERROR(-20010, 'Invalid path for file');
            WHEN OTHERS THEN
                  IF mmy_ref_cols%ISOPEN THEN
                        CLOSE mmy_ref_cols;
                  END IF;
                  close_file;
                  RAISE_APPLICATION_ERROR(-20011,
                                                                                          'GENERATE_STMT Error in populating file. Message: ' ||
                                                                                          SQLERRM);
      END generate_stmt;
 
      PROCEDURE generate_all(prm_output_folder IN VARCHAR2)
      IS
  BEGIN
  /* Please amend the SQL to extract the list of tables which u require */
  FOR tab_rec IN ( SELECT table_name FROM all_tables )
  LOOP
        generate_stmt(tab_rec.table_name,'',prm_output_folder,trim(tab_rec.table_name) || '.sql');
  END LOOP;
  END generate_all;
END utility;

0
 
LVL 13

Expert Comment

by:riazpk
ID: 10715738
You can unload your data to a flat file and then use sql*loader utility to load that data.
From http://asktom.oracle.com/~tkyte/flat/index.html, here is way to do that:

 
   
(1) here is a way to do it into Excel or any spreadsheet that understands the industry standard SYLK file format:
   
We can do this with a straight plsql procedure.  There are more then one format
we can use to write an excel file -- from CSV to SYLK.  I will demonstrate the
SYLK format as I already have the code and it offers the ability to do much
fancier stuff like fonts, headings, formulas and such.  

We will use UTL_FILE (see the supplied packages guide for setup info on that
package.  You need an init.ora parameter set for this to work correctly).  
UTL_FILE allows us to write a file on the server and since your workstation =
server, this should work nicely for you.

Here is the code with an example.  It should get you going:


Rem
Rem $Id$
Rem
Rem  Copyright (c) 1991, 1996, 1997 by Oracle Corporation
Rem    NAME
Rem      owasylk.sql - Dump to Spreadsheet with formatting
Rem   DESCRIPTION
Rem     This package provides an API to generate a file in the
Rem     SYLK file format.  This allow for formatting in a
Rem     spreadsheet with only a ascii text file.  This version
Rem     of owa_sylk is specific to Oracle8.
Rem   NOTES
Rem
Rem   MODIFIED     (MM/DD/YY)
Rem     clbeck      04/08/98  - Created.
Rem     tkyte       09/10/00  - Made it use UTL_FILE.
Rem
Rem

/*
  This package allows you to send the results of any query to
  a spreadsheet using UTL_FILE

  parameters:
    p_query        - a text string of the query.  The query
                     can be parameterized
                     using the :VARAIBLE syntax.  See example
                     below.

    p_parm_names   - an owaSylkArray of the paramter names
                     used as bind variables in p_query

    p_parm_values  - an owaSylkArray of the values of the
                     bind variable names.  The values
                     muse reside in the same index as the
                     name it corresponds to.

    p_cursor       - an open cursor that has had the query
                     parsed already.

    p_sum_column   - a owaSylkArray of 'Y's and 'N's
                     corresponding to the location
                     of the columns selected in p_query.  
                     A value of NYNYY will result
                     in the 2nd, 4th and 5th columns being
                     summed in the resulting
                     spreadsheet.

    p_max_rows     - the maxium number of row to return.

    p_show_null_as - how to display nulls in the spreadsheet

    p_show_grid    - show/hide the grid in the spreadsheet.

    p_show_col_headers - show/hide the row/column headers
                         in the spreadsheet.

    p_font_name    - the name of the font

    p_widths       - a owaSylkArray of column widths.  This
                     will override the default column widths.

    p_headings     - a owaSylkArray of column titles.  
                     This will override the default column
                     titles.

    p_strip_html   - this will remove the HTML tags from the
                     results before
                     displaying them in the spreadsheet cells.
                     Useful when the
                     query selects an anchor tag. Only the
                     text between <a href>
                     and </a> tags will be sent to the
                     spreadsheet.

  examples:

    This example will create a spreadsheet of all the MANAGERS
    in the scott.emp table and will sum up the salaries
    and commissions for them.  No grid will be in the
    spreadsheet.

   

declare
    output utl_file.file_type;
begin
    output := utl_file.fopen( 'c:\temp\', 'emp1.slk', 'w',32000 );

    owa_sylk.show(
        p_file => output,
        p_query => 'select empno id, ename employee,
                           sal Salary, comm commission ' ||
                   'from scott.emp ' ||
                   'where job = :JOB ' ||
                   'and sal > :SAL',
        p_parm_names =>
               owa_sylk.owaSylkArray( 'JOB', 'SAL'),
        p_parm_values =>
                 owa_sylk.owaSylkArray( 'MANAGER', '2000' ),
       p_sum_column =>
                 owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y'),
        p_show_grid => 'NO' );

    utl_file.fclose( output );
end;



    This example will create the same spreadsheet but will
    send in a pre-parsed cursor instead

declare
    l_cursor number := dbms_sql.open_cursor;
    output utl_file.file_type;
begin
    output := utl_file.fopen( 'c:\temp\', 'emp2.slk', 'w',32000 );

    dbms_sql.parse( l_cursor,
        'select empno id, ename employee,
                sal Salary, comm commission ' ||
          'from scott.emp ' ||
          'where job = ''MANAGER'' ' ||
          'and sal > 2000',
        dbms_sql.native );

    owa_sylk.show(
        p_file => output ,
        p_cursor => l_cursor,
        p_sum_column =>
            owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
        p_show_grid => 'NO' );
    dbms_sql.close_cursor( l_cursor );
    utl_file.fclose( output );
end;

*/

create or replace
package owa_sylk as
--
  type owaSylkArray is table of varchar2(2000);
--
  procedure show(
      p_file          in utl_file.file_type,
      p_query         in varchar2,
      p_parm_names    in owaSylkArray default owaSylkArray(),
      p_parm_values   in owaSylkArray default owaSylkArray(),
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' );
--
  procedure show(
      p_file          in utl_file.file_type,
      p_cursor        in integer,
      p_sum_column    in owaSylkArray  default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' );
--
end owa_sylk;
/
show error

create or replace
package body owa_sylk as
--
  g_cvalue  varchar2(32767);
  g_desc_t dbms_sql.desc_tab;

  type vc_arr is table of varchar2(2000) index by binary_integer;
  g_lengths vc_arr;
  g_sums vc_arr;
--
--

  g_file  utl_file.file_type;


  procedure p( p_str in varchar2 )
  is
  begin
    utl_file.put_line( g_file, p_str );
  exception
    when others then null;
  end;

  function build_cursor(
      q in varchar2,
      n in owaSylkArray,
      v in owaSylkArray ) return integer is
    c integer := dbms_sql.open_cursor;
    i number := 1;
  begin
    dbms_sql.parse (c, q, dbms_sql.native);
    loop
      dbms_sql.bind_variable( c, n(i), v(i) );
      i := i + 1;
    end loop;
    return c;
  exception
    when others then
      return c;
  end build_cursor;
--
--
  function str_html ( line in varchar2 ) return varchar2 is
    x       varchar2(32767) := null;
    in_html boolean         := FALSE;
    s       varchar2(1);
  begin
    if line is null then
      return line;
    end if;
    for i in 1 .. length( line ) loop
      s := substr( line, i, 1 );
      if in_html then
        if s = '>' then
          in_html := FALSE;
        end if;
      else
        if s = '<' then
          in_html := TRUE;
        end if;
      end if;
      if not in_html and s != '>' then
        x := x || s;
      end if;
    end loop;
    return x;
  end str_html;
--
  function ite( b boolean,
                t varchar2,
                f varchar2 ) return varchar2 is
  begin
    if b then
      return t;
    else
      return f;
    end if;
  end ite;
--
  procedure print_comment( p_comment varchar2 ) is
  begin
    return;
    p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
  end print_comment;
--
  procedure print_heading( font in varchar2,
                           grid in varchar2,
                           col_heading in varchar2,
                           titles in owaSylkArray )
  is
    l_title varchar2(2000);
  begin
    p( 'ID;ORACLE' );
    print_comment( 'Fonts' );
    p( 'P;F' || font || ';M200' );
    p( 'P;F' || font || ';M200;SB' );
    p( 'P;F' || font || ';M200;SUB' );
    --
    print_comment( 'Global Formatting' );
    p( 'F;C1;FG0R;SM1' ||
           ite( upper(grid)='YES', '', ';G' ) ||
           ite( upper(col_heading)='YES', '', ';H' )  );
    for i in 1 .. g_desc_t.count loop
      p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
    end loop;
    --
    print_comment( 'Title Row' );
    p( 'F;R1;FG0C;SM2' );
    for i in 1 .. g_desc_t.count loop
      g_lengths(i) := g_desc_t(i).col_name_len;
      g_sums(i) := 0;
      begin
        l_title := titles(i);
      exception
        when others then
          l_title := g_desc_t(i).col_name;
      end;
      if i = 1 then
        p( 'C;Y1;X2;K"' || l_title || '"' );
      else
        p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
      end if;
    end loop;
  end print_heading;
--
  function print_rows(
      c            in integer,
      max_rows     in number,
      sum_columns  in owaSylkArray,
      show_null_as in varchar2,
      strip_html   in varchar2 ) return number is
    row_cnt number          := 0;
    line    varchar2(32767) := null;
    n       number;
  begin
    loop
      exit when ( row_cnt >= max_rows or
                  dbms_sql.fetch_rows( c ) <= 0 );
      row_cnt := row_cnt + 1;
      print_comment( 'Row ' || row_cnt );
      --
      p( 'C;Y' || to_char(row_cnt+2) );

      for i in 1 .. g_desc_t.count loop
        dbms_sql.column_value( c, i, g_cvalue );
        g_cvalue := translate( g_cvalue,
                            chr(10)||chr(9)||';', '   ' );
        g_cvalue := ite( upper( strip_html ) = 'YES',
                             str_html( g_cvalue ),
                             g_cvalue );
        g_lengths(i) := greatest( nvl(length(g_cvalue),
                                  nvl(length(show_null_as),0)),
                                  g_lengths(i) );
        line := 'C;X' || to_char(i+1);
        line := line || ';K';
        begin
          n := to_number( g_cvalue );
          if upper( sum_columns(i)) = 'Y' then
            g_sums(i) := g_sums(i) + nvl(n,0);
          end if;
        exception
          when others then
            n := null;
        end;
        line := line ||
                 ite( n is null,
                      ite( g_cvalue is null,
                               '"'||show_null_as||
                                  '"', '"'||g_cvalue||'"' ),
                             n );
        p( line );
      end loop;
      --
    end loop;
    return row_cnt;
  end print_rows;
--
  procedure print_sums(
      sum_columns  in owaSylkArray,
      row_cnt      in number ) is
  begin
    if sum_columns.count = 0 then
      return;
    end if;
    --
    print_comment( 'Totals Row' );
    p( 'C;Y' || to_char(row_cnt + 4) );
    p( 'C;X1;K"Totals:"' );
    --
    for i in 1 .. g_desc_t.count loop
      begin
        if upper(sum_columns(i)) = 'Y' then
          p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
                  to_char(row_cnt+2) || 'C)' );
        end if;
      exception
        when others then
          null;
      end;
    end loop;
  end print_sums;
--
  procedure print_widths( widths owaSylkArray ) is
  begin
    print_comment( 'Format Column Widths' );
    p( 'F;W1 1 7' );
    for i in 1 .. g_desc_t.count loop
      begin
        p( 'F;W' || to_char(i+1) || ' ' ||
            to_char(i+1) || ' ' ||
            to_char(to_number(widths(i))) );
      exception
        when others then
          p( 'F;W' || to_char(i+1) || ' ' ||
               to_char(i+1) || ' ' ||
               greatest( g_lengths(i), length( g_sums(i) )));
      end;
    end loop;
    p( 'E' );
  end print_widths;
--
  procedure show(
      p_file          in utl_file.file_type,
      p_cursor        in integer,
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' ) is
  --
    l_row_cnt number;
    l_col_cnt number;
    l_status  number;
  begin
    g_file := p_file;
    dbms_sql.describe_columns( p_cursor, l_col_cnt, g_desc_t );
    --
    for i in 1 .. g_desc_t.count loop
      dbms_sql.define_column( p_cursor, i, g_cvalue, 32765);
    end loop;
    --
    print_heading( p_font_name,
                   p_show_grid,
                   p_show_col_headers,
                   p_titles );
    l_status := dbms_sql.execute( p_cursor );
    l_row_cnt := print_rows(
                   p_cursor,
                   p_max_rows,
                   p_sum_column,
                   p_show_null_as,
                   p_strip_html );
    print_sums( p_sum_column, l_row_cnt );
    print_widths( p_widths );
  end show;
--
  procedure show(
      p_file          in utl_file.file_type,
      p_query         in varchar2,
      p_parm_names    in owaSylkArray default owaSylkArray(),
      p_parm_values   in owaSylkArray default owaSylkArray(),
      p_sum_column    in owaSylkArray default owaSylkArray(),
      p_max_rows      in number     default 10000,
      p_show_null_as  in varchar2   default null,
      p_show_grid     in varchar2   default 'YES',
      p_show_col_headers in varchar2 default 'YES',
      p_font_name     in varchar2   default 'Courier New',
      p_widths        in owaSylkArray default owaSylkArray(),
      p_titles        in owaSylkArray default owaSylkArray(),
      p_strip_html    in varchar2   default 'YES' ) is
  begin
    show( p_file => p_file,
          p_cursor => build_cursor( p_query,
                                    p_parm_names,
                                    p_parm_values ),
          p_sum_column => p_sum_column,
          p_max_rows => p_max_rows,
          p_show_null_as => p_show_null_as,
          p_show_grid => p_show_grid,
          p_show_col_headers => p_show_col_headers,
          p_font_name => p_font_name,
          p_widths => p_widths,
          p_titles => p_titles,
          p_strip_html => p_strip_html );
  end show;
--
end owa_sylk;
/
show error
 

(2) Pro*C  Way to unload to flat file (Very fast)

The pro*c program (works in 7.x - 8.x as is) is simply:

#include <stdio.h>
#include <string.h>
#include <ctype.h>

#define MAX_VNAME_LEN     30
#define MAX_INAME_LEN     30

static char *     USERID = NULL;
static char *   SQLSTMT = NULL;
static char *   ARRAY_SIZE = "10";

#define vstrcpy( a, b ) \
(strcpy( a.arr, b ), a.len = strlen( a.arr ), a.arr)

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern SQLDA *sqlald();
extern void   sqlclu();



static void die( char * msg )
{
    fprintf( stderr, "%s\n", msg );
    exit(1);
}


/*
    this array contains a default mapping
    I am using to constrain the
       lengths of returned columns.  It is mapping,
    for example, the Oracle
       NUMBER type (type code = 2) to be 45 characters
    long in a string.
*/

static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
 18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
  0, 0, 512, 2000 };


static void process_parms( argc, argv )
int    argc;
char *    argv[];
{
int    i;

    for( i = 1; i < argc; i++ )
    {
        if ( !strncmp( argv[i], "userid=", 7 ) )
              USERID = argv[i]+7;
        else
        if ( !strncmp( argv[i], "sqlstmt=", 8 ) )
              SQLSTMT = argv[i]+8;
        else
        if ( !strncmp( argv[i], "arraysize=", 10 ) )
              ARRAY_SIZE = argv[i]+10;
        else
        {
            fprintf( stderr,
                    "usage: %s %s %s\n",
                     argv[0],
                    "userid=xxx/xxx sqlstmt=query ",
                    "arraysize=<NN>\n" );
            exit(1);
        }
    }
    if ( USERID == NULL  || SQLSTMT == NULL )
    {
        fprintf( stderr,
                "usage: %s %s %s\n",
                 argv[0],
                "userid=xxx/xxx sqlstmt=query ",
                "arraysize=<NN>\n" );
        exit(1);
    }
}

static void sqlerror_hard()
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    fprintf(stderr,"\nORACLE error detected:");
    fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}



static SQLDA * process_1(char * sqlstmt, int array_size )
{
SQLDA *    select_dp;
int     i;
int        j;
int        null_ok;
int        precision;
int        scale;
int        size = 10;

    fprintf( stderr, "Unloading '%s'\n", sqlstmt );
    fprintf( stderr, "Array size = %d\n", array_size );


    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
       EXEC SQL PREPARE S FROM :sqlstmt;
       EXEC SQL DECLARE C CURSOR FOR S;

    if ((select_dp = sqlald(size,MAX_VNAME_LEN,MAX_INAME_LEN))
                   == NULL )
        die( "Cannot allocate  memory for select descriptor." );

    select_dp->N = size;
    EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    if ( !select_dp->F ) return NULL;

    if (select_dp->F < 0)
    {
        size = -select_dp->F;
        sqlclu( select_dp );
        if ((select_dp =
                sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN))
                      == NULL )
        die( "Cannot allocate  memory for descriptor." );
        EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
    }
    select_dp->N = select_dp->F;

    for (i = 0; i < select_dp->N; i++)
        select_dp->I[i] = (short *) malloc(sizeof(short) *
                                                array_size );

    for (i = 0; i < select_dp->F; i++)
    {
        sqlnul (&(select_dp->T[i]),
                &(select_dp->T[i]), &null_ok);
        if ( select_dp->T[i] <
                     sizeof(lengths)/sizeof(lengths[0]) )
        {
            if ( lengths[select_dp->T[i]] )
                 select_dp->L[i]  = lengths[select_dp->T[i]];
            else select_dp->L[i] += 5;
        }
        else select_dp->L[i] += 5;

        select_dp->T[i] = 5;
        select_dp->V[i] = (char *)malloc( select_dp->L[i] *
                                               array_size );

        for( j = MAX_VNAME_LEN-1;
             j > 0 && select_dp->S[i][j] == ' ';
             j--);
        fprintf (stderr,
                "%s%.*s", i?",":"", j+1, select_dp->S[i]);
    }
    fprintf( stderr, "\n" );


    EXEC SQL OPEN C;
    return select_dp;
}


static void process_2( SQLDA * select_dp, int array_size )
{
int    last_fetch_count;
int        row_count = 0;
short    ind_value;
char    * char_ptr;
int    i,
       j;

    for ( last_fetch_count = 0;
          ;
          last_fetch_count = sqlca.sqlerrd[2] )
    {
        EXEC SQL FOR :array_size FETCH C
                      USING DESCRIPTOR select_dp;

        for( j=0; j < sqlca.sqlerrd[2]-last_fetch_count; j++ )
        {
            for (i = 0; i < select_dp->F; i++)
            {
                ind_value = *(select_dp->I[i]+j);
                char_ptr  = select_dp->V[i] +
                                  (j*select_dp->L[i]);

                printf( "%s%s", i?",":"",
                             ind_value?"(null)":char_ptr );
            }
            row_count++;
            printf( "\n" );
        }
        if ( sqlca.sqlcode > 0 ) break;
    }

    sqlclu(select_dp);

    EXEC SQL CLOSE C;

    EXEC SQL COMMIT WORK;
    fprintf( stderr, "%d rows extracted\n", row_count );
}



main( argc, argv )
int    argc;
char *    argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR   oracleid[50];
EXEC SQL END DECLARE SECTION;
SQLDA    * select_dp;


    process_parms( argc, argv );

    /* Connect to ORACLE. */
    vstrcpy( oracleid, USERID );

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    fprintf(stderr, "\nConnected to ORACLE as user: %s\n\n",
             oracleid.arr);

    EXEC SQL ALTER SESSION
      SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

    select_dp = process_1( SQLSTMT, atoi(ARRAY_SIZE) );
    process_2( select_dp , atoi(ARRAY_SIZE));

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}


$./array_flat userid=scott/tiger 'sqlstmt=select * from emp'  arraysize=100


(thats on 1 line) it will produce:

Connected to ORACLE as user: scott/tiger

Unloading 'select * from emp'
Array size = 100
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-DEC-1980 00:00:00,800,(null),20
7499,ALLEN,SALESMAN,7698,20-FEB-1981 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-1981 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,02-APR-1981 00:00:00,2975,(null),20
7654,MARTIN,SALESMAN,7698,28-SEP-1981 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-1981 00:00:00,2850,(null),30
7782,CLARK,MANAGER,7839,09-JUN-1981 00:00:00,2450,(null),10
7788,SCOTT,ANALYST,7566,09-DEC-1982 00:00:00,3000,(null),20
7839,KING,PRESIDENT,(null),17-NOV-1981 00:00:00,5000,(null),10
7844,TURNER,SALESMAN,7698,08-SEP-1981 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-1983 00:00:00,1100,(null),20
7900,JAMES,CLERK,7698,03-DEC-1981 00:00:00,950,(null),30
7902,FORD,ANALYST,7566,03-DEC-1981 00:00:00,3000,(null),20
7934,MILLER,CLERK,7782,23-JAN-1982 00:00:00,1300,(null),10
14 rows extracted

If you run it as:

$ ./array_flat userid=scott/tiger 'sqlstmt=select * from emp' arraysize=100 > 
test.dat

You'll see:

Connected to ORACLE as user: scott/tiger

Unloading 'select * from emp'
Array size = 100
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
14 rows extracted

and test.dat will just have the data.
]

(3) PL/SQL Method:

create or replace function  dump_csv( p_query     in varchar2,
                                      p_separator in varchar2
                                                    default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )
return number
AUTHID CURRENT_USER
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i,
                                    l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue,
                            2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                   l_columnValue );
            utl_file.put( l_output, l_separator ||  
                                    l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/


You would use that for example like this:

create or replace procedure test_dump_csv
as
    l_rows  number;
begin
    l_rows := dump_csv( 'select *
                           from all_users
                           where rownum < 25',
                        ',', '/tmp', 'test.dat' );
end;
/


Make sure to read about the INIT.ORA parameters (utl_file_dir) you need to setup
!!!

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question