CODEWITHSUNDEEP
javasql-serverhibernatestored-procedures
akshaivk
akshaivk

Reputation: 427

Calling Oracle procedures in java

I have a procedure in oracle to decrypt password with one in and one out parameters. I am trying to call in my java program as follows but it shows SQLGrammarException. I am new to Oracle and trying this for first time. Sorry I Searched in internet but I didn't find anything. In SQL server I was using like this.

Iterator it = hibernateSession.createSQLQuery("EXEC DAN_DANAPP_DECRYPPWD '" + username.getText().trim() + "'").list().iterator();

it was working in SQL server. When I tried the same in Oracle it was not working. Can anyone spot me out what mistake I had attempted.

My Oracle procedure is as follows

CREATE OR REPLACE PROCEDURE DAN0703.DAN_DANAPP_DECRYPPWD 
(EMPNO IN VARCHAR2,P_STRING OUT VARCHAR2)
IS

 P_KEY_STRING  VARCHAR2(12) := 'xxxxxxxxxxxx';
 P_ERROR_NO NUMBER;
 RAW_KEY RAW(128);
 DECRYPTED_RAW RAW(2048);

BEGIN

 RAW_KEY := UTL_RAW.CAST_TO_RAW(P_KEY_STRING);

 select USER_PASSWD into P_STRING  FROM MENU_USER  WHERE USER_ID = EMPNO  AND 
 USER_FLAG = 'O' and (USER_DISABLE_FLAG='N' or USER_DISABLE_FLAG='V');

 DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT => P_STRING, KEY => RAW_KEY,  
 DECRYPTED_DATA => DECRYPTED_RAW );

 P_STRING := UTL_RAW.CAST_TO_VARCHAR2(DECRYPTED_RAW);

END;

Thanks in advance for your support.

Upvotes: 0

Views: 289

Answers (2)

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

Maybe something like that:

Iterator it = loginsession.createSQLQuery("CALL DAN_DANAPP_DECRYPPWD(:EMPNO)")
.setParameter("EMPNO", username.getText().trim()).list().iterator();

EDIT

A link explaining of to deal with out parameters with JPA:

https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/

Upvotes: 1

pleft
pleft

Reputation: 7905

In oracle to call a procedure you have to code:

loginSession.createSQLQuery("CALL DAN0703.DAN_DANAPP_DECRYPPWD(:username)")
    .setParameter("username", username.getText().trim());

Upvotes: 1

Related Questions