'Echo sqlplus command without using prompts or scripts

I have a script which I want to maintain as a single file, however I wish to echo the commands input into sqlplus without using either PROMPT <sql> or @script.sql can this be done?

Current script:

$ cat test.sh
#!/bin/bash
LOG=/home/oracle/output.log
sqlplus hr/hr <<EOF > $LOG
set echo on
select 1 from dual;
QUIT
EOF`

Current output:

$ cat output.log
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 1 15:01:12 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SQL>
         1
----------
         1

What I want:

$ cat output.log

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 1 15:02:02 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SQL> SQL> select 1 from dual;

         1
----------
         1
oraclebashoracle11gsqlplus


Solution 1:[1]

When SQL*Plus is reading commands from your TTY, the echoing of input is actually handled by your TTY, not SQL*Plus. If SQL*Plus handled the echoing, any time you manually typed a command you would see the command twice (once as you typed it, once as it was echo'd back).

Additionally, the TERMOUT option only applies when running a script file, not reading from STDIN.

The easy fix is telling SQL*Plus that /dev/stdin is a script:

sqlplus scott/tiger @/dev/stdin <<EOF
SET TERMOUT ON ECHO ON
SELECT SYSDATE FROM dual;
EOF

Solution 2:[2]

To expand on wonderful @Mr.llama answer. His construct works perfect on Solaris.

However on Linux (Oracle Linux Server release 7.9), this causes every command to run twice in sqlplus, even though it echoes the command only one time. See below:

$ sqlplus -l / as sysdba @/dev/stdin <<-EOF
>     set echo on termout on
>     select systimestamp from dual;
> EOF


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 23 14:33:03 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL>     select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-FEB-22 02.33.03.975542 PM -08:00

SQL> SQL>
SYSTIMESTAMP
---------------------------------------------------------------------------
23-FEB-22 02.33.03.976106 PM -08:00

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

Notice how there are two systimestamp lines, with different timestamps, which means that the select was executed twice.

This double execution does not happen when same block is run on Solaris.

A Linux workaround is to modify construct to:

$ cat /dev/stdin <<-EOF | sqlplus -l / as sysdba @/dev/stdin
>     set echo on termout on
>     select systimestamp from dual;
> EOF

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 23 14:38:08 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL>     select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-FEB-22 02.38.08.931790 PM -08:00

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mr. Llama
Solution 2 Al.B

Related Questions