We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

call oracle 8i stored procedure in servlets

iamjhkang
iamjhkang asked
on
Medium Priority
824 Views
Last Modified: 2013-11-24
I have something strange.
Here are source codes.
only stored procedure works ok.

1/3. Oracle DDL Source code ======================
CREATE TABLE TBL_AGREE_AMT
(
  EIS_YEAR    VARCHAR2(4)                       NOT NULL,
  GROUP_CODE  VARCHAR2(5)                       NOT NULL,
  AGREE_AMT   NUMBER(15)                        DEFAULT 0
)

ALTER TABLE TBL_AGREE_AMT ADD (
  PRIMARY KEY (EIS_YEAR, GROUP_CODE)
)

2/3. Oracle Procedure DDL Source code ============
CREATE OR REPLACE PROCEDURE SP_AGREE_AMOUNT_CRU
(
  P_EIS_YEAR           IN VARCHAR2,
  P_GROUP_CODE            IN VARCHAR2,
  P_AGREE_AMT            IN NUMBER
)
AS
  DATA_COUNT NUMBER;

BEGIN

  BEGIN

      SELECT COUNT(*)
      INTO DATA_COUNT
      FROM TBL_AGREE_AMT
      WHERE TBL_AGREE_AMT.EIS_YEAR = P_EIS_YEAR
        AND TBL_AGREE_AMT.GROUP_CODE = P_GROUP_CODE;

   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             DATA_COUNT := 0;

  END;

  IF DATA_COUNT = 0 THEN
     BEGIN
       INSERT INTO TBL_AGREE_AMT
        (
         EIS_YEAR,
         GROUP_CODE,
         AGREE_AMT
         )
       VALUES
        (
         P_EIS_YEAR,
         P_GROUP_CODE,
         P_AGREE_AMT
       );

     END;
  END IF;


  IF DATA_COUNT > 0 THEN
     BEGIN
       UPDATE TBL_AGREE_AMT
       SET AGREE_AMT      = P_AGREE_AMT
       WHERE EIS_YEAR       = P_EIS_YEAR
         AND GROUP_CODE = P_GROUP_CODE;

     END;
  END IF;

END SP_AGREE_AMOUNT_CRU;

3/3. Servlet Source code ================
package ecosmos.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.sql.CallableStatement;

import ecosmos.util.DBHandler;

public class AgreeAmountHandler extends HttpServlet {
      


      private Connection con;
      
      private String target;
      
      public void init (ServletConfig config) throws ServletException{
            super.init(config);
      }
      
      public void service(HttpServletRequest req, HttpServletResponse res) throws ServletException , IOException {
      
            String year      = req.getParameter("slt_year");
            
            CallableStatement cs = null;

            try {

                  String sql = " { call SP_AGREE_AMOUNT_CRU(?,?,?) } ";

                  con = DBHandler.GetConnection();
                  
                  cs = con.prepareCall(sql);

                  java.util.Enumeration names ;
              names       = req.getParameterNames();
                               
                  String codeName = "";
              int value = 0;
/*
                  while( names.hasMoreElements() ) {
                        
                        codeName = (String) names.nextElement();

                        value = Integer.parseInt(req.getParameter(codeName));

                        cs.setString(1,year);
                        cs.setString(2,codeName);
                        cs.setInt(3,value);
                        cs.execute();
                  }
*/
                  cs.setString(1,"2004");
                  cs.setString(2,"A010");
                  cs.setInt(3,3333);
                  
                  cs.execute();
                  
                  target = "/agent/agreeAmountHandler.jsp?db_result=ok";

            }catch(Exception e) {
                  target = "/agent/agreeAmountHandler.jsp?db_result=failure";
                  e.printStackTrace();
            } finally{
                  try{ if(cs != null) cs.close(); } catch(Exception ex) {}
                  try{ if(con != null) con.close(); } catch(Exception ex) {}

            }

            RequestDispatcher rd;
            rd = req.getRequestDispatcher(target);
            rd.forward(req,res);

      }// End of service-method

}
Comment
Watch Question

Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
What is the problem?
Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
BTW, why don't you have doPost () and doGet () methods?
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
Oops!

>>You can iterate the parameters

should have said

You can't iterate the parameters
Principal Technologist
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>you will only be able to get the names of the parameters using that, not the values.

Well iamjhkang was actually getting the names to get the values, which *would* have worked but for the fact that the parameters to the SP are not named themselves, but ordered by number. It would have worked for named params to a Statement

Since there are only three params i don't think it's too much work to get them by name individually anyway ;-)
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>It would have worked for named params to a Statement

Hang on - that's rubbish - there isn't such a thing!
Mayank SPrincipal Technologist
CERTIFIED EXPERT

Commented:
>> iamjhkang was actually getting the names to get the values

Oh yeah, didn't notice req.getParameter ( codeName ) ;-)

Author

Commented:
I changed to "begin SP_AGREE_AMOUNT_CRU(?,?,?); end; "
and it was null pointer exception maybe. the parameter was sltYear not slt_year.

Thanks all of you.

Author

Commented:
and I didn't filter other paramternames.
CERTIFIED EXPERT
Top Expert 2016

Commented:
Null pointer exception on which line?
CERTIFIED EXPERT
Top Expert 2016

Commented:
8-)

Author

Commented:
slt_year is not there so year is null, not null pointer exception.

As I mentioned
I use call ~ instead of begin ~ , I got ORA-00900 error.

BTW, call ~ statements is supposed to work ok? because it is by ansi standard.

Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.