General discussion

  • Creator
    Topic
  • #2082772

    Spool Command in PL/SQL

    Locked

    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.

All Comments

  • Author
    Replies
    • #3897753

      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.

    • #3897749

      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

    • #3897689

      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.

    • #3900576

      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.

    • #3900560

      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.

    • #3899592

      Spool Command in PL/SQL

      by vs ·

      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.

    • #3740470

      Spool Command in PL/SQL

      by bishoy.janardhanan ·

      In reply to Spool Command in PL/SQL

      This question was auto closed due to inactivity

Viewing 6 reply threads