There was a request for repair to output the log of the table used by the application developed in-house and its related table. Refer to the log and have another external application output the information information. The timing to output the log was when the application registered the data in the table. In that case, I felt that it would be possible to execute a batch using a database trigger without modifying the application, so I decided to investigate. I don't want to modify the application as much as possible.
There are two ways to call a Windows batch.
If you just want to output the log file, you can use the UTL_FILE package or the java.io package on Java stored, but considering the versatility of saving to a network drive destination or something, it is better to process it at the destination that called the Windows batch. Something is convenient. Reference: Consider how to output logs from PL / SQL
Oracle implements the Java VM, which allows Java programs to run on the database. I've only used PL / SQL before, so it's a good opportunity to learn Java stored.
I referred to the site of "Java used in PL / SQL".
Once you've created a Java stored, you'll also need to create a wrapper to call it. The wrapper only needs to be created once if there is no change in the argument or return value, and the result will be reflected even if the contents of the Java stored are rewritten later.
CREATE OR REPLACE JAVA SOURCE NAMED java_test_src
AS
public class Test {
public static int kasan(int a,int b){
return a+b;
}
}
/
CREATE OR REPLACE FUNCTION kasanf(a in number,b in number)
RETURN NUMBER
IS LANGUAGE java
NAME 'Test.kasan(int,int) return int'
;
/
It can be used in the same way as calling a PL / SQL user function. Interesting.
SELECT kasanf(2,3) FROM DUAL;
KASANF(2,3)
-----------
5
Execute the command from Java Use the Runtime.getRuntime (). exec method and pass the argument to execute the Windows batch (test.bat).
CREATE OR REPLACE JAVA SOURCE NAMED PLSQLExecByJava
AS
public class PLSQLExecByJava {
public static String execByJava(
String key
) {
try {
String cmd = "cmd /c start C:\\foo\\test.bat " + key;
Runtime.getRuntime().exec(cmd);
return "Success";
} catch(Exception e) {
return e.getMessage();
}
}
}
/
CREATE OR REPLACE FUNCTION FuncExec(key in VARCHAR2)
RETURN VARCHAR2
IS LANGUAGE java
NAME 'PLSQLExecByJava.execByJava(java.lang.String) return java.lang.String'
;
/
When executing commands or inputting / outputting files within a Java stored procedure, it is necessary to grant appropriate privileges to the executing user.
Granting execute permission is done by the GRANT_PERMISSION procedure in the DBMS_JAVA package. Execute as a privileged user (SYS, DBA authority).
CALL dbms_java.grant_permission( '<Privilege destination schema>', 'SYS:java.io.FilePermission', '<<ALL FILES>>', 'execute' );
In the above case, all files are targeted, so if there is a security problem, the range is limited to cmd.exe.
call dbms_java.grant_permission('<Authorization destination schema>', 'SYS:java.io.FilePermission','C:\WINDOWS\system32\cmd.exe','execute');
How to check permissions
select * from DBA_JAVA_POLICY;
How to remove permissions
begin
DBMS_JAVA.disable_permission(<SEQ>);
DBMS_JAVA.delete_permission(<SEQ>);
end;
Execute the command with the KeyName column of the FUGA table as an argument. The batch (test.bat) is called within FuncExec.
TRG_LOGOUTPUT
CREATE OR REPLACE TRIGGER "TEST"."TRG_LOGOUTPUT"
AFTER INSERT
ON FUGA
FOR EACH ROW
DECLARE
sResult VARCHAR2(200);
BEGIN
IF INSERTING THEN
SELECT FuncExec(:new.KeyName) INTO sResult FROM DUAL;
END IF;
END;
The batch (test.bat) is called from the trigger with the key as an argument. Receive this key and output the log. However, even if you SELECT at this point, the target data cannot be obtained because it has not been committed yet. Therefore, prepare another batch (test2.bat) and place one cushion to output the log.
test.bat
cd /d %~dp0
start test2 %1
test2.bat
cd /d %~dp0
outputlog.exe %1
exit
By this method, you will be in a committed state and you will be able to retrieve the target data. Also, if it is rolled back, no log will be output because the target data cannot be found.
Now that I can do it with Java stored, I also tried the DBMS_SCHEDULER method.
Grants permission to run DBMS_SCHEDULER. Execute as a privileged user (SYS, DBA authority).
SQL>GRANT CREATE JOB TO <user name>;
The authorization was successful.
SQL>GRANT CREATE REATE EXTERNAL JOB TO <user name>;
The authorization was successful.
Execute the command with the KeyName column of the FUGA table as an argument. At that time, specify "autonomous_transaction" of the autonomous transaction.
TRG_LOGOUTPUT
CREATE OR REPLACE TRIGGER TRG_LOGOUTPUT
BEFORE INSERT OR UPDATE
ON FUGA
FOR EACH ROW
DECLARE
pragma autonomous_transaction;
BEGIN
IF INSERTING THEN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'job_test',
JOB_TYPE => 'EXECUTABLE',
JOB_ACTION => 'C:\WINDOWS\system32\cmd.exe',
number_of_arguments => 3,
AUTO_DROP => TRUE);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_test',1, '/c');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_test',2, 'C:\foo\test.bat');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_test',3, :new.KeyName);
DBMS_SCHEDULER.ENABLE('job_test');
END IF;
END;
Calling DBMS_SCHEDULER.CREATE_JOB issues an implicit commit. Normally, it fails because it cannot be committed inside the trigger, but it can be avoided by using the autonomous transaction "autonomous_transaction".
The part that was solved by "Creating a dbms_scheduler.create_job with arguments --stackoverflow" is.
When I entered "enabled => TRUE" as shown below, I got an error "ORA-27457: Argument 1 (job" TEST.JOB_TEST ") has no value".
number_of_arguments => 3,
enabled => TRUE,
AUTO_DROP => TRUE);
This will take effect immediately when the job is created, but it will result in an error because the arguments have not yet been set at that point. "Enabled => TRUE" is fine if there is no argument, but if there is an argument, it must be enabled after setting the argument.
The batch (test.bat) is called from the trigger with the key as an argument. Receive this key and output the log. Unlike Java stored, it is already committed at batch execution, so there is no need to put a cushion. If it is rolled back, the target data cannot be found and no log will be output.
test.bat
cd /d %~dp0
outputlog.exe %1
In the end, the modification to output the log disappeared and it became another method, but it was a good study. I thought I could find a way by searching easily, but it's unexpected. It feels like I can finally find out from various places and combine them.