• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Oracle Procedure Call Hangs

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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;
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try to execute your sp by stmt.execute()instead of stmt.executeUpdate()
[ October 09, 2004: Message edited by: Shailesh Chandra ]
 
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
John Olmstead
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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 ?
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had a similar behavior when I was working on the same tables from SQL Developer while executing the procedure from the Java Application. It seemed that somehow, even though I committed and closed the SQL Developer, the Java application had issues with restoring the normal behavior. I had to reset the JDBC connection before everything came back to normal.
 
Marshal
Posts: 69847
278
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
VM: Welcome to the Ranch Maybe somebody will find that information helpful, even on an old thread.
 
That feels good. Thanks. Here's a tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic