Fun with SQL - Part 39

I've been busy helping to get an environment set up for an Exadata POC. w00t.

What this means, is getting all of these disparate data structures into a single Oracle database. OBIEE is run on Windows and thankfully, 11gR2 was released for 64bit and 32bit this week. I downloaded it, installed the software (4 minutes) then used the DBCA to install the database (3 minutes). I really need to get a better computer because these activities take too long on my itty bitty laptop.

Anyway, there are 5 schemas across 3 databases. I have to 1, get access to the specific environment and 2, copy over the data. I've used a mixture of COPY, Data Pump, import/export from SQL Developer and database links to move all this stuff around.

Finally I get to my last table, a table full of usernames and passwords.

Only the password column is of the LONG variety.

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Bollocks.

I then go to AskTom because I know there is an easy way to convert to VARCHAR2. Nope, source database doesn't recognize ROWIDs in this context.

Hmmm...how about TO_LOB? Nope.

I gave up due to a time constraint.

I then asked one of the other developers if he could dump the table for me. While I was explaining it to him...I realized I didn't need the stupid password column. Why? Because this was just for a POC/demo, the passwords would all be the same. CREATE TABLE t AS SELECT username... would work just fine. And it did.

Finally, to the fun part, at least for me.

All of these users need database accounts as well. How about scripting that out?

What would I need? CREATE USER statements and GRANT create session TO user; statements.

I started with, what would be, my inner query:

SELECT
  username,
  'CREATE USER ' || username || ' IDENTIFIED BY password DEFAULT TABLESPACE USERS;' create_s,
  'GRANT create session TO ' || username || ';' grant_s
FROM t
ORDER BY username

That's all well and good, but I need this to run in order and a column/column approach wouldn't work (without manual intervention anyway). Now that I think about it...I could have solved this a lot easier...but I'll save that for the end. It was too much "fun" trying to figure out this solution. Can you say Complicator's Gloves?

Indulge me for a moment.

Basically, with 3 columns (really only 2, I don't need to display the username), I need to pivot these 2 columns into 2 rows. I can use dual for that.

SELECT rownum my_rownum
FROM dual
  CONNECT BY LEVEL <= 2;

Now I hook them up:

SELECT
  username,
  ( CASE WHEN MOD( rownum, 2 ) = 0 THEN 1 ELSE 2 END ) c,
  ( CASE 
      WHEN MOD( rownum, 2 ) = 0 THEN create_s 
      ELSE grant_s 
    END ) t
FROM
  (
    SELECT
      username,
      'CREATE USER ' || username || ' IDENTIFIED BY password DEFAULT TABLESPACE USERS;' create_s,
      'GRANT create session TO ' || username || ';' grant_s
    FROM t
    ORDER BY username
  ) a,
  dual 
    CONNECT BY LEVEL <= 2
ORDER BY 1, 2;

This works, sort of. Adding a DISTINCT clause makes it work...but I only have 1 row for each user. Hmmm...

I add the rownum into the outer query to see what it's doing.

OK, using the rownum was the wrong approach. How about using ROW_NUMBER in the inner query? I could set the ORDER BY clause to order by the username.

SELECT
  username,
  ( CASE WHEN MOD( my_rownum, 2 ) = 0 THEN 1 ELSE 2 END ) c,
  ( CASE 
      WHEN MOD( my_rownum, 2 ) = 0 THEN create_s 
      ELSE grant_s 
    END ) t
FROM
  (
    SELECT
      ROW_NUMBER() OVER ( ORDER BY username ) my_rownum,
      username,
      'CREATE USER ' || username || ' IDENTIFIED BY password DEFAULT TABLESPACE USERS;' create_s,
      'GRANT create session TO ' || username || ';' grant_s
    FROM t
    ORDER BY username
  ) a,
  dual 
    CONNECT BY LEVEL <= 2
ORDER BY 1, 2;

Closer. But not quite right. I'm not getting the order I want (CREATE USER then GRANT). Let's use dual for that rownum.

Here's my final query (sorry, I'm tired and crazy):

SELECT DISTINCT
  username,
  ( CASE WHEN MOD( b.m, 2 ) = 0 THEN 1 ELSE 2 END ) c,
  ( CASE 
      WHEN MOD( b.m, 2 ) = 0 THEN create_s 
      ELSE grant_s 
    END ) t
FROM
  (
    SELECT
      username,
      'CREATE USER ' || username || ' IDENTIFIED BY password DEFAULT TABLESPACE USERS;' create_s,
      'GRANT create session TO ' || username || ';' grant_s
    FROM pass_sync
    ORDER BY username
  ) a,
  (
    SELECT rownum m
    FROM dual
      CONNECT BY LEVEL <= 2
   ) b
ORDER BY 1;

So what's the easy way?

SELECT
  username,
  'CREATE USER ' || username || ' IDENTIFIED BY password DEFAULT TABLESPACE USERS;' || chr(10) ||
  'GRANT create session TO ' || username || ';' s
FROM p

Yeah, that || CHR(10) || would have worked just fine. Oh well.