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

Question

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?

Answer 1

If you select data from a table and use the result as a code for further execution, normally you cannot use ODI variables there. Because it too late for ODI to recognse that it is a variable and substitute it by a variable. This is the same for both global and project variables.

If you could print "#"+variable_name from ?- or %-substitution than it will work. But if @-substitution prints variable name or if variable appears as a final code after fetching values from Source it is too late. In this cases it remains as a plain text #VAR.

In your particular case you can do the following:

1. Declare all variables like #VAR_ETL_LOAD_DATE in a package. I mean all variables that could potentially appear in the metadata table. Bacause scenario should know all variables in advance.
2. Select and fetch records within ?-substitution using odiRef.getJDBCConnection('SRC'). Collect all results into a java-variable in the form of executable code.

E.g., source code could look like this:

select 1 from dual;
<? 
import java.sql.*;
String crlf = System.getProperty("line.separator");
String result = "begin"+crlf+"null;"+crlf;
PreparedStatement stmt = odiRef.getJDBCConnection("SRC").prepareStatement("select schema||'.'||proc||'('||param||')' from metatable");
ResultSet rs = stmt.executeQuery();
while(rs.next()){
  result += "insert into ak_tst2 values('"+rs.getString(1).replaceAll("'",'"'.toString())+"');"+crlf;
  result += "commit;"+crlf;
  result += rs.getString(1)+";"+crlf;
}
result += "end;";
rs.close();
stmt.close();
?>

Target code should be very simple

<?=result?>

At runtime target code will appear like this

begin
null;
insert into ak_tst2 values('qwe.asd("param_using_#var")');
commit;
qwe.asd('param_using_#var');
insert into ak_tst2 values('qwe2.asd2("param2_using_#var")');
commit;
qwe2.asd2('param2_using_#var');
insert into ak_tst2 values('qwe3.asd3("param3_using_#var")');
commit;
qwe3.asd3('param3_using_#var');
end;

And ODI variables will be successfully substituted by values.