using :new in oracle JDBC Query

[Kedar Bhawarthi]

Hello,
I calling a Java procedure from within oracle trigger.
But in that procedure I am not being able to get access of :new variable in query fired by JDBC.
I get error as
oracle.jdbc.driver.OracleSQLException: ORA-01008: not all variables bound

Please help me to fix this.


My Trigger is written like this

    
CREATE OR REPLACE
TRIGGER FRAUD_SCORE_TABLE_AFTER_INSERT AFTER INSERT ON FRAUD_SCORE_TBL 
FOR EACH ROW 
BEGIN
  EVENT_TABLE_CHANGE_NOTIFIER.FRAUD_SCORE_TBL_ROW_ADDED(:NEW.SCORE_ID,:new.TABLE_ID,:new.HAND_ID,:new.SCORE,:new.STATUS) ;
END;

...where FRAUD_SCORE_TBL_ROW_ADDED procedure maps to a java procedure 'rowAdded'


I am trying to execute a code in rowAdded as...

    
//.....
con = new OracleDriver().defaultConnection ();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select :new.SCOREID,GM_INFO.TABLE_NAME FROM GM_INFO WHERE GM_INFO.TABLE_ID = :new.TABLE_ID");

My understanding is, if some code, executing under trigger (may be written in java, or PL/SQL) should have access to this :new variable.
Please correct me if I amk wrong.

[Avi Abrami]

Bhawarthi,
Any stored procedure (either java or PL/SQL or C) does not have access to :new or ld. You can only access these in the trigger code, not in the code of the procedure that is called by the trigger code.

Good Luck,
Avi.

[Kedar Bhawarthi]

thanks Avi, I am convinced to this fact now and adopted a workaround (a constrained one), thanks for your reply.