Unix to Sqlplus - Log the SQL statement about to execute and result of execution

Question

I am on AIX using ksh. Created a unix script which generates the CREATE OR REPLACE VIEW ... and GRANT .. statements, which are placed in a single .txt file. Now I need to execute the contents in the file (there are around 300 view creation and grant statements) in Oracle and I need to log the sql statement that is about to execute and the feedback of Oracle whether the view is created or not.. My excerpt goes as

sqlplus -s username/password@servername <<EOF
SET ECHO ON;
SET NEWPAGE 0;
SET PAGESIZE 0;
SET LINESIZE 200;
SET LONG 10000000;
SET TRIMSPOOL ON;
SET HEADING OFF;
SET FEEDBACK ON;
SET VERIFY ON;
SET TERMOUT OFF;
SET SQLBLANKLINES ON;
 
SPOOL ${drctry}/${v_timestamp}_sql_execution_log.txt
@${drctry}/${v_date}_sql_statements.txt
SPOOL OFF;

EXIT;

EOF

If the contents of the file _${v_date}sql_statements.txt is

CREATE OR REPLACE VIEW V_TABLE1 AS SELECT * FROM TABLE1;  
GRANT SELECT ON V_TABLE1 TO USER1;
...
CREATE OR REPLACE VIEW V_TABLE300 AS SELECT * FROM TABLE300;
GRANT SELECT ON V_TABLE300 TO USER300;

Expected output:

CREATE OR REPLACE VIEW V_TABLE1 AS SELECT * FROM TABLE1;
View created
GRANT SELECT ON V_TABLE1 TO USER1;
Grant succeeded
...
CREATE OR REPLACE VIEW V_TABLE300 AS SELECT * FROM TABLE300;
View created
GRANT SELECT ON V_TABLE300 TO USER300;
Grant succeeded

After some search, noticed List option; But it only records only the last statement that was executed if we have more than 1 statement, which doesn't fit here. In Teradata Bteq the ECHOREQuired attribute can be set to ON for this task. But I am not sure in Oracle. Also tried sqlplus -s username/password@servername <<EOF > ${drctry}/${v_timestamp}_unix_sql_log.txt But still no luck. Will change the password hardcode once I overcome this issue;

Just curious is this part of a refresh process or something? — mmmmmpie
– mmmmmpie, Commented Aug 23, 2016 at 17:11

Alex Poole · Accepted Answer · 2016-08-23 17:49:05Z

1

The -s[ilent] option:

Suppresses all SQL*Plus information and prompt messages, including the command prompt, the echoing of commands, and the banner normally displayed when you start SQL*Plus.

So by including the -s flag you are overriding the set echo on directive.

If you omit that then the commands are echoed in the spool file, but (a) you also see the banner on stdout and (b) you see the SQL> prompt and the script name spool off echoed too. You can fix the first part by redirecting output, with the risk you may miss something you care about if there is a problem, by changing your shell command to do:

sqlplus -s username/password@servername >/dev/null <<EOF

And you can partly fix the spooled output by changing the prompt, adding:

SET SQLPROMPT ""

With those changes the shell sees no output, and the spool file contains:

@/path/to/v_date_sql_statements.txt
CREATE OR REPLACE VIEW V_TABLE1 AS SELECT * FROM TABLE1;

View created.

GRANT SELECT ON V_TABLE1 TO USER1;

Grant succeeded.

...

SPOOL OFF;

You could potentially post-process that to remove the first and last line, and (if you want) blank lines. You can also hide the script name by including the text file in the heredoc instead of using start/@:

SPOOL ${drctry}/${v_timestamp}_sql_execution_log.txt
`cat ${drctry}/${v_date}_sql_statements.txt`
SPOOL OFF;

answered Aug 23, 2016 at 17:49

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chill3chee Over a year ago

Perfect @Alex Poole; Thank you for sqlprompt option too. There were line numbers being displayed and got rid of them using sqlnumber attribute in case if any other user does need it.

Alex Poole Over a year ago

Right, I didn't try any multi-line statements. Good addition.

Javier Bordonada · Accepted Answer · 2022-01-24 23:43:01Z

0

I made a sqplus bash script to do that. link to github: https://github.com/javierB-/ORACLE.git It create two files, one with the sql sentences, and the other with the sql sentences and their output.

The script core is:

tee -a $FOUT $FOUT_TRAZA | sqlplus $@ |  tee -a $FOUT

I use the script throught the alias:

alias sqlpluss='$ADMIN_HOME/oracle/sqlpluss.sh'

it's in spanish, i have traslated the output to write here:

 sqlpluss
  USE ONLY INTERACTIVELY:
  sqlplus enriched to save input and output trace.
 Use:
  sqlpluss [-f fout] user/password@conection @scripts ...
     (fout will be opened in add mode and it will default to yyyymmdd.log)
      
summary:
  tee -a fout | sqlplus $@ |  tee -a fout

the full script its too long to paste here, but if someone want, i'll do it.

edited Jan 24, 2022 at 23:43

answered Dec 17, 2020 at 12:57

Javier Bordonada

92 bronze badges

2 Comments

Tomer Shetah Over a year ago

Maybe add it to a gist in github and share a link?

Javier Bordonada Over a year ago

here it is! only one year later!! github.com/javierB-/ORACLE.git

Collectives™ on Stack Overflow

Unix to Sqlplus - Log the SQL statement about to execute and result of execution

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related