Oracle plsql switch case and sqlplus spool

Question

The following query runs in an error at the THAN statement. Is it possible to use a select in the THAN statement? Any ideas on how to make it work?

SET SERVEROUTPUT ON
SET TERMOUT  OFF
SET ECHO     OFF
SET FEEDBACK OFF
SET LINESIZE 140
SET PAGESIZE 0  

exec dbms_output.enable(NULL);

SPOOL C:\test\user.sql

BEGIN

SELECT DISTINCT version,
  CASE
  WHEN version = '12.1.0.2.0' 
  THEN 
     dbms_output.put_line(''select' || 'Alter user ' || username || ' identified by values ' ||  '''EE3FD1E715941451''' || ';''); from DBA_USERS_WITH_DEFPWD;
  ELSE 
     dbms_output.put_line(''select' || 'Alter user ' || username || ' identified by values ' ||  '''Invalid Password''' || ';''); from DBA_USERS_WITH_DEFPWD;
FROM PRODUCT_COMPONENT_VERSION;
END; 

SPOOL OFF

Edit:

Code which is working looks like this:

spool c:/test/user.sql

select 'Alter user ' || username || ' identified by values ' ||  '''Invalid Password''' || ';'
   from DBA_USERS_WITH_DEFPWD;

spool off

Spool output example:

Alter user GSMUSER identified by values 'Invalid Password';
Alter user MDSYS identified by values 'Invalid Password';
Alter user OLAPSYS identified by values 'Invalid Password';
Alter user LBACSYS identified by values 'Invalid Password';

The script is not working with new Oracle DB Versions. Since Oracle 12.1.0.2.0 it’s not possible to set the password to an invalid password. I need to build in version identification for this issue. All Oracle Databases with a version below 12.1.0.2.0 should be treated with the old script and the passwords should be set to “invalid password”. All newer versions should get some kind of standard password for now.

You can't use DBMS_OUTPUT in SELECT statement. you are mixing SQL and PL/SQL. They are different engines. Also, your SELECT statement actually makes no sense. Explain your requirement in proper words. — Lalit Kumar B, Mar 31 '16 at 09:27
@LalitKumarB he's trying to do this: http://stackoverflow.com/questions/36302810/switch-case-for-oracle-versions-to-set-invalid-passwords-for-standard-users — Sathyajith Bhat, Mar 31 '16 at 09:52

score 4 · Accepted Answer · edited Apr 08 '16 at 11:23

4

Maybe you need something like this:

...
DECLARE
    vVersion varchar2(100);
BEGIN    
    /* get the version */
    SELECT DISTINCT version
    into vVersion
    from PRODUCT_COMPONENT_VERSION;
    --
    /* loop through users */
    for i in ( select * from DBA_USERS_WITH_DEFPWD) loop
        /* print a different 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 ' ||  '''EE3FD1E715941451''' || ';');
        else
            dbms_output.put_line('Alter user ' || i.username || ' identified by values ' ||  '''Invalid Password''' || ';');
        end if;
    end loop;
END; 
...

edited Apr 08 '16 at 11:23

r0tt

379
3
20

answered Mar 31 '16 at 09:50

Aleksej

22,443
5
33
38

Thank you, that is what I was trying to do. Executing the script gives me still some problems. It asks me to declare version and username. Version can be fixed by changing the if ( version = '12.1.0.2.0' ) to if ( vVersion = '12.1.0.2.0' ). But I can’t manage to fix the username. Currently it does not seem to take the username from DBA_USERS_WITH_DEFPWD. I didn’t find a way to fix that yet. – r0tt Mar 31 '16 at 12:03
Need to use SET SERVEROUTPUT ON to get an output to the spool. – r0tt Apr 08 '16 at 11:17

r0tt · Answer 2 · 2016-04-08T11:16:10.503

If it's not possible to use PL/SQL:

SET TERMOUT  OFF
SET ECHO     OFF
SET LINESIZE 140
SET FEEDBACK OFF
SET PAGESIZE 0

spool user.sql

SELECT    'Alter user '
       || A.USERNAME
       || ' identified by values '
       || '''EE3FD1E715941451'''
       || ';'
  FROM DBA_USERS_WITH_DEFPWD a,
       (SELECT DISTINCT version
          FROM PRODUCT_COMPONENT_VERSION) b
 WHERE version = '12.1.0.2.0'
UNION ALL
SELECT    'Alter user '
       || A.USERNAME
       || ' identified by values '
       || '''Invalid Password'''
       || ';'
  FROM DBA_USERS_WITH_DEFPWD a,
       (SELECT DISTINCT version
          FROM PRODUCT_COMPONENT_VERSION) b
 WHERE version != '12.1.0.2.0';


 spool off
 @user.sql

score 0 · Answer 3 · answered Apr 08 '16 at 11:14

Another version which solves the problem without PL/SQL and correct spool:

SET TERMOUT  OFF
SET ECHO     OFF
SET LINESIZE 140
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL user.sql

SELECT    'alter user ' || username || ' identified by values '''
       || CASE
             WHEN b.version = '12.1.0.2.0' THEN '462368EA9F7AD215'
             ELSE 'Invalid Password'
          END
       || ''';'
  FROM DBA_USERS_WITH_DEFPWD a,
       (SELECT VERSION
          FROM PRODUCT_COMPONENT_VERSION
         WHERE UPPER (product) LIKE '%DATABASE%') b;

SPOOL OFF
@user.sql

Oracle plsql switch case and sqlplus spool

3 Answers3