Call PLSQL procedure from Java, parameter error

[Gab Smith]

I've got this oracle plsql procedure:

PROCEDURE add_content(p_kod                 VARCHAR2
                              ,p_eform          VARCHAR2
                              ,p_megnev VARCHAR2
                              ,p_koteg             IN OUT VARCHAR2
                              ,p_page             INTEGER
                              ,p_blob             OUT BLOB
                              ,po_etm              OUT NUMBER);

I like to call from java:

String prepareCall = "{call add_content(?,?,?,?,?,?,?)}";

            cs = db.getConn().prepareCall(prepareCall);
           
            cs.setString("p_kod", "6491409579");
            cs.setString("p_eform", "tif");
            cs.setString("p_megnev", "6491409579.tif");
            cs.setString("p_koteg", "teszt-file");
            cs.registerOutParameter("p_koteg", Types.VARCHAR);
            cs.setInt("p_page", 1);
            cs.setBinaryStream("p_blob", fis, (int) f.length());
            cs.registerOutParameter("p_blob", Types.BLOB);
            cs.registerOutParameter("po_etm", Types.NUMERIC);

            cs.execute();

But it'a not work, throw parameter collision error.



I try change

cs.registerOutParameter("po_etm", Types.NUMERIC); 

to

cs.registerOutParameter("po_etm", Types.INTEGER);

but not works.

What's the problem?

Please help! Thanks for all reply!

[Martin Vajsar]

Welcome to Coderanch!

I'm not sure this is going to help, but I'd try binding the parameters by position, not by name.

If it doesn't help, post full stack trace of the exception you're getting, including the line at which it occurs.

[Tina Smith]

cs.setBinaryStream("p_blob", fis, (int) f.length());  
cs.registerOutParameter("p_blob", Types.BLOB); 

cs.registerOutParameter("p_koteg", Types.VARCHAR); 
vs PLSQL
,p_koteg             IN OUT VARCHAR2 

These look like they might cause a problem? Mainly the first one since you can't have two parameters named p_blob.

[Ravi Majety]

Please post some StacTrace . It would help others to resolve the issue.

Let us know Is this statement failed even though you set the input and output parameters to CallableStatement using indexing as below.

String prepareCall = "{call add_content(?,?,?,?,?,?,?)}";

            cs = db.getConn().prepareCall(prepareCall);
           
            cs.setString(1, "6491409579");
            cs.setString(2, "tif");
            cs.setString(3, "6491409579.tif");
            cs.setString(4, "teszt-file");
            cs.setInt(6, 1);
            cs.registerOutParameter(5, Types.VARCHAR);
            
            cs.setBinaryStream(7, fis, (int) f.length());
            cs.registerOutParameter(7, Types.BLOB);
            cs.registerOutParameter(8, Types.NUMERIC);

            cs.execute();