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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityHow 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 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>
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.