CODEWITHSUNDEEP
javasqloracle-databasejpa
sunleo
sunleo

Reputation: 10943

org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

Query is getting executed well but problm with JPA .Please help me to find the wrongness. Query works well if I execute seperately in Oracle SQL client but with application gives error.

Am getting exception in this line as Eclipse says 

return genDODetails(pSess.executeQuery(rq));

Query :

SELECT DOD.ID AS DODID,ORDD.ID AS ORDDID,DOD.SHIPQTY AS DOQTY,DOD.BUYERCODE AS BUYERCODE,DOD.BUYERPARTNUM AS BUYERPARTNUM,
        DOD.BUYERPARTDESC AS BUYERPARTDESC,DOD.LINENUM AS DOLINENUM,DOD.LINEREVNUM AS LINEREVNUM,
        DOD.LINEINDICATIOR AS LINEINDICATOR,DOD.ORDLINENUM AS ORDLINENUM,ORDD.SHIPQTY AS SHIPPEDQTY,
        DOD.RSPREMARK1 AS SUPREMARK,DOD.ORDNUM AS PONUM
         FROM RDT_DELIVERYORDERDETAIL DOD,RDT_ORDERDETAIL ORDD ,RDT_ORDER ORDM
        WHERE  ORDD.LATEST =1
        AND ORDM.LATEST =1 
        AND ORDM.ID = ORDD.ORDID
        AND ORDD.RESPSTR1 ='EP'
        AND ORDD.LINENUM = DOD.ORDLINENUM
        AND ORDM.DOCNUM = DOD.ORDNUM
        AND DOD.LATEST =1
        AND CONTROLLERID =(SELECT ID FROM RDT_ORGANIZATION WHERE OUCODE ='yes' AND PARENTID IS NULL)
        AND DOD.DOID = 72
        ORDER BY DODID DESC;

JPA Execution Code:

public List<DODetail> getDODetails(Map<String,Object> hparams) throws Exception
    {
        String sqlQuery= pSess.getSQLString4NamedQuery("DO_VIEW_DETAIL");   
        String doid = hparams.get("DOID")!=null ?(String)hparams.get("DOID"):"";
        Hashtable<String,Object> dbparams=new Hashtable();          
        dbparams.put(":DOID",doid);     
        sqlQuery = (String)pSess.getParamQuery(sqlQuery, dbparams);
        ReportQuery rq=new ReportQuery();
        rq.setReferenceClass(DODetail.class);


        rq.addAttribute("DODID");
        rq.addAttribute("ORDDID");
        rq.addAttribute("DOQTY");
        rq.addAttribute("BUYERCODE");
        rq.addAttribute("BUYERPARTNUM");
        rq.addAttribute("BUYERPARTDESC");
        rq.addAttribute("DOLINENUM");
        rq.addAttribute("LINEREVNUM");
        rq.addAttribute("LINEINDICATOR");
        rq.addAttribute("ORDLINENUM");
        rq.addAttribute("SHIPPEDQTY");
        rq.addAttribute("SUPREMARK");
        rq.addAttribute("PONUM");

        rq.setSQLString(sqlQuery);

        return genDODetails(pSess.executeQuery(rq));
    }   
    private List<DODetail> genDODetails(Object obj)throws Exception
     {
         if(obj==null ) return null;

         List newList = (List)obj;
         Iterator it = newList.iterator();
         List<DODetail> doDetails = new ArrayList<DODetail>();

        while(it.hasNext())
         {
            ReportQueryResult rs=(ReportQueryResult)it.next();
            DODetail order=new DODetail();  
            order.setId(rs.get("DODID")!=null?((BigDecimal)rs.get("DODID")).longValue():new Long(0));
            order.setRefid(rs.get("ORDDID")!=null?((BigDecimal)rs.get("ORDDID")).longValue():new Long(0));
            order.setShipqty(rs.get("DOQTY")!=null?(BigDecimal)rs.get("DOQTY"):new BigDecimal(0));
            order.setBuyercode(rs.get("BUYERCODE")!=null?(String)rs.get("BUYERCODE"):"");
            order.setBuyerpartnum(rs.get("BUYERPARTNUM")!=null?(String)rs.get("BUYERPARTNUM"):"");
            order.setCategory(rs.get("BUYERPARTDESC")!=null?(String)rs.get("BUYERPARTDESC"):"");
            order.setLinenum(rs.get("DOLINENUM")!=null?((BigDecimal)rs.get("DOLINENUM")).longValue():new Long(0));
            order.setLinerevnum(rs.get("LINEREVNUM")!=null?(String)rs.get("LINEREVNUM"):"");
            order.setLineindicator(rs.get("LINEINDICATOR")!=null?(String)rs.get("LINEINDICATOR"):"");
            order.setOrdlinenum(rs.get("ORDLINENUM")!=null?((BigDecimal)rs.get("ORDLINENUM")).toPlainString():"");
            order.setAssignqty(rs.get("SHIPPEDQTY")!=null?(BigDecimal)rs.get("SHIPPEDQTY"):new BigDecimal(0));
            order.setRspremark1(rs.get("SUPREMARK")!=null?(String)rs.get("SUPREMARK"):"");
            order.setOrdnum(rs.get("PONUM")!=null?(String)rs.get("PONUM"):"");          
            doDetails.add(order);
         }
         return doDetails;
     }

Upvotes: 0

Views: 2568

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Make sure, you didnt have a trailing semicolon in your query string, before execution. Because, it is not needed, and it is one of the possible reasons for this error.

It is not required , when you send a query to the database using a OCI driver. Only when manually executing in SQL*Plus or something needs that as pushing the sql to the database engine for execution.

Upvotes: 1

Related Questions