Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to capture the exception in oracle trigger from Java client?

 
Reico Cruise
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Group

I have a inserting trigger on my table. The insert
operation failed if the trigger failed. But in my Java
client I have never catched the SQL exception (caused
by the insert statement). The error is just
disregarded silently. To make sure I did it right, I
also did a test on a store procedure which does
exactly same thing as the trigger, I can capture the
exception caused by the stored procedure. If I execute
the insert statement and the stored procedure in
SQL/PLUS, both were failed and shown me the exception.
My question is : 1. is the insert statement be
considered as fail if the trigger is failed? If, yes,
how can I capture this exception?
2. Understand that you always need to add exception
handling in your stored procedure or trigger, but even
if I did not add exception section in my stored
procedure, the exception also be catched by my java
program. This is confused me.
Following is my trigger and procedure
------------------------------------------
Create or replace procedure TErr as
v_test number;
BEGIN
select a.a from a where 1=2; --cause NO_DATA_FOUND
exception
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_RECORD_FOUND_EXCEPTION:'||sqlerrm);
RAISE;
END;
------------------------------------------
------------------------------------------
CREATE or REPLACE TRIGGER TErr2
AFTER INSERT ON b
FOR EACH ROW
DECLARE v_test number;
BEGIN
select a.a from a where 1=2; --cause NO_DATA_FOUND
exception
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_RECORD_FOUND_EXCEPTION:'||sqlerrm);
RAISE;
END;
------------------------------------------
my Java code:
------------------------------------------
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
Connection conn;
//Create Conn
try {
PreparedStatement preparedStmt =
conn.prepareStatement("insert into b(b) values(1);");
preparedStmt.execute();
preparedStmt.close();
CallableStatement pstmt = conn.prepareCall("{call
\"TErr\"()}");
pstmt.execute();
pstmt.close();
}
catch (SQLException sqle) {
//process exception
}
------------------------------------------

Anybody can help? Thanks a lot

-Reico
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic