CODEWITHSUNDEEP
javaoracle-databasejdbcplsql
user10218380
user10218380

Reputation:

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

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?

Upvotes: 0

Views: 1639

Answers (1)

Vadim
Vadim

Reputation: 4120

  1. 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...

  1. 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.

Upvotes: 2

Related Questions