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
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 community
That is a matter of Windows/batch, it is not specific to SQL*Plus.
You can do this:
(echo connect firstname.lastname@example.org/min19_o71 echo select * from dual; echo exit ) | sqlplus -S /nolog
C:\Users\balaz>(echo connect email@example.com/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 firstname.lastname@example.org/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.