Re: Write PL/SQL query results to a file
Date: 16 Jul 2003 08:35:07 -0700
Message-ID: <4c657437.0307160735.3be7e567_at_posting.google.com>
Martin Burbridge <pobox002_at_bebub.com> wrote in message news:<Xns939ED964C1B1Dpobox002bebubcom_at_216.148.227.77>...
> shhmoo_at_excite.com (robert) wrote in news:114e40a3.0306180716.5aa0b4b8
> _at_posting.google.com:
>
> > I'm trying to write the results of my PL/SQL query to a file. I have
> > came across a number of possible* solutions such as DBMS_PIPE and
> > UTL_FILE. Basically, I am running a batch file on an NT box and would
> > like to obtain the results from the DB server running Oracle
> > 8.1.7.4.0.
> >
> > So far I have been able to get export a file to my local machine by
> > doing:
> > exp 'usr/passwd _at_db' full=no file=%ORACLE_SID%.dmp %ORACLE_SID%.log
> >
> > I would like to have this file be the output of my query instead. Any
> > info appreciated. Thanks in advance.
>
> When you say PL/SQL query do you mean you have a stored procedure that
> returns a ref cursor?
>
> SQL> create or replace package types as
> 2 type rc_t is ref cursor;
> 3 end;
> 4 /
>
> Package created.
>
> SQL> create or replace function f
> 2 return types.rc_t as
> 3 rc types.rc_t;
> 4 begin
> 5 open rc for
> 6 select ename, sal, hiredate
> 7 from emp where rownum < 4;
> 8 return rc;
> 9 end;
> 10 /
>
> Function created.
>
> If so look up SQL*Plus in the documentation at http://tahiti.oracle.com.
> PL/SQL runs in the database so its IO is not great and what there is
> doesn't have any concept of users at terminals somewhere. So you are
> always better off using a client tool for this kind of thing, and
> SQL*Plus is ideal for command line batch files.
>
> Here is an example script that uses the above function f and writes the
> query results to the file out.txt. You'll need to refer to the
> documentation to understand what the commands do and modify it if needed.
>
> set termout off
> set verify off
> set timing off
> set trimspool on
> var c refcursor
> exec :c := f
> spool out.txt
> print c
> spool off
> exit
>
> Run the script, f2file.sql in this example, from the command line like
> this.
>
> $ sqlplus -s martin/bub _at_f2file
>
> $ cat out.txt
>
> ENAME SAL HIREDATE
> ---------- ---------- ---------
> SMITH 800 17-DEC-80
> ALLEN 1600 20-FEB-81
> WARD 1250 22-FEB-81
>
> If you are using a cursor for loop, try and rewrite as a SQL statement
> and start again at _ look up SQL*Plus ... _ <vbg>
Here is a PL/SQL solution using UTL_FILE to take a SELECT statement and write the output to a flat file. This is mostly borrowed from Tom Kyte (iirc). Many thanks. =)
create or replace procedure csv( p_query in varchar2,
p_separator in varchar2 default
chr(9),
p_path in varchar2,
p_fileName in varchar2,
p_reportName in varchar2 )
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_colString varchar2(4000);
lv_fileHandle UTL_FILE.FILE_TYPE;
lv_path varchar2(50) default
'/u01/home/cnelson/flat_files';
lv_fileName varchar2(25) default 'MyResultSet.txt';
l_col_count number default 0;
l_columns dbms_sql.desc_tab;
l_column_rec dbms_sql.desc_rec;
err_msg varchar2(50);
begin
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; --determines the # of columns
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);
lv_fileName := p_fileName;
lv_path := p_path;
lv_fileHandle := UTL_FILE.FOPEN(lv_path, lv_fileName, 'w');
--begin column names
dbms_sql.describe_columns( l_theCursor, l_col_count, l_columns
);
for i_sub in 1 .. l_col_count loop
l_column_rec := l_columns( i_sub );
l_colString := l_colString || l_separator ||
l_column_rec.col_name;
l_separator := p_separator;
end loop;
utl_file.put_line( lv_fileHandle, l_colString );
--end column names
l_separator := ''; --reset to null
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := ''; --reset concatenated strings once row is
complete
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put(lv_fileHandle, l_separator || l_columnValue);
l_separator := p_separator;
end loop;
utl_file.new_line(lv_filehandle);
end loop;
dbms_sql.close_cursor(l_theCursor);
UTL_FILE.FCLOSE(lv_fileHandle);
- insert into cnelson.reports
- values(p_reportName, 1, 1, lv_path, lv_filename, sysdate);
- commit;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
err_msg := 'Invalid Path';
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN UTL_FILE.INVALID_MODE THEN
err_msg := 'Invalid Mode';
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN UTL_FILE.INVALID_FILEHANDLE THEN
err_msg := 'Invalid Filehandle';
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN UTL_FILE.INVALID_OPERATION THEN
err_msg := 'Invalid Operation';
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');
INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN UTL_FILE.READ_ERROR THEN
err_msg := 'Read Error';
RAISE_APPLICATION_ERROR(-20104,'Read Error'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN UTL_FILE.WRITE_ERROR THEN
err_msg := 'Write Error';
RAISE_APPLICATION_ERROR(-20105,'Write Error'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN UTL_FILE.INTERNAL_ERROR THEN
err_msg := 'Internal Error';
RAISE_APPLICATION_ERROR(-20106,'Internal Error'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN NO_DATA_FOUND THEN
err_msg := 'No Data Found';
RAISE_APPLICATION_ERROR(-20107,'No Data Found'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN VALUE_ERROR THEN
err_msg := 'Value Error';
RAISE_APPLICATION_ERROR(-20108,'Value Error'); INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT; WHEN OTHERS THEN
err_msg := sqlerrm;
RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error error:' || sqlerrm );
INSERT INTO error_table
VALUES (
'Create Report: ' || p_reportName
, err_msg
, -1
, SYSDATE
);
COMMIT;
if utl_file.is_open( lv_filehandle )
then
utl_file.fclose( lv_filehandle );
end if;
end;
-- csv
Received on Wed Jul 16 2003 - 17:35:07 CEST