How can I issue a single command from the command line through sql plus?

Question

Using SQL Plus, you can run a script with the "@" operator from the command line, as in:

c:\>sqlplus username/password@databasename @"c:\my_script.sql"

But is it possible to just run a single command with a similar syntax, without a whole separate script file? As in:

c:\>sqlplus username/password@databasename @execute some_procedure

I am interested in this because I want to write a batch file that simply executes a command, without generating a bunch of two-line ".sql" files.

Solution 1

I'm able to run an SQL query by piping it to SQL*Plus:

@echo select count(*) from table; | sqlplus username/password@database

Give

@echo execute some_procedure | sqlplus username/password@databasename

a try.

Solution 2

Have you tried something like this?

sqlplus username/password@database < "EXECUTE some_proc /"

Seems like in UNIX you can do:

sqlplus username/password@database <<EOF
EXECUTE some_proc;
EXIT;
EOF

But I'm not sure what the windows equivalent of that would be.

Solution 3

For UNIX (AIX):

export ORACLE_HOME=/oracleClient/app/oracle/product/version
export DBUSER=fooUser
export DBPASSWD=fooPW
export DBNAME=fooSchema 

echo "select * from someTable;" | $ORACLE_HOME/bin/sqlplus $DBUSER/$DBPASSWD@$DBNAME

Solution 4

sqlplus user/password@sid < sqlfile.sql

This will also work from the DOS command line. In this case the file sqlfile.sql contains the SQL you wish to execute.

View more solutions

Comments

[JosephStyons]

The < symbol is a pipe in Windows too. Nice idea, but it does not work.

[spencer7593]

that command line will be visible to other os users with (e.g.) ps -ef, if you have to include username and password, better to execute "sqlplus /nolog <<EOF" and have the first line be "connect username/password". Not the ideal solution, but it's better in that at least it doesn't expose the clear text username and password in output from ps -ef

[ivanatpr]

What does the @ symbol before echo do in this case?

[Patrick Cuff]

@IvanG; For Windows batch files, the @ symbol before a command surpress the command from being echoed to the console. It's useful in situations where you don't want to turn echo off, but don't want to see the command echoed on the screen. In this case it's needed so that what gets piped to the sqlplus command is the SQL we want to run, not the command text.

[Ravimallya]

Describe your answer more specifically where to set this and how it will fix the error etc.

[maxshuty]

Please add more details do your answer.

[durette]

This solution in Apache Ant still needs a SQL script scripttoexecute.sql stored separately from the calling script.

[msm1089]

OP already said he knows he can do this. He is asking for a way to run a single command without needing a separate SQL script.