CODEWITHSUNDEEP
oracle-databaseplsqlcasespool
r0tt
r0tt

Reputation: 379

Oracle plsql switch case and sqlplus spool

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.

Upvotes: 2

Views: 2443

Answers (3)

Aleksej
Aleksej

Reputation: 22949

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; 
...

Upvotes: 4

r0tt
r0tt

Reputation: 379

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

Upvotes: 0

r0tt
r0tt

Reputation: 379

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

Upvotes: 0

Related Questions