Oracle Bulk Loader - Order of execution

mjf_unt — Wed, 17 Mar 2021 16:16:25 GMT

I've got a general question on the order of execution with regards to the generated PROC SQL in an Oracle Bulk Loader transformation. While I have examined the generated code in question, I am no expert at the base code and would prefer a better explanation if possible.

Here's what I'm trying to understand: I'm looking to use the bulk load transformation with the "Drop and recreated indexes" option enabled. Furthermore, I'm considering adding a snippet of code to the Pre-code portion of the transformation.

What will be the order in which these items execute? Will the DROP INDEX execute first, prior to any other actions in the code? Or does the Pre-code section take precedent?

Re: Oracle Bulk Loader - Order of execution

clwatson — Mon, 22 Mar 2021 13:47:33 GMT

@mjf_unt Any code provided and enabled in the Precode and Postcode will come before (Precode) and after (Postcode) the "core" code of the transformation. The "core" code of the transformation in this case is the Oracle Load Technique settings. This rule is true for all DI Studio transforms. In your scenario:

That code then gets generated before the core transform code, which for the Oracle Bulk Table Loader is the settings on the Load Technique tab.

Here's some bits of the excerpts from my pictured example:

/*==========================================================================*
* Step: Oracle Bulk Table Loader A5RMRMLZ.$0000079 *
* Transform: Oracle Bulk Table Loader *
* Description: *
* *
* Source Table: CARS - SASHELP.CARS A5RMRMLZ.BK00002M *
* Target Table: CARS - oracle.CARS A5RMRMLZ.BK000002 *
*==========================================================================*/
.....
%let SYSLAST = %nrquote(SASHELP."CARS"n);

%let ETLS_SYSLAST = &SYSLAST;
/*---- Start of Pre-Process Code ----*/
data _null_;
put 'This is precode for transformation';
run;
/*---- End of Pre-Process Code ----*/

%rcSet(&syserr);
%rcSet(&sqlrc);

%let SYSLAST = &ETLS_SYSLAST;

%global etls_tableExist;
...
%let etls_lastTable = &SYSLAST;

/*---- Define load data macro ----*/

%macro etls_loader;

I've bolded the pre-code area and then the start of the "core" Oracle Bulk Loader Transformation.

So to answer your question, the pre-code specified on the Precode and Postcode tab will take precedence over any "core" functionality of DI transformations.

topic Re: Oracle Bulk Loader - Order of execution in SAS Data Management

Oracle Bulk Loader - Order of execution

Re: Oracle Bulk Loader - Order of execution