Here’s a common task: You need to create a database user with read only access. You have some power users who know how to use SQL, and they are allowed to see all of the data in the system. You can use these commands to create such a user (in Oracle):
create user PSRO identified by PSRO; grant create session, select any table, select any dictionary to PSRO;
Note: You can probably find this all over the web, but I wanted to capture it on my blog so that I could find the syntax easier.
Here’s a simple little trick for Application Engines that have Temporary Tables assigned.
When you have temporary tables, you don’t know for sure which Temporary Table it is using. You could write a quick select and change the number on the end until you find the one you are looking for, but here is a slightly quicker method.
Note: This is designed for Oracle, but you could easily change it up to work with SQL Server.
First, build a select statement that will list all of the tables. We’ll assume our Temporary Table Record is MY_TEMP_TAO. You can substitute with your specific record.
SELECT 'UNION SELECT ''' || TABLE_NAME || ''' TBL, A.* FROM SYSADM.' || TABLE_NAME || ' A' FROM DBA_TABLES WHERE table_name LIKE 'PS_MY_TEMP_TAO%';
Update (thanks to Nicolas): You can avoid having to remove the first UNION if you use something more like this:
SELECT 'SELECT ''' || TABLE_NAME || ''' TBL, A.* ' || 'FROM SYSADM.' || TABLE_NAME || ' A' || case when count(*)over()=rownum then ';' else ' UNION ' end FROM ALL_TABLES WHERE table_name LIKE 'PS_MY_TEMP_TAO%';
The output should look something like this:
UNION SELECT 'PS_MY_TEMP_TAO' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO A UNION SELECT 'PS_MY_TEMP_TAO1' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO1 A UNION SELECT 'PS_MY_TEMP_TAO2' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO2 A UNION SELECT 'PS_MY_TEMP_TAO3' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO3 A UNION SELECT 'PS_MY_TEMP_TAO4' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO4 A UNION SELECT 'PS_MY_TEMP_TAO5' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO5 A UNION SELECT 'PS_MY_TEMP_TAO6' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO6 A;
You just simply need to delete the first Union to make something like this:
SELECT 'PS_MY_TEMP_TAO' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO A UNION SELECT 'PS_MY_TEMP_TAO1' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO1 A UNION SELECT 'PS_MY_TEMP_TAO2' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO2 A UNION SELECT 'PS_MY_TEMP_TAO3' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO3 A UNION SELECT 'PS_MY_TEMP_TAO4' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO4 A UNION SELECT 'PS_MY_TEMP_TAO5' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO5 A UNION SELECT 'PS_MY_TEMP_TAO6' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO6 A;
If you know your temp table has the process instance as the key and you know the one you are looking for, you could do something like this:
SELECT * FROM (
SELECT 'PS_MY_TEMP_TAO' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO A
UNION SELECT 'PS_MY_TEMP_TAO1' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO1 A
UNION SELECT 'PS_MY_TEMP_TAO2' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO2 A
UNION SELECT 'PS_MY_TEMP_TAO3' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO3 A
UNION SELECT 'PS_MY_TEMP_TAO4' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO4 A
UNION SELECT 'PS_MY_TEMP_TAO5' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO5 A
UNION SELECT 'PS_MY_TEMP_TAO6' TBL, A.* FROM SYSADM.PS_MY_TEMP_TAO6 A;
) Z WHERE PROCESS_INSTANCE = 12345