0

I have created a stored procedure in oracle like this

CREATE OR REPLACE PROCEDURE NETSALARY 
(
  EMP_ID IN NUMBER 
) AS 
ns_id employees.employee_id%TYPE;
ns_name employees.first_name%TYPE;
ns_salary number(9,2);
BEGIN
  select employee_id, first_name, 
case
    when (commission_pct is null) 
        then 0.9*salary
    when (commission_pct*salary)<500 
        then 0.85*(salary + commission_pct*salary)
    else 
        0.8*(salary + commission_pct*salary)
end net_salary
into
ns_id, ns_name, ns_salary
from employees
where employee_id = emp_id;
dbms_output.put_line(ns_id||' , '||ns_name||' , '||ns_salary);
END NETSALARY;

I am trying to get the output in Eclipse IDE as

import java.sql.*;

public class UCST {

public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "user";
        String pwd = "password";
        Connection conn;
        try {
            conn = DriverManager.getConnection(url, user, pwd);
            CallableStatement cst = conn.prepareCall("{call netsalary(?)}");
            cst.setInt(1, 127);
            cst.execute();
            System.out.println(cst.getInt(1)+", "+cst.getString(2)+", "+cst.getInt(3));
            cst.close();
            conn.close();
        }
        catch(Exception e) {
            System.out.println("Connection could not be established....");
            e.printStackTrace();
        }
    }

}

but I keep getting the error

java.sql.SQLException: Invalid column index
    at ojdbc8/oracle.jdbc.driver.OracleCallableStatement.getInt(OracleCallableStatement.java:1338)
    at ojdbc8/oracle.jdbc.driver.OracleCallableStatementWrapper.getInt(OracleCallableStatementWrapper.java:617)
    at UCST.main(UCST.java:15)

I have tried with converting to ResultSet too, but the console just shows terminated, without any warning. How do I resolve this?

akarnokd
  • 69,132
  • 14
  • 157
  • 192
  • 1
    @MarkRotteveel While that may solve the OP's XY-problem, the actual solution should be to fix their stored procedure so it does not use `DBMS_OUTPUT` and, instead, returns a cursor (or a collection) that contains the result set and then iterate over that in Java. Which would make this a duplicate of [Using cursors and getting result in Oracle PL/SQL with Java/JDBC](https://stackoverflow.com/q/16047179/1509264) – MT0 Jun 25 '22 at 11:55
  • @MT0 Maybe, but my duplicate answers the question as asked. – Mark Rotteveel Jun 25 '22 at 12:01

0 Answers0