Removing PL/SQL Code from Oracle spool

Question

Executing the following code spools the PL/SQL code in the outputfile 'user.sql'. Is there a way to remove the PL/SQL code from the outputfile? Only the 'alter user ...' statements have to stay in the outputfile.

set serveroutput on
SET TERMOUT  OFF
SET ECHO     OFF
SET FEEDBACK OFF
SET LINESIZE 140
SET PAGESIZE 0

EXEC dbms_output.enable(NULL);
SPOOL user.sql

DECLARE
   vVersion   VARCHAR2 (100);
BEGIN
   /* Check Version */
   SELECT version
     INTO vVersion
     FROM PRODUCT_COMPONENT_VERSION where product like '%Oracle Database 12c%';

   --
   /* loop through users */
   FOR i IN (SELECT *
               FROM DBA_USERS_WITH_DEFPWD)
   LOOP
      /* print a statement, based on vVersion, for the current user */
      IF (vVersion = '12.1.0.2.0')
      THEN
         DBMS_OUTPUT.put_line (
               'Alter user '
            || i.username
            || ' identified by values '
            || '''462368EA9F7AD215'''
            || ';');
      ELSE
         DBMS_OUTPUT.put_line (
               'Alter user '
            || i.username
            || ' identified by values '
            || '''Invalid Password'''
            || ';');
      END IF;
   END LOOP;
END;
/
SPOOL OFF
set serveroutput off
@user.sql

Example Output:

SQL> DECLARE 2 vVersion VARCHAR2 (100); 3 BEGIN 4 /* Check Version / 5 SELECT version 6 INTO vVersion 7
FROM PRODUCT_COMPONENT_VERSION where product like '%Oracle Database 12c%'; 8 9 -- 10 / loop through users */ 11 FOR i IN (SELECT * 12 FROM DBA_USERS_WITH_DEFPWD) 13
LOOP 14 /* print a statement, based on vVersion, for the current user */ 15 IF (vVersion = '12.1.0.2.0') 16
THEN 17 DBMS_OUTPUT.put_line ( 18 'Alter user ' 19 || i.username 20 || ' identified by values ' 21 || '''462368EA9F7AD215''' 22
|| ';'); 23 ELSE 24 DBMS_OUTPUT.put_line ( 25
'Alter user ' 26 || i.username 27 || ' identified by values ' 28 || '''Invalid Password''' 29 || ';'); 30 END IF; 31 END LOOP; 32 END; 33 / Alter user GSMUSER identified by values '462368EA9F7AD215';
Alter user MDSYS identified by values '462368EA9F7AD215';
Alter user OLAPSYS identified by values '462368EA9F7AD215';
Alter user LBACSYS identified by values '462368EA9F7AD215';
Alter user ORDDATA identified by values '462368EA9F7AD215';
Alter user ORDSYS identified by values '462368EA9F7AD215';
Alter user DVF identified by values '462368EA9F7AD215';
Alter user SYSDG identified by values '462368EA9F7AD215'; ....

Can you put that code in a script and then run the script with @ from the SQL prompt? — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2016-04-07T11:08:22

SQL*Plus doesn't honour the set echo off for an interactive session. This is documented:

ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.

If you paste that code into an SQL plus session you'll see that behaviour. You'll also see it if you do input redirection, e.g. from a Linux command line:

sqlplus user/passwd@tns < script.sql

You'll see lots of "SQL>" prompts echoed to your terminal as well, plus the alter statements.

You can either use the silent flag:

sqlplus -s user/passwd@tns < script.sql

or run as a script instead of interactively (though you need to add an exit at the end of the script, otherwise it'll appear to hang while it waits for you to type that):

sqlplus user/passwd@tns @script.sql

or both:

sqlplus -s user/passwd@tns @script.sql

The silent flag suppresses the SQL*Plus banners as well, so it will produce less clutter overall - though those don't go in the spool file anyway.

Not relevant to the question, but you don't need to spool the commands to a file and then run that as a script. You can just run the alter commands directly as dynamic SQL:

   LOOP
      IF (vVersion = '12.1.0.2.0')
      THEN
         execute immediate 'Alter user '
            || i.username
            || ' identified by values '
            || '''462368EA9F7AD215'''
            || ';');
      ELSE
         execute immediate 'Alter user '
            || i.username
            || ' identified by values '
            || '''Invalid Password'''
            || ';');
      END IF;
   END LOOP;

Removing PL/SQL Code from Oracle spool

1 Answers