55

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.

JosephStyons
  • 57,317
  • 63
  • 160
  • 234

6 Answers6

76

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.

Patrick Cuff
  • 28,540
  • 12
  • 67
  • 94
  • 2
    What does the @ symbol before echo do in this case? – ivanatpr Jan 23 '13 at 21:50
  • 7
    @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. – Patrick Cuff Jan 24 '13 at 11:21
18

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • Note, on unix ( and probably windows ), you really shouldn't ever the username/password syntax, that will expose your password to every user on the system. Use external auth if you need to. – Matthew Watson Mar 12 '09 at 14:44
  • 1
    The < symbol is a pipe in Windows too. Nice idea, but it does not work. – JosephStyons Mar 12 '09 at 15:58
  • 3
    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 < – spencer7593 Jun 01 '09 at 01:16
10

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
javaPlease42
  • 4,699
  • 7
  • 36
  • 65
6
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.

zb226
  • 9,586
  • 6
  • 49
  • 79
tale852150
  • 1,618
  • 3
  • 17
  • 23
  • 1
    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. – msm1089 Apr 23 '20 at 07:10
-1
@find /v "@" < %0 | sqlplus -s scott/tiger@orcl & goto :eof

select sysdate from dual;
xxoid
  • 11
  • 2
-10

This is how I solved the problem:

<target name="executeSQLScript">
    <exec executable="sqlplus" failonerror="true" errorproperty="exit.status">
        <arg value="${dbUser}/${dbPass}@<DBHOST>:<DBPORT>/<SID>"/>
        <arg value="@${basedir}/db/scripttoexecute.sql"/>
    </exec>
</target>
Brock Adams
  • 90,639
  • 22
  • 233
  • 295
jkob
  • 1
  • 4
    Describe your answer more specifically where to set this and how it will fix the error etc. – Ravimallya Jun 16 '15 at 09:41
  • This solution in Apache Ant still needs a SQL script `scripttoexecute.sql` stored separately from the calling script. – durette Jul 24 '19 at 19:16