Forums Register Login

Calling stored procedure problems

+Pie Number of slices to send: Send
I am a bit fed up about this stored procedure. Here is the summmary i ahve a package spec called DCT_2_0 and there types is declared as follows with the procedure decleration
TYPE t_Model_Id IS TABLE OF EVDB.MDB_VALUE.VALUE_CODE%YPE INDEX BY BINARY_INTEGER;
TYPE t_Model_Name IS TABLE OF EVDB.MDB_VALUE.VALUE_NAME%TYPE INDEX BY BINARY_INTEGER;
PROCEDURE getModels
( p_Year_Code IN VARCHAR2,
p_Make_Code IN VARCHAR2,
P_Model_Id OUT t_Model_Id,
p_Model_Name OUT t_Model_Name);

Now the body of the package is
PROCEDURE getModels
(p_Year_Code IN VARCHAR2,
p_Make_Code IN VARCHAR2,
P_Model_Id OUT t_Model_Id,
p_Model_Name OUT t_Model_Name) IS

v_count NUMBER := 0;

CURSOR c_Models IS
SELECT dm.model_code, mv.value_name
FROM EVDB.DCT_MODEL dm, EVDB.MDB_VALUE mv
WHERE dm.model_code = mv.value_code
AND dm.make_code = p_Make_Code
AND dm.year_code = p_Year_Code
ORDER BY mv.value_name;

BEGIN
FOR r_Models IN c_Models
LOOP
--FILL THE RECORDSET
v_count := v_count + 1;
p_Model_Id(v_count) := r_Models.model_code;
p_Model_Name(v_count) := r_Models.value_name;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
CLOSE c_Models;
DBMS_OUTPUT.PUT_LINE('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;

Here is hte java code whihc i tried

CallableStatement procCall = null;
procCall = conn.prepareCall("{ call EVDB.DCT_2_0.getModels(?,?,?,?)}");
procCall.setString(1,"K01439");
procCall.setString(2,"K00014");
procCall.registerOutParameter(3,OracleTypes.ARRAY,"T_MODEL_ID");
procCall.registerOutParameter(4,OracleTypes.ARRAY,"T_MODEL_NAME");
procCall.execute();

But it gives an error

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETMODELS'

i can't figure it out why this problem since the types are already declared in the package spec. any help will be very much apprecaited
I even tried creating my own types in oracle and then making array descriptors but still it didn't work
i u want i can post that code as well
Thanks
+Pie Number of slices to send: Send
Rashid,
According to the [lack of] information you have provided, I can only make a guess, but my guess is that you are trying to do something that is not supported by the JDBC driver and/or database you are using.

If you haven't already done so, I suggest perusing the Oracle documentation, namely the JDBC User's Guide and Reference and look at the JDBC Web page.

Good Luck,
Avi.
+Pie Number of slices to send: Send
Hi Avi,

If you need more information let me know what i can provide
Thanks...
rubbery bacon. rubbery tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1524 times.
Similar Threads
Stored procedure which returns array
Need Help with pl/sql (Compilation Error)
Handling cursor declared in oracle in java program
Passing array input in procedure - thin driver
registerOutParameter Problem when the type is ARRAY
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 19, 2024 04:45:59.