Solved

Generate INSERT statement

Posted on 2004-03-28
5
9,275 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
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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now