UNIX Sqlplus - Capture the sql statement about to run and execution status

Shell Programming and Scripting
1

Greetings Experts,

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 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. Thank you for your time

2 
{
sqlplus -s username/password@servername <<EOF
...
EOF
} > ${drctry}/${v_timestamp}_unix_sql_log.txt
1 Like
3

Hi Vgersh99,

Thank you for your time. But the file ${drctry}/${v_timestamp}_sql_primary_log.txt content is empty. Did I miss something here please.. However, I did see the contents in another (spool) file which is partial logging only the execution status of the statement but not the actual sql statement. ${drctry}/${v_timestamp}_sql_execution_log.txt

{
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
} >> ${drctry}/${v_timestamp}_sql_primary_log.txt
4

Try running without silent mode:-

sqlplus -s username/password@servername <<EOF

to

sqlplus username/password@servername <<EOF
1 Like
5

Thank you Yoda, that did it :slight_smile: