topic Re: ContextLoad duplicate local variable key in Talend Studio

ContextLoad duplicate local variable key

Anonymous — Fri, 27 Dec 2019 13:22:02 GMT

Hello,

I start with Talend.

I follow O'Reilly guide in page 82.

I create a Pre-Job / Post-Job to audit the Execution.

I have a OracleSP calling an Oracle procedure : etl_demarrage returning a label and run_id

create or replace PROCEDURE etl_demarrage ( cle_lib out varchar2, actuel_run_id out integer)
IS

BEGIN
actuel_run_id := null;
cle_lib := 'run_id';

    SELECT MAX(run_id) 
        INTO actuel_run_id
    FROM ETLEXECUTION
    WHERE date_fin IS NULL;

IF actuel_run_id IS NULL THEN
    BEGIN
        INSERT INTO ETLEXECUTION (date_debut) VALUES (SYSDATE);
        SELECT MAX(run_id) 
            INTO actuel_run_id
        FROM ETLEXECUTION
        WHERE date_fin IS NULL;
    END;
END IF;

COMMIT;
END;

If I replace T_Contextload by a tLogRow, i get :

[statistics] connected
run_id|24
[statistics] disconnected

So Procedure return a right tuple and the insert in DB is OK.

The light params of contextLoad are :

But when i run, i got the error : Duplicate local variable key_tContextLoad_1

In the code i have twice declaration for key_tContextLoad_1 variable :

/**
 * [tContextLoad_1 main ] start
 */

currentComponent = "tContextLoad_1";

// row1
// row1

if (execStat) {
	runStat.updateStatOnConnection("row1" + iterateId, 1, 1);
}

//////////////////////////
String tmp_key_tContextLoad_1 = null;
String key_tContextLoad_1 = null;
...

/**
 * [tContextLoad_1 end ] start
 */

currentComponent = "tContextLoad_1";

java.util.Enumeration<?> enu_tContextLoad_1 = context.propertyNames();
while (enu_tContextLoad_1.hasMoreElements()) {
	String key_tContextLoad_1 = (String) enu_tContextLoad_1.nextElement();
	if (!assignList_tContextLoad_1.contains(key_tContextLoad_1)

Why i have twice declarations and how i can i fix this problem ?

I couldn't find a solution on the internet

Thanks for your help

Re: ContextLoad duplicate local variable key

tnewbie — Fri, 27 Dec 2019 15:38:45 GMT

If i am not sure if i am undermining the complexity, here are couple of my observations:

1) Do you really need a procedure here, because procedure will definitely turnout to be expensive in terms of performance in this context

2) Instead of a procedure you can have a view like:

SELECT 'run_id' AS v_key, NVL (MAX (RUN_ID), 0) + 1 AS v_value
FROM etlexecution where date_fin is null; --your query may differ, pls reconfirm

3) You don't need a commit in your procedure, when ever a procedure is executed externally, it does a implicit commit

4) Try to put a tmap after your stored procedure component to see the outports of it, i doubt if it is giving 2 ports with the same name

Re: ContextLoad duplicate local variable key

Anonymous — Fri, 27 Dec 2019 19:58:28 GMT

Thanks for your advise
I add a tMap between OracleSP and TContextLoad and the error is gone.
I don't understand but that's ok
I prefer keep my procedure to store my new run id