CODEWITHSUNDEEP
javaoracle-databaseservletssqlplus
user1954492
user1954492

Reputation: 495

ORA-00905: missing keyword error in executing from servlet

I'm using the following code to execute the query. It shows error as ORA-00905: missing keyword. When I'm executing this in sql*plus it shows correct result. By using servlet it doesn't. Anyone help me to solve the problem please

public class PickOfferNo extends HttpServlet {


protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
         //TODO output your page here
        out.println("<html>");
        out.println("<head>");
        out.println("<title>Servlet PickOfferNo</title>");  
        out.println("</head>");
        out.println("<body>");
        out.println("<h1>Servlet PickOfferNo at " + request.getContextPath () + "</h1>");
        out.println("</body>");
        out.println("</html>");

    } finally {            
        out.close();
    }
}

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //processRequest(request, response);

    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try
    {
        out.println("<html>");
        out.println("<head>");
        out.println("<title>Servlet PickOfferNo</title>");
        out.println("</head>");
        out.println("<body>");
        out.println("<h1>Servlet PickOfferNo at " + request.getContextPath () + "</h1>");
        doPost(request,response);
        out.println("</body>");
        out.println("</html>");
    }
    finally
    {
       out.close(); 
    }
}


@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //processRequest(request, response);
    String ofrn=null;
    String cd=null;
    String no=null;


    cd=request.getParameter("sec_code");
    PrintWriter out=response.getWriter();
    try
    {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        java.sql.Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.200:1521:pdsm", "test", "test");
             Statement stmt=conn.createStatement();
                 conn.setAutoCommit(true);
                 ResultSet rs=stmt.executeQuery("select to_char(nvl(max(substr(offerno,4,4)),0)+1) into '"+no+"' from offer1 where div_sec='"+cd+"'");
                 ResultSet rs1=stmt.executeQuery("select lpad(ltrim(rtrim("+no+")),4,'0') from dual");
                 if(rs1.next())
                     no=rs1.getString(1);
                 ofrn=cd+no;

                 //obj.put("offrn", ofrn);
                 out.println(ofrn);
    }
    catch(Exception e)
    {
        out.println(e.getMessage());
    }
}

/** 
 * Returns a short description of the servlet.
 * @return a String containing servlet description
 */
@Override
public String getServletInfo() {
    return "Short description";
}// </editor-fold>
}

Thanks. Please help

Upvotes: 1

Views: 548

Answers (1)

maksim_khokhlov
maksim_khokhlov

Reputation: 804

The SELECT INTO expression will not work here. The SELECT INTO expression can only be used in the PL/SQL runtime, e.g. in a stored procedure. You should use the regular SELECT syntax and take results as usual from the ResultSet object. The following code should work:

             ResultSet rs=stmt.executeQuery("select to_char(nvl(max(substr(offerno,4,4)),0)+1) from offer1 where div_sec='"+cd+"'");
             if(rs.next())
                 no=rs.getString(1);
             ResultSet rs1=stmt.executeQuery("select lpad(ltrim(rtrim("+no+")),4,'0') from dual");
             if(rs1.next())
                 no=rs1.getString(1);

Upvotes: 2

Related Questions