J2ee app gets stuck at a stored procedure call

[anushka pancham]

J2ee application using struts gets stuck at a stored procedure call and never comes out of it. The connetion is never released to the pool. It takes the system down. I am using a cursor in the stored procedure. Can anybody suggest what could be causing this?

[Julian Kennedy]

Possibly an infinite loop or an extremely inefficient query in your stored procedure. The latter is not uncommon during development. Can you post your SP along with an explanation of what it is doing and and idea of the data volumes on the tables involved?

Jules

[Prashant Jain]

Along with Julian's suggestions,if I was in your position I would also look at the fact

1. Are you using multithreading?
2. If so ..is there a select <table> for update , on the same table being run by another thread?

If you are using connection pooling, you could consider debuging your application setting pool size to 1.

It would be good if you could show your stored prc as well as the java code accessing it.

Prashant Jain
[e-mail]

[anushka pancham]

My stored procedure is:

CREATE OR REPLACE FUNCTION Validate_Css_Status
(    p_request_id      IN VARCHAR2,
     o_request_id         OUT VARCHAR2
 
)
  RETURN VARCHAR2
IS
 
 
status VARCHAR2(60);
l_css_status VARCHAR2(100);
l_resolved_on VARCHAR2(100);
        l_no_days  NUMBER(5) :=0;
l_count NUMBER(5) :=0;
        CURSOR c_status IS
   SELECT UPPER(ps.status_name) status_name,pr.request_id r_id
     FROM PMNT_REQUESTS pr, PMNT_STATUSES ps, PMNT_REQUEST_DETAILS prd
      WHERE pr.status_id=ps.status_id
      AND pr.request_id = prd.request_id
      AND prd.request_type_id=(SELECT request_type_id FROM PMNT_REQUEST_TYPES WHERE request_type_name='EPM - Client Satisfaction Survey')
      AND prd.parameter11= p_request_id AND prd.batch_number=1
   ORDER BY r_id;
 
CSS_record c_status%ROWTYPE;
 
BEGIN
o_request_id :=0;
SELECT user_data2 INTO l_no_days FROM PNTA_LOOPUPS WHERE loopup_type='EPM - CS Rules' AND loopup_code='days_active';
SELECT parameter9 INTO l_resolved_on FROM PMNT_REQUEST_DETAILS WHERE request_id= p_request_id    AND batch_number=1 AND  
request_type_id=(SELECT request_type_id FROM PMNT_REQUEST_TYPES WHERE request_type_name='EPM - Service Request');
 
--fetch the availability of css object
SELECT COUNT(*) INTO l_count
FROM PMNT_REQUESTS pr, PMNT_REQUEST_DETAILS prd
  WHERE pr.request_id = prd.request_id
  AND prd.request_type_id=(SELECT request_type_id FROM PMNT_REQUEST_TYPES WHERE request_type_name='EPM - Client Satisfaction Survey')
  AND prd.parameter11 = p_request_id AND prd.batch_number=1;
 
IF l_count = 1 THEN
  SELECT UPPER(ps.status_name),pr.request_id INTO l_css_status,o_request_id
  FROM PMNT_REQUESTS pr, PMNT_STATUSES ps, PMNT_REQUEST_DETAILS prd
   WHERE pr.status_id=ps.status_id
   AND pr.request_id = prd.request_id
   AND prd.request_type_id=(SELECT request_type_id FROM PMNT_REQUEST_TYPES WHERE request_type_name='EPM - Client Satisfaction Survey')
   AND prd.parameter11= p_request_id AND prd.batch_number=1;
 
  IF l_css_status = 'CLIENT SURVEY COMPLETE' THEN
     status := 'COMPLETED';
  ELSIF l_css_status = 'PENDING RESPONSE TO CLIENT SURVEY' THEN
   status := 'OPEN';
  ELSE
  status := '';
  END IF;
ELSIF l_count = 0 THEN
  status := 'NEW';
   ELSE
   OPEN c_status;
   LOOP
   FETCH c_status INTO CSS_record;
   l_css_status := CSS_record.status_name;
   o_request_id := CSS_record.r_id;
   IF l_css_status = 'CLIENT SURVEY COMPLETE' THEN
   status := 'COMPLETED';
   EXIT;
   ELSIF l_css_status = 'PENDING RESPONSE TO CLIENT SURVEY' THEN
   status := 'OPEN';
   ELSE
   status := '';
   END IF;
   END LOOP;
   CLOSE c_status;
 
END IF; -- l_count
 
IF (status != 'COMPLETED') AND (status != 'NEW') THEN
 IF
  (TO_DATE(l_resolved_on, 'yyyy/mm/dd HH24:mi:ss' ) + l_no_days) < SYSDATE
    THEN
   status := 'EXPIRED';
    END IF; -- first if
 END IF;
 
 
and java code in DAO:
    public static CSSDetails getCSSDetails(String srNumber) throws SQLException, Exception {
        CSSDetails cssDetails = null;
        Connection conn = null;
        CallableStatement cs = null;
       
        try {
            log.debug("#########getCSSDetails started#########");
            conn = DbUtils.getDBConnection();
            cs = conn.prepareCall("{? = call KNTA_MAIN.mdt_epm_validate_css_status(?,?)}");
            cs.registerOutParameter(1,Types.VARCHAR);
            cs.setString(2,srNumber);
            cs.registerOutParameter(3,Types.VARCHAR);
            cs.execute();
            String status = cs.getString(1); // status
            String cssNumber = cs.getString(3); // css number
            cssDetails = new CSSDetails(cssNumber, status);
            log.debug("Status "+status+" cssNumber "+cssNumber);
            log.debug("#########getCSSDetails Ended#########");
        } catch (SQLException se) {
            log.error("SurveyDAO.getCSSDetails(): SQLException "+se.getMessage());
            throw se;
        } catch (Exception e) {
            log.error("SurveyDAO.getCSSDetails(): Exception "+e.getMessage());
            throw e;
        } finally {
            DbUtils.close(cs);
            DbUtils.close(conn);
            return cssDetails;
        }
    } 

And the way it is called in the Actionseervlet is:

try {
cssDetails = SurveyDAO.getCSSDetails(requestId);
 
}
 
catch{}

These are the details of the code. I went through the code a lot of times but never found any hole in that.

[edited to add code tags]
[ August 25, 2004: Message edited by: Jeanne Boyarsky ]

[anushka pancham]

How to set a time out on a connection in the connection pools.

[Julian Kennedy]

Anushka,

Can you please edit your post and put your original code, with all the indentation intact, inside the UBB CODE tags? It's impossible to read at the moment. Can you please also provide the volume info I asked for?

How long does your SP take to execute when you execute it from SQL*Plus or TOAD with the same parameters, rather than from Java?

Thanks

Jules

[anushka pancham]

Julian,

I do not know what tags you are talking about . I cut pasted the code from toad...

[Julian Kennedy]

Can you see how all your indentation has disappeared in your post? That makes it really hard to try and diagnose your problem. If you look next to the Instant Graemlins on the "Post A Reply" page (or even edit) there are a set of buttons that allow you to mark up your post, kind of HTML-style. Use the CODE button and put your TOAD code between the tags it produces. People should then be able to read your code without too much effort.

Jules

[anushka pancham]

CREATE OR REPLACE FUNCTION Mdt_Epm_Validate_Css_Status
(    p_request_id      IN VARCHAR2,
     o_request_id         OUT VARCHAR2
 
)
  RETURN VARCHAR2
IS
        status VARCHAR2(60);
        l_css_status VARCHAR2(100);
l_resolved_on VARCHAR2(100);
        l_no_days  NUMBER(5) :=0;
l_count NUMBER(5) :=0;
 
CURSOR c_status IS
SELECT UPPER(ps.status_name) status_name,pr.request_id r_id     
FROM PMNT_REQUESTS pr, PMNT_STATUSES ps, PMNT_REQUEST_DETAILS prd    
WHERE pr.status_id=ps.status_id     
AND pr.request_id = prd.request_id      
AND prd.request_type_id=(SELECT request_type_id FROM PMNT_REQUEST_TYPES    WHERE request_type_name='EPM - Client Satisfaction Survey')
AND prd.parameter11= p_request_id AND prd.batch_number=1   
ORDER BY r_id;
IF l_count = 1 THEN
     SELECT UPPER(ps.status_name),pr.request_id 
     INTO l_css_status,o_request_id 
     FROM PMNT_REQUESTS pr, PMNT_STATUSES ps, PMNT_REQUEST_DETAILS prd  
     WHERE pr.status_id=ps.status_id  
     AND pr.request_id = prd.request_id   
     AND prd.request_type_id=(SELECT request_type_id FROM PMNT_REQUEST_TYPES WHERE request_type_name='EPM - Client Satisfaction Survey')      
AND prd.parameter11= p_request_id AND prd.batch_number=1;   
IF l_css_status = 'CLIENT SURVEY COMPLETE' THEN     
status := 'COMPLETED';  
ELSIF l_css_status = 'PENDING RESPONSE TO CLIENT SURVEY' 
THEN    status := 'OPEN';
ELSEstatus := '';
END IF;
ELSIF l_count = 0 THEN
status := 'NEW';
ELSE  
OPEN c_status; 
LOOP  
   FETCH c_status INTO CSS_record;
   l_css_status := CSS_record.status_name; 
   o_request_id := CSS_record.r_id;
   IF l_css_status = 'CLIENT SURVEY COMPLETE' THEN
   status := 'COMPLETED';
   EXIT;
ELSIF l_css_status = 'PENDING RESPONSE TO CLIENT SURVEY' THEN
status := 'OPEN';
  ELSE  status := ''; 
  END IF; 
  END LOOP;
CLOSE c_status; 
END IF; -- l_count 
 

IF (status != 'COMPLETED') AND (status != 'NEW') THEN
IF (TO_DATE(l_resolved_on, 'yyyy/mm/dd HH24:mi:ss' ) + l_no_days) < SYSDATE  
  THEN
    status := 'EXPIRED'; 
   END IF; -- first if
 END IF;
END
 

[anushka pancham]

I posted the stored procedure and the java code used for calling it. PLease let me know if you find something.

[Julian Kennedy]

There doesn't seem to be anything wrong with the Java code (that would cause the problem). What happens when you run the stored procedure directly in Oracle, outside Java?

Jules

[Julian Kennedy]

Under what conditions does your problem occur? I mean what path through the stored procedure is being taken when it locks up? As far as I can see, when l_count = 1 or l_count = 0 the SP should complete with no problems. The only dubious bit of code is the following:

OPEN c_status;
LOOP
    FETCH c_status INTO CSS_record;
    l_css_status := CSS_record.status_name;
    o_request_id := CSS_record.r_id;
 
    IF l_css_status = 'CLIENT SURVEY COMPLETE' THEN
        status := 'COMPLETED';
EXIT;
    ELSIF l_css_status = 'PENDING RESPONSE TO CLIENT SURVEY' THEN
        status := 'OPEN';
    ELSE
        status := '';
    END IF;
END LOOP;
CLOSE c_status;

The only condition under which the loop will exit is when l_css_status = 'CLIENT SURVEY COMPLETE'. If the cursor doesn't retrieve a record with exactly that status then you'll get an infinite loop; it will never exit.

I suspect that's your problem. If you run your cursor SQL outside the Java, filling in the required request_id parameter, you should be able to verify that.

Jules