title: Oracle plsql switch case and sqlplus spool
body: <p>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? </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Edit:</p>
  
  <p>Code which is working looks like this:</p>
  
  <pre><code>spool c:/test/user.sql
  
  select 'Alter user ' || username || ' identified by values ' ||  '''Invalid Password''' || ';'
     from DBA_USERS_WITH_DEFPWD;
  
  spool off
  </code></pre>
  
  <p>Spool output example:</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>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.</p>