58

Is it possible to do something like this?

$ sqlplus -s user/pass "select 1 from dual" or
$ echo "select 1 from dual" | sqlplus -s user/pass

I know I can put select 1 from dual in a file and do this:
$ sqlplus -s user/pass @myFile.sql

but I'm wondering if it's actually necessary to create a file just to satisfy sqlplus

andersonbd1
  • 5,266
  • 14
  • 44
  • 62
  • 1
    Possible duplicate - http://stackoverflow.com/questions/638705/how-can-i-issue-a-single-command-from-the-command-line-through-sql-plus – vapcguy Jun 07 '16 at 21:30

5 Answers5

57

Just be aware that on Unix/Linux your username/password can be seen by anyone that can run "ps -ef" command if you place it directly on the command line . Could be a big security issue (or turn into a big security issue).

I usually recommend creating a file or using here document so you can protect the username/password from being viewed with "ps -ef" command in Unix/Linux. If the username/password is contained in a script file or sql file you can protect using appropriate user/group read permissions. Then you can keep the user/pass inside the file like this in a shell script: 

sqlplus -s /nolog <<EOF
connect user/pass
select blah;
quit
EOF
David Mann
  • 1,960
  • 15
  • 15
  • not always true, depending on Oracle version and OS, but probably a good standard to follow – dpbradley Oct 29 '09 at 13:02
  • too bad heredoc's aren't easy in DOS http://stackoverflow.com/questions/1015163/heredoc-for-windows-batch – rogerdpack Feb 18 '15 at 20:54
  • What wonders me is why commandline leaking is not simply tackeled in the sql client program itself, as that would be quite trivial to do: in the main() function, copy the commandline args to a new variable, and overwrite the old args/argv[], and no commandline is visible (or - just for a fraction of a millisecond perhaps) - why doesn't oracle implement that solution? – Rob Heusdens Mar 04 '17 at 14:19
  • @RobHeusdens because it's not Oracle's problem? Why should Oracle assume that users _shouldn't_ see what they expect to see? – osullic Nov 11 '19 at 18:04
51

I'm able to execute your exact query by just making sure there is a semicolon at the end of my select statement. (Output is actual, connection params removed.)

echo "select 1 from dual;" | sqlplus -s username/password@host:1521/service

Output:

         1
----------
         1

Note that is should matter but this is running on Mac OS X Snow Leopard and Oracle 11g.

zb226
  • 9,586
  • 6
  • 49
  • 79
Grant Lammi
  • 1,324
  • 11
  • 5
  • +1, but be aware that if you're doing anything moderately complicated in SQL*Plus you'll usually be making a few SET directives and this will get messy if you're looking to have a one-line command. – dpbradley Oct 28 '09 at 20:22
  • 4
    +1 Finally, the answer! Adding the semi-colon to the end of the query! You actually can run sqlplus, log in, and then run the commands - you don't have to pipe in the credentials. My problem was the commands weren't running - the line numbers just kept incrementing! Now I know how to get them to run -- just add a semi-colon! Thanks! – vapcguy Jun 07 '16 at 21:29
  • +1 Nice! I used this for a `desc table_name;` command and was getting unknown command errors, but removed the quotes and it worked. `echo desc table_name; | sqlplus uid/pwd@host` – S3DEV Dec 19 '17 at 14:50
  • 2
    The quotes produce an Oracle "SP2-0734: unknown command beginning ""select 1 ..." error for me on Windows. Removing them works for me. – galaxis May 24 '18 at 13:53
  • works for docker as well: `docker exec -it CONTAINER_ID bash -c "echo 'select table_name from user_tables;' | sqlplus username/password@//localhost:1521/ORCLCDB";`. Lesson is dont forget `;` in statement to execute – prayagupa Feb 25 '19 at 07:05
  • Works like a charm in debian and linux distro. Thanks for sharing. Quick test example. – Doogle May 15 '19 at 09:50
31

My version

$ sqlplus -s username/password@host:port/service <<< "select 1 from dual;"


         1
----------
         1

EDIT:

For multiline you can use this

$ echo -e "select 1 from dual; \n select 2 from dual;" | sqlplus -s username/password@host:port/service


         1
----------
         1


         2
----------
         2
rofrol
  • 14,438
  • 7
  • 79
  • 77
9

I assume this is *nix?

Use "here document":

sqlplus -s user/pass <<+EOF
select 1 from dual;
+EOF

EDIT: I should have tried your second example. It works, too (even in Windows, sans ticks):

$ echo 'select 1 from dual;'|sqlplus -s user/pw

         1
----------
         1


$
DCookie
  • 42,630
  • 11
  • 83
  • 92
3

For windows version you can try below. remove double quotes (")

echo select 1 from dual; | sqlplus -s username/password@service
Vinodraj Jayaraman
  • 31
  • 2