Testing multiple queries from input file

Question

I have set of tables and need to execute select query based on different users. Table names are stored in file and I am looking forward to write a script to execute a script to read table name from the file and execute select query as specific user. Need to validate if the query was successful or failure.

Any suggestion on the tools for Oracle database / scripting(python or java) to be used for automation.

Thanks in advance.

• Colin 't Hart: What did you try yourself? Why or what didn't (it) work?
• Santosh M: I have written a java program to read the table names from a file and execute the select query against with specific users from another file. If select statement fails, it would log error and continue with others.

Answer 1

It looks like you need to write dynamic SQL. Dynamic SQL is SQL the creates SQL that can get run. Here is a simple example of dynamic SQL.

SELECT 'ALTER ' ||
 CASE object_type 
 WHEN 'PACKAGE BODY' THEN 'PACKAGE '
 WHEN 'TYPE BODY' THEN 'TYPE '
 ELSE object_type ||' '
 END || owner ||'.' || object_name ||' '||
 CASE object_type 
 WHEN 'PACKAGE BODY' THEN 'COMPILE BODY;'
 WHEN 'TYPE BODY' THEN 'COMPILE BODY;'
 ELSE 'COMPILE;'
 END text
 FROM dba_objects
 WHERE status = 'INVALID'
 ORDER BY 1;

You should also have formatting in the SQL to keep the output of the SQL as clean as possible.

SET TERM OFF
COLUMN text FORMAT A150
SET ECHO OFF
SET TRIMSPOOL ON
SET TAB OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 150
SET TIMING OFF
SET TERM ON

Then you just need to write the SQL that generates SQL, if you need to connect as a different user you can include a line like:

SELECT 'CONNECT user1/cdjkfghljsdg@sidname' text from dual;

But you need to figure out how to write the SQL, since you have not given many details. You will spool to a file, then turn on feedback, set pagesize 50 or so, probably set timing on etc, then run the output of the spool file. You may want to spool to a log file during the run phase. There are alot of examples, you just need to query for an example that matches what you want to do.