OWB - Invoking SQL*Loader from Process Flow

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);

image

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.

image

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.

Comments:

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.

Posted by guest on January 12, 2012 at 01:06 AM PST #

Hi

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;
declare
...
begin
:RESULT_CODE := ....;
end;
/
exit :RESULT_CODE

Cheers
David

Posted by David on January 12, 2012 at 02:22 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today