How can I issue commands through SQL*Plus on Windows?
In Linux is like this:
sqlplus -s /nolog<<EOF
conn username/password@database
EXECUTE some_proc;
EXIT;
EOF
And Windows?
-
That's not "interactive mode"; you're sending your commands to the SQL*Plus standard input. Please clarify your question. – mustaccio Aug 25 '19 at 12:52
-
I learnt that << send the commands in sequence to some command in Linux and it is called "interactive mode". Maybe I'm wrong, but what I want to say is to send commands to sql*plus on Windows in sequence or something such as mysql -e in mysql – DBA Jr Aug 25 '19 at 12:58
Add a comment
|
That is a matter of Windows/batch, it is not specific to SQL*Plus.
You can do this:
(echo connect bp/bp@192.168.1.71/min19_o71
echo select * from dual;
echo exit
) | sqlplus -S /nolog
Result:
C:\Users\balaz>(echo connect bp/bp@192.168.1.71/min19_o71
More? echo select * from dual;
More? echo exit
More? ) | sqlplus -S /nolog
D
-
X
C:\Users\balaz>
Or just use a script, that method is specific to SQL*Plus:
C:\Users\balaz>type script.sql
connect bp/bp@192.168.1.71/min19_o71
select * from dual;
exit
C:\Users\balaz>sqlplus -S /nolog @script.sql
D
-
X
C:\Users\balaz>
answered Aug 25 '19 at 13:07
-
Thank you very much, I got using the first option, but only worked with one echo and the commands within the echo. (echo "connect sys/manager as sysdba select * from dual;" ) | sqlplus /nolog each command need to be in one different line! – DBA Jr Aug 25 '19 at 13:41
I'm not sure where you get the term "interactive" in this context, but what you describe is known in the *nix world as "input redirection". It is actually the opposite of 'interactive', where you would simply start sqlplus and 'interact' with it via the keyboard. Unfortunately, Windows simply doesn't work that way - yet another shortcoming of Windows and another reason I avoid it every chance I get. The way I write my windows command scripts (to avoid having to maintain a separate sql script, as @Balzas Papp describes) is as follows:
echo connect scott/tiger@orcl > doit.sql
echo select * from emp >> doit.sql
echo exit >> doit.sql
sqlplus /nolog @doit.sql
Of course, there is nothing about this that requires sqlplus to be started '/nolog', with the connect command embedded in the script. You could just as easily
echo select * from emp > doit.sql
echo exit >> doit.sql
sqlplus scott/tiger@orcl @doit.sql
Note that my first 'echo' command uses a single '>' to redirect the output to 'doit.sql'. This will cause any existing copy to 'doit.sql' to be overwritten, while the following lines use a double '>>' which indicates to append the redirected line rather than overwriting. One could also choose to delete 'doit.sql' before starting to recreate it, then simply use '>>' for ALL of the redirection lines. This could be seen as a better way because if you needed to edit the script at a later date you wouldn't risk mixing up where the single and double redirection indicators go.
Also note that Balzas demonstrates using EZConnect syntax while I demonstrate using traditional tns net service name, depending on tnsnames.ora. Either is fine and has nothing specific about the technique either of us demonstrates.
