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

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

beginnull;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.