java の CallableStatementインタフェース で Oracle Database の
ストアド・プロシージャ を コールして、V$ACTIVE_SESSION_HISTORY の
PROGRAM列 や MODULE列 に何がセットされるかを見てみるやで彡(゚)(゚)
下記サイトを参考にしながら書いてみたやで。
相変わらずjava不慣れマンなんで、そこは勘弁や彡(-)(-)
JDBCからストアドプロシージャを呼び出す(Oracle、SQL Server、MySQL)
http://hito4-t.hatenablog.com/entry/2015/04/06/185902
インタフェースCallableStatement
https://docs.oracle.com/javase/jp/8/docs/api/java/sql/CallableStatement.html
try-with-resources文 ※8/11追記
http://docs.oracle.com/javase/jp/7/technotes/guides/language/try-with-resources.html
ストアド・プロシージャの定義は下記彡(゚)(゚)
CREATE OR REPLACE PROCEDURE PRC_CALL_SP
IS
d_date DATE;
BEGIN
FOR i IN 1..1000000
LOOP
SELECT SYSDATE INTO d_date FROM DUAL;
END LOOP;
END;
/
javaソースのサンプルは下記彡(゚)(゚)
※8/11修正 Class.forName(~)を削除、try-with-resources構文に変更
※8/15修正 exception廻りとかcommit廻りとかを修正
import java.sql.*;
public class StoredCall {
public static void main(String[] args) {
//initialize
System.out.println("Init...");
//DB connection info
final String path = "jdbc:oracle:thin:@localhost:1521:orcl"; //path
final String id = "XXXXXXXX"; //ID
final String pw = "YYYYYYYY"; //password
//try-with-resources Statement
try (
//DB Connect
Connection conn = DriverManager.getConnection(path, id, pw);
//Stored Procedure Statement Set.
CallableStatement cs = conn.prepareCall("CALL PRC_CALL_SP()");
) {
//AutoCommit Setting
conn.setAutoCommit(false);
//Stored Procedure Execute
System.out.println("Call stored procedure...");
cs.execute();
//Commit
conn.commit();
} catch(SQLException ex) {
//SQLException
ex.printStackTrace(); //Error
}
//End
System.out.println("End...");
}
}
漢の生javacコンパイル、IDEって何や?彡(゚)(゚)
javac -classpath .:${ORACLE_HOME}/jdbc/lib/ojdbc8.jar StoredCall.java
やはり漢の生java実行、IDEってn(ry
java -classpath .:${ORACLE_HOME}/jdbc/lib/ojdbc8.jar StoredCall
javaモジュールの実行結果、こちらはメッセージが出てくるだけ彡(゚)(゚)
Init...
Call stored procedure...
End...
下記は V$ACTIVE_SESSION_HISTORY の参照結果、
PROGRAM も MODULE も JDBC Thin Clientや!彡(゚)(゚)
SET LINESIZE 170;
SET PAGESIZE 300;
COLUMN sid FORMAT 9999;
COLUMN ss FORMAT A10;
COLUMN event FORMAT A30;
COLUMN program FORMAT A20;
COLUMN module FORMAT A20;
SELECT session_id AS sid
, sql_id
, top_level_sql_id
, session_state as ss
, event
, program
, module
FROM v$active_session_history
WHERE sample_time >= systimestamp - (3 / 24 / 60)
AND user_id = 131
ORDER BY sample_time DESC;
SID SQL_ID TOP_LEVEL_SQL SS EVENT PROGRAM MODULE
----- ------------- ------------- ---------- ------------------------------ -------------------- --------------------
17 1vq4vx6n0r4cn 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 1vq4vx6n0r4cn 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 c749bc43qqfz3 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 c749bc43qqfz3 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 1vq4vx6n0r4cn 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 c749bc43qqfz3 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 c749bc43qqfz3 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 c749bc43qqfz3 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
17 c749bc43qqfz3 1vq4vx6n0r4cn ON CPU JDBC Thin Client JDBC Thin Client
:
SELECT sql_id, sql_text FROM V$SQL
WHERE sql_id IN ('1vq4vx6n0r4cn', 'c749bc43qqfz3');
SQL_ID SQL_TEXT
------------- ---------------------------
c749bc43qqfz3 SELECT SYSDATE FROM DUAL
1vq4vx6n0r4cn CALL PRC_CALL_SP()