Is it possible to pass a single query to SQLplus?

user_15489747 0
Viewed 211

I regularly need to run a single query on some table in one of my DBs and I would rather not have to create a file and call it as a script and everything if I don't have to.

I would like to be able to send the query directly to sqlplus as a string like this perhaps

sqlplus user/pass@hostname:port/service "select * from table"

instead of having to create a file that holds only that one query and call it with

sqlplus user/pass@hostname:port/service @filename

does anyone know if this is possible?

sqlplus oracle
3 Answers

In addition to mathguy answer, with linux shells, you can use the operator here-string like this:

sqlplus user/pass@hostname:port/service <<< 'select * from table;'

Don't forget the semi-colon at the end of your query.

user_36816070

I believe the sqlplus command doesn't accept a SQL statement (as a string) as a command-line parameter. You can run sqlplus either interactively or in batch (giving a .sql file as input).

However, you can do something like what you are asking, using operating system facilities. I don't know how this might work in Windows, but in Unix/Linux you can use a here document. Something like this: (I masked my password and my machine name for privacy, but otherwise it's a screenshot showing the command and its output)

[oracle@******** ~]$ sqlplus mathguy/********@orclpdb <<EOF
> select empno, ename, sal
> from   scott.emp
> where  deptno = 30;
> EOF

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 26 15:15:30 2021

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

Last Successful login time: Fri Mar 26 2021 15:14:40 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>   2    3  
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            1600
      7521 WARD             1250
      7654 MARTIN           1250
      7698 BLAKE            2850
      7844 TURNER           1500
      7900 JAMES             950

6 rows selected.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
   - 64bit Production
[oracle@******** ~]$
Admin1

Never ever enter your credentials next to sqlplus command but use /nolog option through a variable assignment in order to unveil them whenever ps command issued by the others such as

rec=$(sqlplus -S /nolog << EOF
conn un/pwd@mydb
set pages 1001
set linesize 500
set feedback off
SELECT * FROM tab;
exit
EOF
)
echo $rec
user_58413060
Related