*
The moose likes JDBC and the fly likes Calling stored procedure problems Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Calling stored procedure problems" Watch "Calling stored procedure problems" New topic
Author

Calling stored procedure problems

Rashid Darvesh
Ranch Hand

Joined: Feb 13, 2004
Posts: 189
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
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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.
Rashid Darvesh
Ranch Hand

Joined: Feb 13, 2004
Posts: 189
Hi Avi,

If you need more information let me know what i can provide
Thanks...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling stored procedure problems
 
Similar Threads
Handling cursor declared in oracle in java program
Stored procedure which returns array
Need Help with pl/sql (Compilation Error)
registerOutParameter Problem when the type is ARRAY
Passing array input in procedure - thin driver