sqlplus command/DB version: 11.2.0.4
Platform : Oracle Linux
My question:
I ran the below shell script to execute two SQLs. But, the commands themselves are not captured in spool file because SET ECHO ON will only work for script mentioned with @. As shown below, the log only shows "Database altered." , "1 row created." but not the commands.
This is discussed below by Frank in the below post
https://community.oracle.com/thread/2616547
Rather than calling another sql script using @, I want the SQL commands to be directly placed in the below shell script (like below) and get commands printed in the log file too. Is there any workaround for this ?
#!/bin/bash
echo -e "\n"
export ORACLE_SID=CSPOS
echo "Datafile Add and Resize started at " `date`
sqlplus -s ' / as sysdba' << EOF
set time on timing on
set echo on
set feedback on
spool someCommands.log
--TEST
alter database datafile '/db/oradata/CSPOS/datafile/ctms_data01.dbf' resize 1g;
insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (8005, 'Keith', 'ANALYST', 7902, SYSDATE, 5600, 300, 10);
commit;
quit
EOF
Output of the spool file:
Datafile Add and Resize started at Tue Mar 14 18:11:40 MYT 2017
Database altered.
Elapsed: 00:00:00.36
1 row created.
Elapsed: 00:00:00.00
Commit complete.
Elapsed: 00:00:00.01