JDBC - Calling PLSQL from JAVA gives Java.sql.SQLException: ORA-06550

Question

I'm in a huge trouble with my call to a PLSQL from Java. Here's my code:

static final String PLSQL = "{call DBK_PDG_METADATI_CEDOLINO.dbp_main(?,?,?,?,?,?,?,?,?)}";

Connection conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
CallableStatement cs = conn.prepareCall(PLSQL);

    for (Cedolino item : items) {
        LOG.info("################# ELABORAZIONE CEDOLINO " + item.getTestata().getAnagrafica().getCodFiscaleAmministrato() + " #################");
        cedolini.getCedolino().add(item);
        setParametersForPlSql(cs, item);

        try{
            cs.execute();
        }catch(SQLException e){
            LOG.info(e.toString());
        }

    }

cs.close();
conn.close();

private void setParametersForPlSql(CallableStatement cs, Cedolino ced){

    try {
        cs.setInt("tipo_lancio", 1);
        cs.setString("iscr", ced.getTestata().getTrattamento().getIscrizione().trim());
        cs.setString("rts", ced.getTestata().getDpt().trim());
        cs.setString("codfisc", ced.getTestata().getAnagrafica().getCodFiscaleAmministrato().trim());
        cs.setString("lingua", this.lingua);
        cs.setString("file_name", null);
        cs.setString("dir_spec", null);
        cs.setString("stato_elab", "S");
        cs.setString("descr_elab", null);


    } catch (SQLException e) {
        e.printStackTrace();
    }

}

This code works good except for cs.execute, that gives me this error

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'DBP_MAIN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I checked a thousand times, parameters and types and numbers match perfectly. DB connection is also good, because I do some caching first and it works.

Already tried to delete DBK_PDG_METADATI_CEDOLINO but nothing it is needed. Can you help me to figure out it?

If you use positional placeholders (?), you should use index-based setters (although I'm not 100% sure if Oracle may allow named any way in that case). — Mark Rotteveel
– Mark Rotteveel, Commented Oct 26, 2018 at 18:02

Vadim · Accepted Answer · 2018-10-26 17:14:53Z

2

Problem could be related to the JDBC driver which may not support Named Parameters.

Try to check it first like:

  Connection myConn = . . .   // connection to the RDBMS for Database
  DatabaseMetaData dbmd = myConn.getMetaData();
  if (dbmd.supportsNamedParameters() == true)
  {
      System.out.println("NAMED PARAMETERS FOR CALLABLE"
                        + "STATEMENTS IS SUPPORTED");
  }

and if it is not - use parameter indexes to set instead of names...

Is there any OUT or INOUT parameters in that Stored Procedure?

If so, you need to register those parameters using registerOutParameter of the CallableStatement and give a placeholder for output.

answered Oct 26, 2018 at 17:14

Vadim

4,1302 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user10218380 Over a year ago

Sorry for late answer! I tried your solution using parameters indexes and.. it works! The reason could be that first I used ojbdc14 as jar and named parameters were ok, then after a downgrade to ojdbc5 were not anymore. Thank you man!

Collectives™ on Stack Overflow

JDBC - Calling PLSQL from JAVA gives Java.sql.SQLException: ORA-06550

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related