Testing Multiple Queries from Input File – Oracle, Java, Scripting, Python

javaoraclepythonscripting

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.

Best Answer

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.

Related Question