Can I call a global variable in source variable in ODI?

Viewed 2514

I am trying to build a ODI procedure, which will take schema name, db procedure name and parameters from a oracle database metadata table. The parameter field contains a name of a ODI global variable.The source command is like this

SELECT SCHEMA_NAME VAR_SCHEMA, PROCEDURE_NAME VAR_PROCEDURE, PARAMETER_NAME 
VAR_PARAMETER FROM SCHEMA-NAME.TABLE_NAME

the output of the source command is like this:

VAR_SCHEMA_NAME  VAR_TABLE_NAME   VAR_PARAMETER
ABC              PROC_LIST        TO_DATE('#VAR_ETL_LOAD_DATE','DD/MM/RRRR') 

Here, #VAR_ETL_LOAD_DATE is a global variable in ODI.

In the target command of the procedure, I want to use these information from source command to execute procedures listed in metadata table. I wrote a command like this:

DECLARE

VVC_SQL_STMT LONG;

BEGIN

VVC_SQL_STMT := 'BEGIN
            #VAR_SCHEMA_NAME.#VAR_PROCEDURE_NAME(#VAR_PARAMETER);
            END;';                                                     

INSERT INTO AK_TST2 VALUES(VVC_SQL_STMT,SYSDATE);

COMMIT;

EXECUTE IMMEDIATE (VVC_SQL_STMT);

END;

This code gives the following error in ODI:

ODI-1228: Task PROC_SP_HANDLER (Procedure) fails on the target ORACLE 
connection OCDM_SYS.
Caused By: java.sql.SQLException: ORA-06550: line 8, column 61:
PLS-00103: Encountered the symbol "#" when expecting one of the following:

* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset

What is the reasons for this and how can I execute stored procedures in ODI by reading procedure names and parameters from a metadata table?

1 Answers
Related