Menu

Java.sql.SQLException: ORA-00933: la commande SQL ne se termine [Fermé]

Messages postés
2
Date d'inscription
mardi 24 mars 2015
Dernière intervention
25 mars 2015
- 24 mars 2015 à 12:33 - Dernière réponse :
Messages postés
15826
Date d'inscription
samedi 31 mai 2008
Statut
Modérateur
Dernière intervention
16 novembre 2018
- 24 mars 2015 à 19:07
Bonjour,
Lorsque j'exécute ce code, une exeption SQL est lancée ou je l'ai indiqué, et l'erreur est ORA-00933 - la commande SQL ne se termine pas correctement.
Voici mon code :
package com.mycompany.mavenproject1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date ;

public class test9 {
 private static final String HOST = "jdbc:oracle:thin:@192.168.72.129:1521/";
 private static final String DB = "remot";
 private static final String USER = "mohamed";
 private static final String PASSWORD = "mohamed";
 
        @SuppressWarnings("CallToPrintStackTrace")
 public static void main(String[] args) throws SQLException  {
  String sqlQuery = "SELECT et.ENTITY_TYPE_ID,"
                    + " et.ENTITY_CAT_ID,"
                    + " act.REF_ICCID,"
                    + " act.ACT_DATE,"
                    + "  distE.STATE,"
                    + "   distE.ENTITY_NAME as DIST_ENTITY_NAME,"
                    + "   pdvE.ENTITY_ID    as PDV_ENTITY_NAME,"
                    + "  c.CO_ID,"
                    + " c.CONSP_AGREGATION,"
                    + "  c.CO_DEACTIV_DATE,"
                    + "   c.SIM_BOX_STATUS,"
                    + "    c.CO_HOMOLOG_STATUS,"
                    + "   prcp.PROD_COM_PID,"
                    + "  pkcp.PACK_COM_PID,"
                    + "  prcp.PRODUCT_ID,"
                    + "   pkcp.PACK_ID,"
                    + "   ocp.OFF_COM_PID,"
                    + "   ocp.OFFER_ID,"
                    + "    ocp.COM_CONSO_VALUE,"
                    + "   ocp.COM_OFFER_MAX_THRESHOLD,"
                    + "   ocp.COM_OFFER_MAX_VALUE,"
                    + "   ocp.COM_OFFER_MIN_THRESHOLD,"
                    + "   ocp.COM_OFFER_MIN_VALUE,"
                    + "    ocp.OFF_COM_PCOMMISSION_PERIOD,"
                    + "   prcp.PROD_COM_PCOM,"
                    + "    prcp.PROD_COM_PPAIE,"
                    + "    pkcp.PACK_COM_PCOM,"
                    + "    pkcp.PACK_COM_PPAIE,"
                    + "   comp.COMPUTE_ID,"
                    + "   comp.COMPUTE_VERSION,"
                    + "   comp.COMMISSION_SIM,"
                    + "   comp.COMMISSION_SHARING,"
                    + "   comp.commission_fix as COMMISSION_ADD,"
                    + "   comp.COMMISSION_FIX_PDV as COMMISSION_ADD_PDV,"
                    + "    comp.COMMISSION_REPAYEMENT_SIM,"
                    + "    compV1.COMPUTE_ID      as V1_COMPUTE_ID,"
                    + "  compV1.COMMISSION_SIM as V1_COMMISSION_SIM,"
                    + "  compV1.COMMISSION_SHARING as V1_COMMISSION_SHARING,"
                    + "   compV1.Commission_Fix          as V1_COMMISSION_ADD,"
                    + "   compV1.COMMISSION_FIX_PDV as V1_COMMISSION_ADD_PDV,"
                    + "    compV1.COMMISSION_REPAYEMENT_SIM as V1_COMMISSION_REPAYEMENT_SIM,"
                    + "   nvl(cons.CONSUMPTION_M,0) as CONSO_M,"
                    + "         nvl(cons.CONSUMPTION_M1,0) as CONSO_M1,"
                    + "         nvl(cons.CONSUMPTION_M2,0) as CONSO_M2,"
                    + "       nvl(cons.CONSUMPTION_M3,0) as CONSO_M3"
                    + "  FROM ACTIVATION                 act,"
                    + "       STOCK_SIM                  sm,"
                    + "      DISTRIBUTION_SYSTEM_ENTITY distE,"
                    + "     DISTRIBUTION_SYSTEM_ENTITY pdvE,"
                    + "     PACKAGE_COM_PARAMETER      pkcp,"
                    + "     PRODUCT_COM_PARAMETER      prcp,"
                    + "     entity_type                et,"
                    + "    OFFER_COM_PARAMETER        ocp,"
                    + "  contract                   c,"
                    + "   COMPUTE                    comp,"
                    + "   COMPUTE                    compV1,v"
                    + "     CONSUPTION_AGGRAGATION     cons"
                    + " WHERE act.REF_ICCID = sm.REF_ICCID"
                    + "   and c.CO_ICCID = act.REF_ICCID"
                    + "  and c.CO_ID = cons.CONTRACT_CO_ID(+)"
                    + "   and sm.DISTRIBUTOR_DISTRIBUTOR_ID = distE.ENTITY_ID"
                    + "   and sm.POINT_OF_SALE_POINT_OF_SALE_ID = pdvE.ENTITY_ID"
                    + "  and ((et.ENTITY_TYPE_ID = distE.ENTITY_TYPE_ID and et.entity_cat_id = 0 and distE.State=1) or"
                    + "      (et.ENTITY_TYPE_ID = pdvE.ENTITY_TYPE_ID and et.entity_cat_id = 1  and pdvE.State=1))"
                    + "   and act.ACT_DATE >= to_date(:startDate, 'dd-mm-yyyy')"
                    + "   and act.ACT_DATE < to_date(:stopDate, 'dd-mm-yyyy') + 1"
                    + "   and et.entity_cat_id = :entityCat"
                    + "   and act.OFFER_OFFER_ID = ocp.OFFER_ID"
                    + "  and et.ENTITY_TYPE_ID = ocp.ENTITY_TYPE_ID"
                    + "   and act.PRODUCT = prcp.PRODUCT_ID"
                    + "   and et.ENTITY_TYPE_ID = prcp.ENTITY_TYPE_ID"
                    + "    and act.PACK_PACK_ID = pkcp.PACK_ID"
                    + "   and et.ENTITY_TYPE_ID = pkcp.ENTITY_TYPE_ID"
                    + "   and act.REF_ICCID = comp.ACTIVATION_ID(+)"
                    + "   and comp.COMPUTE_VERSION(+) = 0"
                    + "  and act.REF_ICCID = compV1.ACTIVATION_ID(+)"
                    + "  and compV1.COMPUTE_VERSION(+) = 1"
                    + "   and NOT EXISTS"
                    + " (select ocpp.version"
                    + "          from OFFER_COM_PARAMETER ocpp"
                    + "         where ocpp.OFFER_ID = ocp.OFFER_ID"
                    + "          and act.ACT_DATE >= ocpp.OFF_COM_PAPPLICATION_DATE"
                    + "          and ocp.ENTITY_TYPE_ID = ocpp.ENTITY_TYPE_ID"
                    + "          and ocp.version < ocpp.version)"
                    + "  and NOT EXISTS (select pkcomp.version"
                    + "        from PACKAGE_COM_PARAMETER pkcomp"
                    + "        where pkcomp.PACK_ID = pkcp.PACK_ID"
                    + "          and act.ACT_DATE >= pkcomp.PACK_COM_PDATE_APP"
                    + "         and pkcomp.ENTITY_TYPE_ID = pkcp.ENTITY_TYPE_ID"
                    + "        AND pkcp.VERSION < pkcomp.version)"
                    + "  and NOT EXISTS (select prcomp.version"
                    + "     from PRODUCT_COM_PARAMETER prcomp"
                    + "   where prcomp.PRODUCT_ID = prcp.PRODUCT_ID"
                    + "     and act.ACT_DATE >= prcomp.PROD_COM_PAPP_DATE"
                    + "     and prcomp.ENTITY_TYPE_ID = prcp.ENTITY_TYPE_ID "
                    + "      and prcp.VERSION < prcomp.version) ";
  
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection conn = DriverManager.getConnection(HOST+DB,USER,PASSWORD);
   
   PreparedStatement preparedSelect = conn.prepareStatement(sqlQuery);
   preparedSelect.setString(1, "22-03-2012");
                        preparedSelect.setString(2, "03-07-2013");
                        preparedSelect.setInt(3, 0);
   ResultSet rs = preparedSelect.executeQuery();
   
   while (rs.next()) {
    //int STATE_ID = rs.getInt(1);
    //String STATE_NAME = rs.getString(1);
    //System.out.printf("%s\n",STATE_NAME);
   }
   
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }

 }

}


l'erreur :
java.sql.SQLSyntaxErrorException: ORA-00933: la commande SQL ne se termine pas correctement
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
 at com.mycompany.mavenproject1.test9.main(test9.java:123)


aidez moi svp :(
Afficher la suite 

1 réponse

Messages postés
15826
Date d'inscription
samedi 31 mai 2008
Statut
Modérateur
Dernière intervention
16 novembre 2018
- Modifié par KX le 24/03/2015 à 19:12
-1
Merci
Bonjour,

Pas besoin d'aller très loin pour voir que ta requête est monstrueuse.
Tu ne connais pas les jointures ? Ça te serait utile ici...

Sinon c'est quoi cette syntaxe avec les
(+)
? À mon avis c'est faux.

+ "   and act.REF_ICCID = comp.ACTIVATION_ID(+)"
+ "   and comp.COMPUTE_VERSION(+) = 0"
+ "  and act.REF_ICCID = compV1.ACTIVATION_ID(+)"
+ "  and compV1.COMPUTE_VERSION(+) = 1"

Remarque : tu as fait .setString(1, 2 et 3, et pourtant tu utilises des champs nommés dans ta requête :startDate, :stopDate, :entityCat
Il faudrait être cohérent et utiliser soit l'un, soit l'autre, pas un mélange.

PS. Bizarre d'ailleurs que tu fasses un setString alors que c'est clairement un setDate qui devrait venir ici...La confiance n'exclut pas le contrôle