Can I call a global variable in the original variable in ODI?
I am trying to create an ODI procedure that will take the schema name, db procedure name and parameters from an oracle database metadata table. The parameter field contains the name of the global ODI 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 looks 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 this information from the original command to execute the procedures listed in the 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 are the reasons for this and how can I execute stored procedures in ODI by reading the names and parameters of the procedures from the metadata table?
source to share
If you select data from a table and use the result as code for further execution, usually you cannot use ODI variables. Because it is too late for ODI to recognize that it is a variable and replace it with a variable. This is the same for global and project variables.
If you can print "#"+variable_name
from? - or% -substitution, than it will work. But if @ -substitution prints the name of the variable, or if the variable appears as final code after retrieving values โโfrom the Source, then it's too late. In this case, it remains as normal text #VAR
.
In your specific case, you can do the following:
- Declare all type variables
#VAR_ETL_LOAD_DATE
in the package. I mean all the variables that could potentially appear in the metadata table. The Bacause script needs to know all the variables in advance. - Selecting and fetching records inside? -subscriptions using odiRef.getJDBCConnection ('SRC'). Collect all the results into a java variable as executable code.
For example, the source code might 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();
?>
The target code should be very simple
<?=result?>
At runtime, the target code will look 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 the ODI variables will be replaced with values โโsuccessfully.
source to share