SQL & PL/SQL

Hi Everyone,

May someone please help? I am getting stuck in getting values/data from the OUT parameters from an oracle function within my java class. The oracle function owa_find_acct_matches() has 1 OUT parameter. To get the basic to work, I hard coded some values in the function. In my java class, I used oracleCallableStatement and registeroutparameter() to register the OUT parameters. However, I couldn't pass the execute() line. It said 'ORA-06531: Reference to uninitialized collection ORA-06512: at "PBDS.OWA_FIND_ACCT_MATCHES", line 10 ORA-06512: at line 1'. Can someone please help? May you also show me how to get values? I pasted the TYPES, function(), and my java file. Your help is greatly appreciated.

Many thanks,
Mike

==================== TYPES ===================
CREATE OR REPLACE TYPE owa_acct_score_rec_type AS OBJECT (
account_id NUMBER( 8 ),
match_score NUMBER(3)
);
/
CREATE OR REPLACE PUBLIC SYNONYM owa_acct_score_rec_type FOR pbds.owa_acct_score_rec_type;
/
GRANT EXECUTE ON owa_acct_score_rec_type TO TOTAL;
/

CREATE OR REPLACE TYPE owa_acct_score_tab_type IS TABLE OF owa_acct_score_rec_type;
/
CREATE OR REPLACE PUBLIC SYNONYM owa_acct_score_tab_type FOR pbds.owa_acct_score_tab_type;
/
GRANT EXECUTE ON owa_acct_score_tab_type TO TOTAL;
/
==================== END TYPES =====================

=================== FUNCTION ==================
CREATE OR REPLACE FUNCTION owa_find_acct_matches
(
Exact_Match_Accts OUT owa_acct_score_tab_type
)
RETURN VARCHAR2
IS
v_exact_matches owa_acct_score_tab_type;
v_message varchar2(250);
BEGIN
v_exact_matches(1).account_id := 11111;
v_exact_matches(1).match_score := 99;
v_exact_matches(2).account_id := 22222;
v_exact_matches(2).match_score := 88;
Exact_Match_Accts := v_exact_matches;
v_message := 'Testing...';
return v_message;
END;
/
CREATE OR REPLACE PUBLIC SYNONYM owa_find_acct_matches FOR pbds.owa_find_acct_matches;
/
GRANT EXECUTE ON owa_find_acct_matches TO TOTAL;
/
=================== END FUNCTION ======================

================== MY JAVA FILE ===================
Connection con = null;
OracleCallableStatement cs = null;

try {
cs = (OracleCallableStatement)con.prepareCall("{ ? = call owa_find_acct_matches(?) }");

cs.registerOutParameter(1, OracleTypes.VARCHAR);
cs.registerOutParameter(2, OracleTypes.ARRAY, "OWA_ACCT_SCORE_TAB_TYPE");

/*
It stopped at here. Couldn't get to cs.execute(). Here is the error:
'ORA-06531: Reference to uninitialized collection ORA-06512: at "PBDS.OWA_FIND_ACCT_MATCHES", line 10 ORA-06512: at line 1'
*/

cs.execute();

/*
How to set the values out from the second OUT PARAMETER "OWA_ACCT_SCORE_TAB_TYPE"?
*/

}
catch (SQLException se){
}
finally {
}

Message was edited by:
Dalomi