Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ODI-1217: Session ONP_Audit (348079) fails with return code 7000

3651009Mar 13 2020 — edited Mar 13 2020

Hi,

My customer use oracle data integrator 11g (ODI) to build and manage data integration processes.

When the customer execute the procedure ONP_Audit on his server,

The customer have the following error message :

In French :

ODI-1217 Echec de la session ONP_Audit (348079) avec le code retour 7000.

In English :

ODI-1217: Session ONP_Audit (348079) fails with return code 7000.

org.apache.bsf.BSFException: exception from Jython:

Traceback (most recent call last):

File "<string>", line 37, in <module>

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)

com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)

at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)

at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

java.sql.SQLException: java.sql.SQLException: Column count doesn't match value

count at row 1

at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)

com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)

com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)

com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)

oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)

oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)

oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)

com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)

com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)

com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)

at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)

at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)

oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)

oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)

oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)

oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)

oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)

oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)

at java.lang.Thread.run(Thread.java:662)

Caused by: Traceback (most recent call last):

File "<string>", line 37, in <module>

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)

com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)

at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)

at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

java.sql.SQLException: java.sql.SQLException: Column count doesn't match value

count at row 1

at org.python.core.PyException.fillInStackTrace(PyException.java:70)

at java.lang.Throwable.<init>(Throwable.java:181)

at java.lang.Exception.<init>(Exception.java:29)

at java.lang.RuntimeException.<init>(RuntimeException.java:32)

at org.python.core.PyException.<init>(PyException.java:46)

at org.python.core.PyException.<init>(PyException.java:43)

at org.python.core.Py.JavaError(Py.java:455)

at org.python.core.Py.JavaError(Py.java:448)

org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:177)

at org.python.core.PyObject.__call__(PyObject.java:355)

at org.python.core.PyMethod.__call__(PyMethod.java:215)

at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)

at org.python.core.PyMethod.__call__(PyMethod.java:206)

at org.python.core.PyObject.__call__(PyObject.java:397)

at org.python.core.PyObject.__call__(PyObject.java:401)

at org.python.pycode._pyx115.f$0(<string>:44)

at org.python.pycode._pyx115.call_function(<string>)

at org.python.core.PyTableCode.call(PyTableCode.java:165)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1204)

at org.python.core.Py.exec(Py.java:1248)

at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)

at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)

... 19 more

Caused by: java.sql.SQLException: Column count doesn't match value count at row

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)

com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)

at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)

at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)

at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)

... 33 more

The Execution blocks at a step called LANCE_AUDIT_PROC_MYSQL :

import string

import java.sql as sql

import java.lang as lang

import java.sql.SQLException

import java.lang.Exception

import re

import sys

isDoublon = '0'

#codeerreurdoublon = 400

sourceConnection = odiRef.getJDBCConnection("SRC")

tbl_lst_Stmt = sourceConnection.createStatement()

UPDATE_Stmt = sourceConnection.createStatement()

chemin_log = chemin_chargement + 'logAudit.txt'

output_write=open(chemin_log,'a')

print >> output_write , " ****** LANCEMENT PROCEDURES AUDIT : ******** "

sql_insert_audit = "insert into ONP_AUDIT (TYPE_AUDIT, DATE_AUDIT ,INFO, VAL1, VAL2, VAL3) VALUES ('AUDIT_REGLE_DOUBLON_DEBUT', SYSDATE(), 'Debut lancement regle doublon', '' , '', '')"

UPDATE_Stmt.executeUpdate(sql_insert_audit)

TBL_CUR = tbl_lst_Stmt.executeQuery("select ERR_NUMBER, VALEUR from ONP_SYS_REGLE_PARAM where ERR_NUMBER = 400 and VALEUR='1' ")

if (TBL_CUR.next()):

isDoublon = TBL\_CUR.getString("VALEUR")

if isDoublon == '1':

try :

    print >> output\_write , "LANCEMENT PROCEDURE DOUBLON : "

    UPDATE\_Stmt.executeUpdate("UPDATE ONP\_LISTE\_CHAMPS set CLE=1 where TNAME = 'ONP\_ECHELN\_CHEVRN' and CNAME ='A\_CAR\_NUECGR';")

   **tbl\_lst\_Stmt.execute("CALL ONP\_PROC\_doublon()")**

    UPDATE\_Stmt.executeUpdate("UPDATE ONP\_LISTE\_CHAMPS set CLE=0 where TNAME = 'ONP\_ECHELN\_CHEVRN' and CNAME ='A\_CAR\_NUECGR';")

except java.sql.SQLException, e :

    erreur =  e.toString()

    erreur = erreur.replace("'"," ")

    UPDATE\_Stmt.executeUpdate("insert into ONP\_AUDIT (TYPE\_AUDIT, DATE\_AUDIT ,INFO, VAL1, VAL2, VAL3) VALUES ('AUDIT\_REGLE\_DOUBLON', SYSDATE(), 'ERREUR SQL ', '" + erreur + "', '');")

    print >> output\_write , "ERROR SQL call doublon: " + e.toString()

    sys.exit(0)

sql_insert_audit = "insert into ONP_AUDIT (TYPE_AUDIT, DATE_AUDIT ,INFO, VAL1, VAL2, VAL3) VALUES ('AUDIT_REGLE_DOUBLON_FIN', SYSDATE(), 'Fin lancement regle doublon', '' , '', '')"

UPDATE_Stmt.executeUpdate(sql_insert_audit)

output_write.close()

This step call a stored procedure with the name ONP_PROC_doublon :

MariaDB [DEV_BDC500]> SHOW CREATE PROCEDURE ONP_PROC_doublon;

+------------------+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| ONP_PROC_doublon | NO_AUTO_VALUE_ON_ZERO | CREATE DEFINER=`ONPTRG`@`localhost` PROCEDURE `ONP_PROC_doublon`()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE var\_tname VARCHAR(50);

DECLARE var\_MatCle VARCHAR(1);

DECLARE var\_cnameCLE VARCHAR(50);

DECLARE var\_cname VARCHAR(50);

DECLARE var\_cle\_str LONGTEXT;

DECLARE var\_select\_group\_by VARCHAR(500);

      DECLARE var\_where VARCHAR(500);

DECLARE var\_insert\_into\_ERR VARCHAR(500);

DECLARE var\_ERR\_AUDIT\_ID INT DEFAULT 0;

DECLARE SEP VARCHAR(1);

      DECLARE curseur1 CURSOR FOR SELECT tname , IFNULL((select '1' from ONP\_LISTE\_CHAMPS C where C.tname = B.tname and C.cname='P\_CIV\_MATCLE' LIMIT 1), '0') as EXISTE\_MATCLE, IFNULL((select C.cname from ONP\_LISTE\_CHAMPS C where C.tname = B.tname and C.CLE='1' and COLNO='1' LIMIT 1), '') as CNAME\_CLE  FROM ONP\_SYS\_LISTE\_TABLE B WHERE type\_tbl='TR' and tname in (select tname from ONP\_SYS\_TABLE\_AUDIT where AUDIT='1');

DECLARE curseur2 CURSOR FOR SELECT cname FROM ONP\_LISTE\_CHAMPS WHERE tname = var\_tname and CLE = '1' order by COLNO;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

select ERR\_AUDIT\_ID into var\_ERR\_AUDIT\_ID from ONP\_SYS\_PARAM where CODE\_PARAM='PARAM\_AUDIT';

OPEN curseur1;

LOOP1: loop

    SET var\_cle\_str = '';

    SET var\_insert\_into\_ERR = '';

                SET var\_where = '';

    SET SEP = '';

    FETCH curseur1 INTO var\_tname, var\_MatCle, var\_cnameCLE;

    if done = 1 then

        close curseur1;

        leave LOOP1;

    end if;

    open curseur2;

    LOOP2: loop

        FETCH curseur2 INTO var\_cname;

        if done = 1 then

            set done := 0;

            close curseur2;

            leave LOOP2;

        ELSE

            SET var\_cle\_str = CONCAT(var\_cle\_str, SEP, var\_cname);

            SET SEP = ',';

        end if;

    end loop LOOP2;

    IF var\_cle\_str \<> '' then

                    IF var\_MatCle = '1' then

                            SET var\_where = ' where P\_CIV\_MATCLE IN (select P.P\_CIV\_MATCLE from ONP\_SYS\_POPULATION P where P.AUDIT = \\'1\\') ';

                    END IF;

                    SET var\_insert\_into\_ERR = CONCAT('insert into ERR\_', var\_tname, '(', var\_cle\_str, ', ERR\_DESCR,ERR\_NUMBER,ERR\_DATE,ERR\_CHAMP,ERR\_LIB\_CHAMP,ERR\_VAL,ERR\_AUDIT\_ID) ');

        SET var\_select\_group\_by = CONCAT('select ', var\_cle\_str, ",'DOUBLON' AS ERR\_DESCR, 400 AS ERR\_NUMBER , SYSDATE() AS ERR\_DATE, '" , var\_cnameCLE , "' AS ERR\_CHAMP, '' AS ERR\_LIB\_CHAMP, '' AS ERR\_VAL," , var\_ERR\_AUDIT\_ID, ' AS ERR\_AUDIT\_ID from ',  var\_tname,  var\_where, ' group by ', var\_cle\_str, ' having count(\*)>1;');

        set [@sql](https://forums.oracle.com/ords/apexds/user/sql)\_cmd2= CONCAT (var\_insert\_into\_ERR, var\_select\_group\_by);

        PREPARE stmt2 FROM [@sql](https://forums.oracle.com/ords/apexds/user/sql)\_cmd2;

        EXECUTE stmt2;

        DEALLOCATE PREPARE stmt2;

    END IF;

end loop LOOP1;

END | utf8 | utf8_general_ci | utf8_general_ci |

1 row in set (0.00 sec)

We would appreciate your help.

sincerely yours

Added on Mar 13 2020

#business-intelligence-applications, #data-integrator, #system-management-and-integration

1 comment

314 views