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 :(
A voir également:
Ora-00933: la commande sql ne se termine pas correctement
La commande sql ne se termine pas correctement - Meilleures réponses
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
