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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

208 views

Analytics Software

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

Comments

Post Details