I need to execute commands from my code written in PLSQL.
In Oracle Forms 10g, I use HOST procedure
I can execute this by three ways
1- Using Java Class
Develop my own Java class to execute command, then create wrapped procedure for it in PLSQL
2- Using DBMS_SCHEDULER package
This package is available in oracle from version 10g.
To use this package you should run service OracleJobScheduler[SID] for example if my service name is ORCL the service will be OracleJobSchedulerORCL
3- Using DBMS_PIPE package
1- Using Java Class
a- Create Java Source in PLSQL CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ClientHost"
AS public class ClientHost {
public static void executeCommand(String command) {
try {
Runtime.getRuntime().exec(command);
} catch (Exception exception) {
exception.printStackTrace();
}
}
public static void main(String[] args) {
ClientHost.executeCommand("c:\\windows\\system32\\cmd /c c:\\windows\\system32\\notepad.exe");
}
}
/
b-Create wrapper procedure in PLSQL
CREATE OR REPLACE PROCEDURE executeCommand (in_command IN VARCHAR2)
AS
LANGUAGE JAVA
NAME 'ClientHost.executeCommand (java.lang.String) ';
/
c- Run wrapper procedure from PLSQL
User should has some java permission to execute command which illustrated in script
BEGIN
DBMS_JAVA.grant_permission ('SCOTT',
'SYS:java.lang.RuntimePermission',
'writeFileDescriptor',
'*');
DBMS_JAVA.grant_permission ('SCOTT',
'SYS:java.lang.RuntimePermission',
'readFileDescriptor',
'*');
DBMS_JAVA.grant_permission ('SCOTT',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute');
END;
BEGIN
executeCommand ('c:\windows\system32\cmd /c c:\windows\system32\notepad.exe');
END;
2- Using DBMS_SCHEDULAR
a- I use DBMS_SCHEDULER.CREATE_JOB to create new job in scheduler BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'myjob',
job_type => 'EXECUTABLE',
job_action => 'c:\windows\system32\notepad',
enabled => true);
END;
b- You can use DBMS_SCHEDULER.RUN_JOB to run created job named "myjob"
EXEC DBMS_SCHEDULER.RUN_JOB ('myjob');
c- You can use DBMS_SCHEDULER.DROP_JOB to drop created job named "myjob"
exec DBMS_SCHEDULER.DROP_JOB('myjob');
d- You can track your jobs from DBA_SCHEDULER_JOBS view
SELECT * FROM DBA_SCHEDULER_JOBS;
e-You can DBMS_SCHEDULER.DISABLE to disable job named "myjob"
exec DBMS_SCHEDULER.DISABLE('myjob');
f- You can use DBMS_SCHEDULER.ENABLE to enable job named "myjob"
exec DBMS_SCHEDULER.ENABLE('myjob');
3- Using DBMS_PIPE
I will use DBMS_PIPE.SEND_MESSAGE to execute command DECLARE
LC$COMMAND VARCHAR2 (1024);
LN$STATUS NUMBER := 0;
BEGIN
LC$COMMAND := 'c:\windows\system32\notepad';
DBMS_PIPE.PACK_MESSAGE (LC$COMMAND);
LN$STATUS := DBMS_PIPE.SEND_MESSAGE ('my_pipe');
END;
Thanks
Mahmoud Ahmed El-Sayed
great thanks
ReplyDeleteThanks For The Wonder Full Information...
ReplyDeleteSTC Technologies
Yes, you can as it is executed in plsql engine not from oracle forms
ReplyDelete