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
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(?,?) }