from document:
if the Read mode property is set to PL/SQL and the Lookup type is set to Sparse, the connector matches by name the reference link columns with the parameters in the PL/SQL block. The connector maps the columns marked as key columns to PL/SQL input/output parameters and maps the remaining columns to the PL/SQL output parameters.
If the connector cannot match the names, the connector attempts to use the column order to associate link columns and parameters. Therefore, the connector associates the first column on the link with the first parameter, associates the second column on the link with the second parameter, and so on.
Note: For PL/SQL blocks, you must use the Oracle syntax. If you use DataStage syntax, the connector logs an error and the operation stops. If you use integer values for parameter names, you must specify the integers in increasing order; otherwise, the connector logs a Fatal message, and the operation stops.
===========================================
Yes, passing a paramter isnt quite the correct terms to use but i want to use variables i have in the lookup stage to be used in the pl/sql statements.
I have done hundreds of sparse lookups using just the normal sql but everything i try (and it was countless different attempts) to get a pl/sql to read my :MY_VARIABLE in my sql text, it was complete hopeless. I do not have parameters like calling a procedure, i just wanted to write some pl/sql code and use some variables i have in the lookup stage. Everything i did ended in:
Lookup_13: The connector could not find a column in the input schema to match parameter MY_VARIABLE. (CC_OraUtils::bindDBParamsByName, file CC_OraUtils.cpp, line 808)
Obviously i have MY_VARIABLE set as an input field going into the lookup. Has anyone been able to pass a parameter to a pl/sql statement and got it to work. The returning variables work fine. If i hardcode the MY_VARIABLE, then it works and gives me my results. I try to pass a variable to the routine, and it fails.
Here is the shortened code:
declare
v_rc CALL_PKG.c_realtime_tdp_ref_cursor ;
o_tran number;
begin
CALL_PKG.npp_main
(
:MY_VARIABLE ,
v_rc
) ;
loop
fetch v_rc into o_tran
exit when v_rc%NOTFOUND;
end loop;
close v_rc;
:RETURN_TRAN := o_tran;
end ;
Passing parameters to PL/SQL while in a Sparse Lookup
Moderators: chulett, rschirm, roy
