Solved: Generate INSERT statement

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

n4nazim

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

n4nazim

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;

Ora_Techie

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
!!!