This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Oracle Procedure Call Hangs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle Procedure Call Hangs" Watch "Oracle Procedure Call Hangs" New topic
Author

Oracle Procedure Call Hangs

John Olmstead
Greenhorn

Joined: Nov 25, 2003
Posts: 6
Hello Collegues;

Oracle 9i procedure Called From hibernateDAOTestCase
1.4.2_05 jdk

The code snippet below hangs ( No Stacktrace) on the executeUpdate method call on a CallableStatement Object. All hibernate dao calls unrelated to stored procedures work well. Any comments or assistance is appreciated!


public void add( Job job)
{
Session s = null;
Connection conn = null;
String oracleQuery = "BEGIN MPCS.JOB_PRE_INSERT(:1 , :2); END;";
try
{
s = this.getHibernateTemplate().getSessionFactory().openSession();
conn = s.connection();
CallableStatement stmt = conn.prepareCall(oracleQuery);
stmt.registerOutParameter(1, OracleTypes.VARCHAR);
stmt.registerOutParameter(2, OracleTypes.NUMBER);
// <== Hangs Here ==>
stmt.executeUpdate();

log.info("Variable1 : "+stmt.getString(1));
log.info("Variable2 : "+stmt.getBigDecimal(2));

}

catch(HibernateException he)
{
log.warn(he.getMessage() , he);
}
catch(SQLException sqle)
{
log.warn(sqle.getMessage() , sqle);
}
finally
{
try
{
s.close();
}
catch(HibernateException he){log.warn(he.getMessage() , he);}
}


}

There is no stack trace and the Hibernate/JDBC calls work very well from the rest of the class.

Here is the stored procedure, which does work from my sql client :

newjob OUT MPCS.JOB.JOB_NO%TYPE,
cnum OUT MPCS.TEXT.CONTROL_NUM%TYPE)
AS
-- MPCS.JOB - PRE-INSERT STORED PROCEDURE - GETS NEXT JOB#.
-- CREATED 06-OCT-2004 SAB.

pre_ins_err EXCEPTION;
exit_wo_err EXCEPTION;
err_mess VARCHAR2(100);
asciijl MPCS.COSTCONTROL.ASCII_JOB_LETTER%TYPE;
jobno MPCS.COSTCONTROL.JOB_NUMBER%TYPE;
njcol1 MPCS.COSTCONTROL.NEWJOB_COL1%TYPE;
njcol2 MPCS.COSTCONTROL.NEWJOB_COL2%TYPE;
njcol3 MPCS.COSTCONTROL.NEWJOB_COL3%TYPE;
njcol45 MPCS.COSTCONTROL.NEWJOB_COL45%TYPE;
job4char MPCS.JOB.JOB_NO%TYPE;
job5char MPCS.JOB.JOB_NO%TYPE;
BEGIN
LOCK TABLE MPCS.CONTROL_NUM, MPCS.COSTCONTROL
IN SHARE UPDATE MODE;

-- Get the general job text primary key control number.
UPDATE MPCS.CONTROL_NUM
SET CONTROL_NUM = CONTROL_NUM + 1
WHERE CONTROL_NUM_TYPE = 'TEXT';
IF SQL%NOTFOUND THEN
err_mess := 'System error incrementing text control number.';
RAISE pre_ins_err;
END IF;

err_mess := 'System error retrieving next text control number.';
SELECT CONTROL_NUM INTO cnum
FROM MPCS.CONTROL_NUM
WHERE CONTROL_NUM_TYPE = 'TEXT';

-- Get the information to create the next job number.
err_mess := 'System error getting next job number.';
SELECT ASCII_JOB_LETTER, NEWJOB_COL1, CHR(ASCII_JOB_LETTER)||LTRIM(TO_CHAR(JOB_NUMBER,'009')),
CHR(NEWJOB_COL1)||LTRIM(TO_CHAR(NEWJOB_COL2,'9'))||CHR(NEWJOB_COL3)||LTRIM(TO_CHAR(NEWJOB_COL45,'09')),
JOB_NUMBER, NEWJOB_COL2, NEWJOB_COL3, NEWJOB_COL45
INTO asciijl, njcol1, job4char, job5char, jobno, njcol2, njcol3, njcol45
FROM MPCS.COSTCONTROL;

-- IF ORIGINAL JOB LETTER IS 'Z', UPDATE NEW JOB FORMAT FOR NEXT JOB CREATE.
IF asciijl < 90 THEN
newjob := job4char;
-- Add 1 to number counter...max 999.
IF jobno < 999 THEN
jobno := jobno + 1;
ELSIF jobno >= 999 THEN
-- Add 1 to letter counter.
asciijl := asciijl + 1;
-- Skip I and O and Q and S and X.
IF asciijl IN (73, 79, 81, 83, 88) THEN asciijl := asciijl + 1; END IF;
jobno := 1;
END IF;
ELSIF njcol1 < 90 THEN
-- NEW JOB FORMAT: A thru Y , 0 thru 9 , A thru Y , 01 thru 99. - 5 characters total: A0A01 will be the first. 396,000 combinations.
-- Skip I, O, Q, S, X, Z for letter characters.
newjob := job5char;
IF njcol45 < 99 THEN
njcol45 := njcol45 + 1;-- Increase to next number.
ELSE
njcol45 := 1;-- Set back to 01.
njcol3 := njcol3 + 1;-- Increase to next letter.
-- If 3rd character = Z, set back to A and increase 2nd character.
IF njcol3 = 90 THEN
njcol3 := 65;-- Set back to letter A.
njcol2 := njcol2 + 1;-- Increase to next number.
-- If 2nd character > 9, set back to 0 and increase 1st character.
IF njcol2 > 9 THEN
njcol2 := 0;-- Set back to 0.
njcol1 := njcol1 + 1;-- Increase to next letter.
-- If I, O, Q, S, or X, add 1 to letter.
IF njcol1 IN (73, 79, 81, 83, 88) THEN njcol1 := njcol1 + 1; END IF;
END IF;
ELSE
-- If I, O, Q, S, or X, add 1 to letter.
IF njcol3 IN (73, 79, 81, 83, 88) THEN njcol3 := njcol3 + 1; END IF;
END IF;
END IF;
ELSE
err_mess := 'The maximum job number has already been used. Call System Manager.';
RAISE pre_ins_err;
END IF;

-- Store next job number.
UPDATE MPCS.COSTCONTROL SET JOB_NUMBER = jobno,
ASCII_JOB_LETTER = asciijl,
NEWJOB_COL1 = njcol1,
NEWJOB_COL2 = njcol2,
NEWJOB_COL3 = njcol3,
NEWJOB_COL45 = njcol45;

EXCEPTION
WHEN pre_ins_err THEN
RAISE_APPLICATION_ERROR(-20005, err_mess);
WHEN exit_wo_err THEN
RAISE_APPLICATION_ERROR(-20000, 'Quit without error message.');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20005, err_mess);
WHEN OTHERS THEN
RAISE;
END JOB_PRE_INSERT;


John Olmstead<br />jolmstead2k@yahoo.com
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

try to execute your sp by stmt.execute()instead of stmt.executeUpdate()
[ October 09, 2004: Message edited by: Shailesh Chandra ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Possible causes are

1. Some row in a table is being updated in the database by more than one user.
In this case the second user will be waiting till the first user gives a commit.

I faced the similar problem.

If u are calling this proc from a web app try to restart the server.
and chk the Stored procedure.

Srini
[ October 09, 2004: Message edited by: srini vasan ]

Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
John Olmstead
Greenhorn

Joined: Nov 25, 2003
Posts: 6
Thank you for your replies. I have tried both stmt.execute() and stmt.executeUpdate(). Both hang. I am executing this code from a testcase for this dao.

I will be looking at alternative JDBC drivers next week.

Thanks;

John Olmstead
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Originally posted by John Olmstead:
Thank you for your replies. I have tried both stmt.execute() and stmt.executeUpdate(). Both hang. I am executing this code from a testcase for this dao.

I will be looking at alternative JDBC drivers next week.

Thanks;

John Olmstead


Will this solve u'r problem ?
 
 
subject: Oracle Procedure Call Hangs
 
Similar Threads
Help with line feed line break characters
Callable Statement
Stored procedure which returns array
java.sql.SQLException: ORA-06550
Date Difference