General discussion
-
CreatorTopic
-
February 18, 2000 at 8:53 am #2082772
Spool Command in PL/SQL
Lockedby bishoy.janardhanan · about 25 years ago
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.
Topic is locked -
CreatorTopic
All Comments
-
AuthorReplies
-
-
February 18, 2000 at 9:59 am #3897753
Spool Command in PL/SQL
by fofa · about 25 years ago
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.
-
September 20, 2000 at 7:02 pm #3740471
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
The question was auto-closed by TechRepublic
-
-
February 18, 2000 at 11:10 am #3897749
Spool Command in PL/SQL
by jim · about 25 years ago
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-
September 20, 2000 at 7:02 pm #3740472
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
The question was auto-closed by TechRepublic
-
-
February 21, 2000 at 2:23 am #3897689
Spool Command in PL/SQL
by snowcycle · about 25 years ago
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, Column3Order by not required, add parameters and data formatting to taste.
-
September 20, 2000 at 7:02 pm #3740473
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
The question was auto-closed by TechRepublic
-
-
February 28, 2000 at 9:35 am #3900576
Spool Command in PL/SQL
by ranjeevk · about 24 years, 12 months ago
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.-
September 20, 2000 at 7:02 pm #3740474
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
The question was auto-closed by TechRepublic
-
-
February 28, 2000 at 3:27 pm #3900560
Spool Command in PL/SQL
by alesu · about 24 years, 12 months ago
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.
-
September 20, 2000 at 7:02 pm #3740475
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
The question was auto-closed by TechRepublic
-
-
April 21, 2000 at 12:02 am #3899592
Spool Command in PL/SQL
by vs · about 24 years, 10 months ago
In reply to Spool Command in PL/SQL
You may set a buffer size for dbms_output by command: dbms_output.enable(buffer_size=512000);
Buffer size cannot be greater than 1000000.-
September 20, 2000 at 7:02 pm #3740476
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
The question was auto-closed by TechRepublic
-
-
September 20, 2000 at 7:02 pm #3740470
Spool Command in PL/SQL
by bishoy.janardhanan · about 24 years, 5 months ago
In reply to Spool Command in PL/SQL
This question was auto closed due to inactivity
-
-
AuthorReplies