utl_file [message #39105] |
Sun, 16 June 2002 22:21 |
Mahesh
Messages: 90 Registered: January 2001
|
Member |
|
|
Hi,
i have a cursor v_cur which is a select * from table.
This table has abt 400 columns.i don't have choice to partition the table.
now i need to write records of this table to a file using utl_file handle.
v_cur is select * from table;
utl_file(v_file_handle,col2,col99,col3,col4.....col400);
i need to achieve 2 objectives -
1. multi records have to be written in one shot to a file.
2.is there some way of avoiding the select * from table in cursor.ie dynamically insert the 400 columns by using some pointer.
Please post your response.
how do i achieve this?
|
|
|
|
Re: utl_file [message #39111 is a reply to message #39105] |
Mon, 17 June 2002 08:59 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
See if this example helps:
PROCEDURE punloadrecord (
ppowner IN VARCHAR2,
pptablename IN VARCHAR2,
ppwhereclause IN VARCHAR2)
IS
/****************************************************************************
Author: Sean Cassidy (scassidy@ozemail.com.au) December 1999
Purpose: Unload a record to be a SQL INSERT Statement.
Primarily for one record but will handle if many.
Parameters
Name Details
---------------- ------------------------------------------------------------
ppOwner The Owner of the table.
ppTableName The Name of the Table from which the record is to be
Unloaded from
ppWhereClause A CLAUSE to identify the record. May select multiple records
NB: This Procedure is about as robust as those little sachets
of International Roast Coffee that you get in cheap hotel rooms
It will fall over for any number of reasons and does not handle LONG
****************************************************************************/
CURSOR ctablecolumns (cpowner VARCHAR2, cptablename VARCHAR2)
IS
SELECT atc.column_name, atc.data_type
FROM all_tab_columns atc, all_tables ats
WHERE
--== Join to All_Tables so we can't get views (Inserts would fail) ==--
atc.owner = UPPER (cpowner)
AND atc.owner = ats.owner
AND atc.table_name = ats.table_name
AND ats.table_name = UPPER (cptablename)
AND atc.data_type IN ('DATE', 'NUMBER', 'VARCHAR2');
rtablecolumns ctablecolumns%ROWTYPE;
--
TYPE ttctable IS TABLE OF ctablecolumns%ROWTYPE
INDEX BY BINARY_INTEGER;
TYPE tcursortable IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
TYPE tvaltable IS TABLE OF VARCHAR2(2000)
INDEX BY BINARY_INTEGER;
lvcursortable tcursortable;
lvtctable ttctable;
lvvaluetable tvaltable;
lvselectioncursor INTEGER;
lvselectstatement VARCHAR2(2000);
lvcolumnlist VARCHAR2(2000);
i BINARY_INTEGER := 0;
lvnumcolumns NUMBER := 0;
lvcurrentrow ROWID;
lvignore NUMBER;
lvcurrentvalue VARCHAR2(2000);
BEGIN -- pUnloadRecord;
--== Fetch our columns into a pl/sql table ==--
FOR rtablecolumns IN ctablecolumns (ppowner, pptablename)
LOOP
i := i + 1;
lvtctable (i) := rtablecolumns;
END LOOP;
lvnumcolumns := i;
<<columnlist_loop>>
FOR i IN 1 .. lvnumcolumns
LOOP
-- Add each column name to a list
IF lvcolumnlist IS NULL
THEN
lvcolumnlist := lvtctable (i).column_name;
ELSE
lvcolumnlist := lvcolumnlist || ','|| lvtctable (i).column_name;
END IF; -- ColumnList is NULL
IF lvtctable (i).data_type = 'DATE'
THEN
--== All the quotes are so our strings have enough in the end ==--
lvselectstatement :=
'SELECT ''to_date(''''''||to_char('||
lvtctable (i).column_name ||
', ''yyyymmddhh24miss'')||
'''''', ''''yyyymmddhh24miss'''')'' FROM '||
pptablename ||
' WHERE rowid = :x';
ELSIF lvtctable (i).data_type = 'NUMBER'
THEN
lvselectstatement :=
'SELECT to_char('||
lvtctable (i).column_name ||
')'||
' FROM '||
pptablename ||
' WHERE rowid = :x';
ELSIF lvtctable (i).data_type = 'VARCHAR2'
THEN
lvselectstatement :=
'SELECT ''''''''||'||
lvtctable (i).column_name ||
'||'''''''' FROM '||
pptablename ||
' WHERE rowid = :x';
END IF; -- Data type
lvcursortable (i) := DBMS_SQL.open_cursor;
DBMS_SQL.parse (lvcursortable (i), lvselectstatement, DBMS_SQL.native);
END LOOP column_list_loop;
--===--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--
--== Because DBMS_SQL requires us to call define_column for each ==--
--== column, we cannot have dynamic number of columns fetched ==--
--== Instead, we need to fetch each column seperately... Bugger! ==--
--===--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--
lvselectstatement :=
'SELECT rowid'|| ' FROM '|| pptablename || ' WHERE '|| ppwhereclause;
lvselectioncursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (lvselectioncursor, lvselectstatement, DBMS_SQL.native);
DBMS_SQL.define_column_rowid (lvselectioncursor, 1, lvcurrentrow);
lvignore := DBMS_SQL.execute (lvselectioncursor);
<<fetch_rows_loop>>
LOOP
IF DBMS_SQL.fetch_rows (lvselectioncursor) > 0
THEN
DBMS_SQL.column_value (lvselectioncursor, 1, lvcurrentrow);
<<column_loop>>
FOR i IN 1 .. lvnumcolumns
LOOP
DBMS_SQL.define_column (lvcursortable (i), 1, lvcurrentvalue, 2000);
DBMS_SQL.bind_variable (lvcursortable (i), 'x', lvcurrentrow);
lvignore := DBMS_SQL.execute (lvcursortable (i));
IF DBMS_SQL.fetch_rows (lvcursortable (i)) > 0
THEN
DBMS_SQL.column_value (lvcursortable (i), 1, lvcurrentvalue);
ELSE
lvcurrentvalue := NULL;
END IF;
IF lvcurrentvalue IS NULL
THEN
lvcurrentvalue := 'NULL';
END IF;
lvvaluetable (i) := lvcurrentvalue;
END LOOP column_loop;
--== Output this row ==--
DBMS_OUTPUT.put_line (
'INSERT INTO '|| ppowner || '.'|| pptablename || '('
);
pprintcolumnlist (lvcolumnlist);
DBMS_OUTPUT.put_line (') VALUES (');
<<output_values>>
FOR i IN 1 .. lvnumcolumns
LOOP
IF i > 1
THEN
DBMS_OUTPUT.put (',');
END IF;
pprintstring (lvvaluetable (i));
END LOOP output_values;
DBMS_OUTPUT.put_line (')');
DBMS_OUTPUT.put_line ('/');
ELSE
EXIT fetch_rows_loop;
END IF; -- fetch > 0
END LOOP fetch_rows_loop;
--== Close all the cursors ==--
<<column_cursor_loop>>
FOR i IN 1 .. lvnumcolumns
LOOP
DBMS_SQL.close_cursor (lvcursortable (i));
END LOOP column_cursor_loop;
DBMS_SQL.close_cursor (lvselectioncursor);
END punloadrecord;
/
|
|
|