Welcome to All Things Warehouse Builder

  • ETL
    May 25, 2011

OWB - Invoking SQL*Loader from Process Flow

David Allan

Quick post on how to execute SQL*Loader from within a process flow. You can use the SQL*Plus activity and invoke the sqlldr command via the SQL*Plus HOST command passing the specific parameters you want.

Below you see variables being used for the user and password, and the control file (to make more dynamic the entire script could have been a variable that was constructed);


The process flow must be configured to defined the deployed location which is the credential that is used for SQLPlus (that’s why I can use the variables (Target.User etc..). Below I just used SCOTT_LOCAL location for the example.


Also by default SQLPlus and all other external activities are disabled so you will have to enable this in the Runtime.properties file and stop/start the runtime service. Just now the file has an entry

property.RuntimePlatform.0.NativeExecution.SQLPlus.security_constraint = DISABLED

this needs changed to..

property.RuntimePlatform.0.NativeExecution.SQLPlus.security_constraint = NATIVE_JAVA

in order to execute the SQLPlus activity.

Join the discussion

Comments ( 2 )
  • guest Thursday, January 12, 2012

    Thanks for that post.

    I've allready such a solution. How can i recognize the success of the execution of the called sql script.

    The requiremt is to set the whole process to failure if the sqlplus script has any errors.

    Thanks for your help.

  • David Thursday, January 12, 2012


    You can define a process flow variable and bind the RESULT_CODE parameter of SQLPlus activity to this variable. Then you can define the transition from SQLPlus activity with custom (complex) condition (like PF_VAR_RESULT_CODE=2 etc.).

    So you will return the error code with EXIT command in your SQLPLus script;

    variable RESULT_CODE number;




    :RESULT_CODE := ....;



    exit :RESULT_CODE



Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.