Call Procedures From java With oracle database

Question

Hello to All stackOverflow goodfellas , I'm so noob in The Procedures with oracle database 21c enterprise edition , As my past background always works with Direct jdbc but now I need to write program with Procedures that call and every time I need to change some Algorithm to call data from database don't change the main code form java . as you know if I do, need compile , build and deploy to server get more time , so going to straight problem view!(I'm not sure is good way or we have better way) I have two table Like :

CREATE TABLE C##CREATOR.USERS 
(
  ID NUMBER(11, 0) NOT NULL 
, USERNAME VARCHAR2(20 CHAR) NOT NULL 
, DATECREATION TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL 
, CONSTRAINT USERS_PK PRIMARY KEY 
  (
    ID 
  )
  DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  (
      CREATE INDEX C##CREATOR.USERS_PK ON C##CREATOR.USERS (ID ASC) 
      LOGGING 
      TABLESPACE DPS_PERMANENT 
      PCTFREE 10 
      INITRANS 2 
      STORAGE 
      ( 
        INITIAL 65536 
        NEXT 1048576 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        BUFFER_POOL DEFAULT 
      ) 
      NOPARALLEL 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE DPS_PERMANENT 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NO INMEMORY 
NOPARALLEL;

And :

CREATE TABLE C##CREATOR.USERPASSWORDS 
(
  ID NUMBER(11, 0) NOT NULL 
, USERID NUMBER(11, 0) NOT NULL 
, PASSWORD VARCHAR2(20 BYTE) NOT NULL 
, DATECREATION TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL 
, CONSTRAINT USERPASSWORDS_PK PRIMARY KEY 
  (
    ID 
  )
  USING INDEX 
  (
      CREATE UNIQUE INDEX C##CREATOR.USERPASSWORDS_PK ON C##CREATOR.USERPASSWORDS (ID ASC) 
      LOGGING 
      TABLESPACE DPS_PERMANENT 
      PCTFREE 10 
      INITRANS 2 
      STORAGE 
      ( 
        INITIAL 65536 
        NEXT 1048576 
        MINEXTENTS 1 
        MAXEXTENTS UNLIMITED 
        BUFFER_POOL DEFAULT 
      ) 
      NOPARALLEL 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE DPS_PERMANENT 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NO INMEMORY 
NOPARALLEL;

As other side In Procedures :

CREATE OR REPLACE PROCEDURE SYSTEM_LOGIN_GET_USER_WITH_PASSWORD 
( 
INP_USERNAME IN VARCHAR2
, user_cursor OUT SYS_REFCURSOR 
) AS 
BEGIN 
    OPEN user_cursor FOR
        SELECT USERS.USERNAME , USERS.ID , USERPASSWORDS.PASSWORD FROM USERS 
        LEFT OUTER JOIN USERPASSWORDS 
        ON USERS.ID = USERPASSWORDS.USERID WHERE
        USERS.USERNAME = INP_USERNAME ORDER BY USERPASSWORDS.DATECREATION DESC
        FETCH FIRST 1 ROWS ONLY;
END SYSTEM_LOGIN_GET_USER_WITH_PASSWORD;

If Calling from Java with :

package mehritco.ir.megnatis.dps.repository;

import mehritco.ir.megnatis.dps.repository.rdbms.oracle.OracleConnection;
import mehritco.ir.megnatis.users.User;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import java.sql.*;

public class RepoUsers {

    public User get(User userToFind){
        try {
            Connection oracleConnection = OracleConnection.getPoolConnection().getConnection();
            String queryToGetUser ="{ execute SYSTEM_LOGIN_GET_USER_WITH_PASSWORD(?,?) }";
            CallableStatement callableStatement = oracleConnection.prepareCall(queryToGetUser);
            callableStatement.setString(1,userToFind.getUsername());
            callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
            callableStatement.execute();
            ResultSet resultSet = ((OracleCallableStatement)callableStatement).getCursor(2);
            while (resultSet.next()) {
                for(int i=1;i<=resultSet.getMetaData().getColumnCount();i++){
                    System.out.println(resultSet.getString(i));
                }
            }
            return null;//for test
        }catch (SQLException sqlException){
            sqlException.printStackTrace();
            System.out.println(sqlException.getMessage());
            return null;
        }
    }


}

I got error :

java.sql.SQLException: Non supported SQL92 token at position: 3

Answer 1

I found my Mistake around :

{ execute SYSTEM_LOGIN_GET_USER_WITH_PASSWORD(?,?) }

and when changed to 'call' works.

{ call SYSTEM_LOGIN_GET_USER_WITH_PASSWORD(?,?) }