Re: How to dump table to field delimited ASCII file?

This package works OK in 8.1.7:  

create or replace type TY_SH_STRING as object   (string varchar2(2000),
  static function Construct return ty_sh_string,     pragma RESTRICT_REFERENCES (DEFAULT,RNPS,WNPS,RNDS,WNDS) );
/
create or replace type CL_SH_STRING as table of ty_sh_string; /

CREATE OR REPLACE PACKAGE P_UTILITY is
-- function to export table contents as a CSV ASCII file.

• To get results use syntax:
• select * from table (select cast(p_utility.CreateTableCSV
• ('SCHEMANAME','TABLENAME') as cl_sh_string) from dual);
• (in SQLPLUS spool output to create ASCII file) function CreateTableCSV ( pSchemaName in varchar2, pTableName in varchar2, pSeparator in varchar2 default ',', -- the separator character pWhere in varchar2 default '1=1') -- option where clause
  • (omit the ' where ') return cl_sh_string; PRAGMA RESTRICT_REFERENCES(CreateTableCSV, WNDS, WNPS, RNPS); end P_UTILITY; / CREATE OR REPLACE PACKAGE BODY P_UTILITY is function ObjectExists ( ObjectName VarChar2, v_Owner VarChar2 default null) return boolean is N Number; begin select count(*) into n from dba_objects where owner = upper(v_Owner) and object_name = upper(ObjectName); return (N <> 0); end;
    --
    
• FUNCTION to return column list for specified table, function TableColumns( pSchemaName in varchar2, pTableName in varchar2) return varchar2 is Result varchar2(2000); begin for rec in (select column_name||',' ColumnString from all_tab_columns where table_name = upper(pTableName) and owner = upper(pSchemaName) order by column_id) loop Result := Result || rec.ColumnString; end loop; Result := rtrim(Result,', '); --remove trailing comma from last column name return Result; end TableColumns;
  --
  
• FUNCTION to return column names for specified table for use in select statement function TableCSVString( pSchemaName in varchar2, pTableName in varchar2, pInserts in boolean, pSeparator in varchar2 default ',', pToSchema in varchar2 default null) return varchar2 is Result varchar2(2000); begin if pInserts then Result := '''insert into '||pToSchema||'.'||pTableName|| ' ('||TableColumns(pSchemaName,pTableName)||') values (''''''||'; else Result := '''''''''||'; end if;
  • initially create string of column values using chr(9) (=TAB) as separator
  • use replace to escape any apostrophes within strings for rec in (select 'replace('||column_name||','''''''','''''''''''') ||'''''''||chr(9)||'''''''||' ColumnString from all_tab_columns where table_name = upper(pTableName) and owner = upper(pSchemaName) order by column_id) loop Result := Result || rec.ColumnString; end loop; --remove trailing separator from last column definition if pInserts then Result := rtrim(Result,'|'' '||chr(9))||'||'''''');'''; else Result := rtrim(Result,'|'' '||chr(9))||'||'''''''''; end if;
  • replace all chr(9) with required separator Result := translate(Result,chr(9),pSeparator); return Result; end TableCSVString;
    --
    
• function to export table contents as a CSV ASCII file.
• To get results use syntax:
• select * from table (select cast(p_utility.CreateTableCSV
• ('SCHEMANAME','TABLENAME') as cl_sh_string) from dual);
• (in SQLPLUS spool output to create ASCII file)
• outputs text lines in format
• 'col1value','col2value',...
• apostrophe characters within char/varchar2 columns are escaped
• Current known limitations:
• max 2000 characters per line
• csv string cannot straddle multiple lines
• supported column types are char, varchar2, number, date function CreateTableCSV ( pSchemaName in varchar2, pTableName in varchar2, pSeparator in varchar2 default ',', pWhere in varchar2 default '1=1') return cl_sh_string is Results cl_sh_string := cl_sh_string(); type cu_type is ref cursor; cu cu_type; i integer := 0; Str varchar2(2000); begin if ObjectExists (pTableName, pSchemaName) then i := 0; open cu for 'select '|| TableCSVString(pSchemaName,pTableName,false,pSeparator,pSchemaName)|| ' from '||pSchemaName||'.'||pTablename||' where '||pWhere; loop fetch cu into Str; exit when cu%notfound; Results.Extend; i := i+1; Results(i) := ty_sh_string(Str); end loop; close cu; end if; return Results; end CreateTableCSV; end P_UTILITY; /