Echo sqlplus command without using prompts or scripts

Question

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

You particularly want the banners too - so spool isn't an option? And creating a temporary script isn't allowed? — Alex Poole
– Alex Poole, Commented Mar 1, 2016 at 15:27
@AlexPoole Mr.Llama's answer is the best response so far, but is there a way to supress the banners? MaxU's might be the better answer for my requirements for this reason — bob dylan
– bob dylan, Commented Mar 1, 2016 at 15:48
You can suppress the banners with -s, but that kills the command echo, even with Mr.Llama's approach (which was new to me). Spool won't include them anyway, and you can redirect screen output to /dev/null to hide them. There's going to be some trade-off. — Alex Poole
– Alex Poole, Commented Mar 1, 2016 at 15:53
Yea. For my purposes I've decided to just go for: $ cat test3.sh LOG=/home/oracle/output.log sqlplus hr/hr <<EOF spool $LOG append select 1 from dual; QUIT EOF Since it satisfies my needs best — bob dylan
– bob dylan, Commented Mar 1, 2016 at 15:56

Mr. Llama · Accepted Answer · 2016-03-01 15:29:40Z

4

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

answered Mar 1, 2016 at 15:29

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

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

Comments

MaxU - stand with Ukraine · Accepted Answer · 2016-03-01 15:27:50Z

1

try spool instead of redirecting your STDOUT:

#!/bin/bash
LOG=/home/oracle/output.log
sqlplus hr/hr <<EOF
set echo on term on 
spool $LOG
select 1 from dual;
QUIT
EOF

answered Mar 1, 2016 at 15:27

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

1 Comment

bob dylan Over a year ago

I have chosen this as the correct answer since it best suits my needs, hower @Mr.llama has a good answer which will suit others needs also.

Al.B · Accepted Answer · 2022-02-23 22:40:56Z

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

Pau · Accepted Answer · 2025-01-29 20:57:15Z

0

If you are using PowerShell you can use a pipe with the query you would like to run

 echo 'select 1 from dual' | sql -S user/password@localhost:1521/DB

 1
----
 1

answered Jan 29 at 20:57

Pau

16.2k16 gold badges74 silver badges97 bronze badges

Collectives™ on Stack Overflow

Echo sqlplus command without using prompts or scripts

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related