• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Calling stored procedure problems

 
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Avi,

If you need more information let me know what i can provide
Thanks...
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic