07 April, 2012

Execute Operating System Commands from PSLQL


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

3 comments:

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...