Re: Converting oracle table's data into csv on client computer

From: Billy <vslabs_at_onwe.co.za>
Date: 22 Jul 2005 02:35:19 -0700
Message-ID: <1122024919.595112.3120@f14g2000cwb.googlegroups.com>

nirav wrote:

> Any ideas on : 1) How can we manage this using sql?

Using a PL/SQL pipeline. Wrote the following sample code for someone on Metalink a while ago (different requirement, but also CSV output). ==
SQL> create or replace type TColumnNames is table of varchar2(30); 2 /

Type created.

SQL>
SQL> create or replace type TColumnValues is table of varchar2(4000); 2 /

Type created.

SQL>
SQL> create or replace type TLotsOfMeaninglessLines is table of varchar2(4000);
2 /

Type created.

SQL>
SQL> create or replace function StuffItUp( ctable varchar2, columnsList TColumnNames )
2 return TLotsOfMeaninglessLines
3 pipelined is

  4          type    TRefCursor is REF CURSOR;
  5          sql$    varchar2(4000);
  6          c$      TRefCursor;
  7          col$    TColumnValues;
  8          line$   varchar2(4000);
  9  begin
 10          sql$ := 'SELECT TColumnValues( ';
 11          for i in 1..columnsList.Count-1
 12          loop
 13                  sql$ := sql$  || columnsList(i) || ',';
 14          end loop;
 15          sql$ := sql$  || columnsList( columnsList.Count ) || ')

FROM '||ctable;
16
17 PIPE ROW( sql$ );
18

 19          open c$ for sql$;
 20          loop
 21                  fetch c$ into col$;
 22                  exit when c$%NOTFOUND;
 23
 24                  line$ := '';
 25                  for i in 1..col$.Count
 26                  loop
 27                          line$ := line$ || col$(i) || '|';
 28                  end loop;
 29
 30                  PIPE ROW( line$ );
 31          end loop;
 32
 33          close c$;
 34          return;

35 end;
36 /

Function created.

SQL>
SQL>
SQL> col LINE format a79 truncated
SQL> select
  2          column_value as LINE
  3  from       TABLE( StuffItUp( 'USER_OBJECTS',
TColumnNames('OBJECT_NAME','OBJECT_ID','OBJECT_TYPE') ))
  4  where      rownum < 11;

LINE

SQL>
SQL> alter session set nls_date_format='yyyymmdd hh24miss';

Session altered.

SQL>
SQL> select

  2          column_value as LINE
  3  from       TABLE( StuffItUp( 'USER_TAB_COLUMNS',
TColumnNames('TABLE_NAME','COLUMN_NAME','LAST_ANALYZED') ))
  4  where      rownum < 11;

LINE

SELECT TColumnValues( TABLE_NAME,COLUMN_NAME,LAST_ANALYZED) FROM USER_TAB_COLUM

ABC|NLIST||
BIG_TABLE|OBJECT_ID|20041029 092427|
BIG_TABLE|OBJECT_NAME|20041029 092427|
BIG_TABLE|OBJECT_TYPE|20041029 092427|
BIG_TABLE|CREATED|20041029 092427|
BIG_TABLE|DESCR|20041029 092427|
BILLING_PERIODS|ID||

BILLING_PERIODS|TARIFF_CODE||
BILLING_PERIODS|MONTH|| 10 rows selected.

SQL>
==

Of course, formatting can include quotes around strings, different column delimiters, stripping of special characters from text, automatic formatting of non-varchar columns and so on.

There are also alternatives to this - like having the caller pass a SQL statement and then using DBMS_SQL to parse it, dynamically determine the column names and produce CSV output. Issue here is bind variables. You don't want the caller to hardcode literals into the SQL predicate and cause non-sharable SQL to be generated.. there is ALTER SESSION thugh that allows you to force cursor sharing..

Thus a few ways this can be hacked.

--
Billy

Received on Fri Jul 22 2005 - 04:35:19 CDT