General discussion

Locked

Spool Command in PL/SQL

By bishoy.janardhanan ·
How can we use the spool command in a Oracle PL/SQL script ? i need to output results of a query without using a user created table. the use of dbms_output.put_line gives a overflow error when more than the length is more than 256 bytes.

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Spool Command in PL/SQL

by fofa In reply to Spool Command in PL/SQL

You can not use SPOOL inside a PL/SQL block. We usually write to a user/temp table, exit the PL/SQL block and then spool.

Collapse -

Spool Command in PL/SQL

by bishoy.janardhanan In reply to Spool Command in PL/SQL

The question was auto-closed by TechRepublic

Collapse -

Spool Command in PL/SQL

by jim In reply to Spool Command in PL/SQL

This is a way to send the output to directly to the printer depending on the operating system and version of Oracle you are using. Our DBA at work uses the example below to send a list of tables to the printer. Hope it helps.

SPOOL TABLIST.LST
REM *****************************************************************
REM Prints Report Showing tables available
REM *****************************************************************
SET PAGESIZE 71 LINESIZE 98 VERIFY OFF
SET PAGESIZE 71
CLEAR COL
CLEAR BREAK
COL TODAY NEW_VALUE CURRENT_DATE NOPRINT
COL TIME NEW_VALUE CURRENT_TIME NOPRINT
TTITLE LEFT "DATE: " CURRENT_DATE CENTER 'ARIZONA DEPARTMENT OF HEALTH SERVICES' COL 87 "PAGE: " FORMAT 999 -
SQL.PNO SKIP 1 LEFT "TIME: " CURRENT_TIME CENTER 'BEHAVIORAL HEALTH SERVICES' RIGHT -
COL 84 "USERID: " FORMAT A15 SQL.USER SKIP 2 CENTER -
'CLIENT ENROLLMENT DISENROLLMENT AND REPORTING SYSTEM (CEDAR)' SKIP 4
COL USER FORMAT A10 TRUNCATE
COL TABLE_NAME FORMAT A30
SELECT OWNE

Collapse -

Spool Command in PL/SQL

by bishoy.janardhanan In reply to Spool Command in PL/SQL

The question was auto-closed by TechRepublic

Collapse -

Spool Command in PL/SQL

by snowcycle In reply to Spool Command in PL/SQL

You could try using the UTL_FILE set of commands. These output data to a file with a maximum of 1023 characters, so you could open a file:

vfhdl_op_file := UTL_FILE.FOPEN(vs_dir, vs_filename, 'w');
vn_reccount := 0;
FOR lrec_row IN lcur_query LOOP
-- output record
vn_reccount := vn_reccount + 1;
UTL_FILE.PUTF(vfhdl_file, lrec_decl.op_row);
UTL_FILE.NEW_LINE(vfhdl_file, 1);
END LOOP;
-- write trailer record (if you want to!)
UTL_FILE.PUTF(vfhdl_file, '**END OF FILE**, ' || TO_CHAR(vn_reccount, '9999999999'));
UTL_FILE.NEW_LINE(vfhdl_file, 1);
-- close file
UTL_FILE.FCLOSE(vfhdl_file);

Your cursor should be like this:

CURSOR lcur_query IS
SELECT Column1 || ',' || Column2 || ',' || Column3
|| ',' || Column4 || ',' || Column5 || ',' || Column6 AS table_line
FROM USER.TABLE
ORDER BY Column1, Column2, Column3

Order by not required, add parameters and data formatting to taste.

Collapse -

Spool Command in PL/SQL

by bishoy.janardhanan In reply to Spool Command in PL/SQL

The question was auto-closed by TechRepublic

Collapse -

Spool Command in PL/SQL

by ranjeevk In reply to Spool Command in PL/SQL

spool command can't be used inside PL/SQL .
But you can increase the buffer length inside PL/SQL so that you will not get the error of overflow.

Collapse -

Spool Command in PL/SQL

by bishoy.janardhanan In reply to Spool Command in PL/SQL

The question was auto-closed by TechRepublic

Collapse -

Spool Command in PL/SQL

by alesu In reply to Spool Command in PL/SQL

You can keep using dbms_output package, but try using a bigger buffer:

SET SERVEROUTPUT ON SIZE n, where n is the new buffer size.

Collapse -

Spool Command in PL/SQL

by bishoy.janardhanan In reply to Spool Command in PL/SQL

The question was auto-closed by TechRepublic

Back to Web Development Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Software Forums