CODEWITHSUNDEEP
plsqlspool
zajcev
zajcev

Reputation: 81

Spooling only DBMS_OUTPUT

I have a procedure I need to spool the output from this procedure into a new sql file.

CREATE OR REPLACE PROCEDURE lock_users_prep
AS
  username varchar2(60);
 BEGIN
    FOR user IN (SELECT username
                FROM dba_users WHERE username NOT IN (SELECT name FROM hr.katastr_users))
  LOOP
    dbms_output.put_line('alter user "'||user.username||'" account lock;');
  END LOOP;
END;
 /


set heading off arraysize 1 pages 0 feedback off echo off verify off
SPOOL lockall.sql
  EXEC lock_users_prep
SPOOL OFF lockall.sql

But in my file lockall.sql I still have the first row like this:

> EXEC lock_users_prep
alter user "SYSTEM" account lock;
alter user "SYS" account lock;
alter user "OLAPSYS" account lock;

I need to get rid of the first line. Any ideas what set command to use?

Upvotes: 1

Views: 2456

Answers (1)

AdamRossWalker
AdamRossWalker

Reputation: 314

If you use spooling to create files you are at the mercy of the host and the current environment. For example many of the "set" commands in SQL*Plus. The DBMS_OUTPUT buffer may also be limited which risks an exception if you send too much data.

The correct way to write files from PL/SQL is to use the UTL_FILE package. This will give you more control over your output and avoid problems such as in this question.

Upvotes: 2

Related Questions