execute PL/SQL command from command line.

[babsjoy]

I have a SQL*PLUS command in a .bat file and want to know if a .sql script file is the only way to execute a PL/SQL procedure. For example. Could the below work ?:

sqlplus username/password@databasename execute instancename.procedurename;

WHAT WORKS:

The procedure executes if the execution of the procedure is in a .sql file and the command line in the .bat looks like the following:
sqlplus username/password@databasename @scriptfile.sql

The procedure executes when logging into SQL*PLUS manually entering username and password. Then at the PL/SQL prompt entering:
execute procedurename;

So, why won't the first work ? Since the script would only have one statement it would be easier to maintain and track if the procedure could be executed from the command line.

Eventually the .bat file (which contains the pl/sql command line) will grow and contain several other commands and possibly other .bat executions. It would be easier for maintenance reasons that all commands be in one .bat file. When problems arise only one file would be the source requirig research. I know its not a perfect world but if it can be done then that would be ideal.

Thanks in advance

 

[sem]

Because it works so!
Check command line parameters for sql*plus:

Usage: SQLPLUS [<option>] [<user>[/<password>] [@<host>]]
[@<startfile> [<parm1>] [<parm2>] ...]
where <option> ::= { -s | -? }
-s for silent mode and -? to obtain version number

Why do you want to start sql*plus for each procedure? Isn't it better to create a script and use HOST to call programs instead of calling multiple sql commands from bat file? Note that each connection to Oracle takes a lot of resources on server side. It's not so obvious when you're the only who does it, but when everybody chooses the same strategy your server would probably be overstressed.

Regards, Dima

http://seminihin.narod.ru