How to trigger a stored package/procedure from SQLLDR control file

Question

I have a sqlldr process (which is triggerd from a windows batch) that loads around million records in to a staging table. Once a load is complete, need to trigger a SQL packages/procedures to transform the data. Can I invoke the packages from the same control file which I used to import the data? if so, what would be the syntax? if no, what is an alternate method to achieve this?

Answer 1

Short Answer: call the package/procedure that your need it with the sqlplus command in your batch file, after your sqlldr command, like next example.

sqlldr DB_User/DB_Password control=path_of_your_ctl_file.ctl log=path_to_save_log_file.log &

exit | sqlplus DB_User/DB_Password "begin package.procedure; end;" 

Long Answer: First, the sqlldr's objective is load a considerable data amount, in batch way. For this you may use sqlldr command to load and not transform data. Now, this doesn't mean that you can't transform data while sqlldr read and load data (Look the next example).

load data
 infile 'path_of_source_data_file.csv'
 badfile 'path_to_save_bad_file.bad'
 discardfile 'path_to_save_discard_file.dsc'
 append into table TABLE_NAME_TO_STORAGE_DATA
 fields terminated by ';' optionally enclosed by '"' --it depends of data file structure or extension
 TRAILING NULLCOLS
 ( 
   COL_1 "TRIM(:COL_1)", 
   COL_2 "COALESCE(:COL_2, 'UNDEFINED')",
   ID_COL "SEQ_ID_COL.NEXTVAL"
 )

Although you can normalize your data through sqlldr command, you have to think in your system's architecture. If the normalize phase is simple, like the above example, will good put together into a ctl file, otherwise you should abstract the normalize phase and call it, through sqlplus command when finish the data load phase.

Note: Also you should think about security, because you are pass, into the batch file, the database username and password. I recommend you that use Powershell files and Export-Clixml to cipher the sensible parameters (look this link to achive this).