0

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.

2
  • What did you try yourself? Why or what didn't (it) work? – Colin 't Hart Jun 2 '14 at 8:53
  • 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. – Santosh M Jun 3 '14 at 0:58
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.