Sqlplus oracle : How can I run sql command on bash in 1 line?

Question

Can I convert this into 1 command line on bash in sqlplus? cause i want to automate it.

sqlplus / as sysdba
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
exit

https://stackoverflow.com/questions/9462416/shell-one-line-query/31545856#31545856?newreg=27ff13f929b544a5b5cd908766f30769 try the upvoted method — Vishnu Krishnan, Jun 27 '22 at 11:43

score 16 · Accepted Answer · answered Jun 28 '12 at 04:50

16

You won't need the exit with automation because it should exit on end of file anyway. So on one line you could do:

echo 'EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);' | sqlplus / as sysdba

answered Jun 28 '12 at 04:50

Sodved

Thanks. this would be the best answer as i don't want to create additional .sh or .sql at all – TheOneTeam Jun 28 '12 at 06:36
I try this command with select query but nothing happened. It just login and then disconnect. What should I do? – Mahfud Harun Aug 16 '13 at 01:41
Sounds like you're missing a `;` or `/` (on a line of its own) to actually run the command. Just a guess, you'll need to provide more details for a better answer – Sodved Aug 27 '13 at 05:55

score 6 · Answer 2 · answered Mar 22 '13 at 15:03

6

sqlplus user/password@host @file.sql

answered Mar 22 '13 at 15:03

Neerav

score 3 · Answer 3 · answered Jun 28 '12 at 04:04

3

you can wirite by follow in a shell

#!/bin/bash
sqlplus / as sysdba <<EOF
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
exit
EOF

or you can put this commond into a procedure

answered Jun 28 '12 at 04:04

Jie Hou

score 0 · Answer 4 · answered Jun 28 '12 at 05:31

0

sqlplus /nolog @your_script.sql

answered Jun 28 '12 at 05:31

Iľja

That won't work, since /nolog prevents SQL*Plus from logging on to the database. – Frank Schmitt Jun 28 '12 at 06:35
Of course it won't - without putting the `connect` command into the script. – Iľja Jun 28 '12 at 06:45

4 Answers4