4

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?

Samuel Owens
  • 41
  • 1
  • Does this answer your question? [sqlplus statement from command line](https://stackoverflow.com/questions/1639704/sqlplus-statement-from-command-line) – OldProgrammer Mar 26 '21 at 22:19
  • If it's the same query every time, why would you _not_ want to keep the query in .sql file? And if it is a different query each time, what's wrong with simply starting sqlplus then entering the query at sql prompt? Either way, what do you think you are accomplishing by putting the query on the same command line that is used to launch sqlplus? – EdStevens Mar 27 '21 at 14:40

4 Answers4

1

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.

Stéphane Millien
  • 3,238
  • 22
  • 36
0

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@******** ~]$
0

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
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

Here's the Windows version:

echo select 1 from dual; | sqlplus -s username/password@service

You can use this to implement e.g. a Docker Healthcheck in a Powershell script:

$chekdbsql = "`nselect 1 from dual;"
$chkdb = ""
$chkdb = ($chekdbsql | cmd /c "sqlplus username/password@service")
if ($chkdb.Contains("OPEN") -eq 'True'){
    exit 0
}
else {
    exit 1
}
TrojanName
  • 4,853
  • 5
  • 29
  • 41