CODEWITHSUNDEEP
oracle-databasesqlplus
Samuel Owens
Samuel Owens

Reputation: 41

Is it possible to pass a single query to SQLplus?

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?

Upvotes: 4

Views: 1100

Answers (4)

TrojanName
TrojanName

Reputation: 5355

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
}

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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

Upvotes: 0

St&#233;phane Millien
St&#233;phane Millien

Reputation: 3448

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.

Upvotes: 1

user5683823
user5683823

Reputation:

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

Upvotes: 0

Related Questions