You can execute concurrently at OS level. Unfortunately this won't run on NT, which I think is your case, since DOS won't take parallel execution as simple as UNIX.
It is important to notice that it is a different alternative to Tom's suggestion. His is simpler for what the reader wants.
But this code is more powerful in the sense you can run things in the OS environment.
Use this code tip to publish the "shellcmd" PL/SQL function:
</code>
https://asktom.oracle.com/magazine-archive.htm
Or take a look at Oracle Magazine:
http://asktom.oracle.com/magazine-archive.htm/oracle/00-Mar/index.html?o20java.html <code>
Then you have something like:
CREATE OR REPLACE PROCEDURE club_proc
IS
r1 NUMBER;
r2 NUMBER;
r3 NUMBER;
BEGIN
r1=shellcmd(<os_cmd1>);
r2=shellcmd(<os_cmd2>);
r3=shellcmd(<os_cmd3>);
END;
Where each <os_cmd> command will execute serially.
If you want to run OS commands "concurrently", use the UNIX ampersand (&) suntax.
So, if you want to run your procedures, run SQLPLUS starting them, just like:
CREATE OR REPLACE PROCEDURE club_proc
IS
r1 NUMBER;
r2 NUMBER;
r3 NUMBER;
BEGIN
r1=shellcmd("$ORACLE_HOME/bin/sqlplus scott/tiger@host1 @p1.sql &");
r2=shellcmd("$ORACLE_HOME/bin/sqlplus scott/tiger@host2 @p2.sql &");
r3=shellcmd("$ORACLE_HOME/bin/sqlplus scott/tiger@host3 @p3.sql &");
END;
Notice you can run whichever OS command you like, including starting SQLPLUS sessions in other machines, like in host1, host2 and host3.
Just take care:
- Beware of Java privileges in Oracle -- they changed from 8.1.5 to 8.1.6, differently from the second article I mentioned (8.1.5), you should use "direct Java privileges" (look it up).
- Your username and password would be visible, so you could use the wrap utility to "hide" your code. Run:
$ wrap iname=p1.sql
Even so, some of your data is visible in the wrapped code.
Hope this helps !