cancel
Showing results for 
Search instead for 
Did you mean: 

Calling Oracle sequences from within BO Data Services Designer

Former Member
0 Kudos

Hi - I just started using BO Data Services and I'm trying to reference an Oracle sequence within a Data Flow using a SQL transformation. The transformation is returning the next number from the Oracle sequence correctly, but it only makes the call once instead of calling the SQL transformation for every record being processed.

The Data Flow has the output from a Match transformation and the output from the SQL transformation going into a Query transformation. This Query transformation then writes to the Oracle target table. All of the records written to the table are getting the same Id returned from the SQL transformation instead of the Id incrementing for each record inserted.

It's probably something simple, but what am I missing here? Do I need to create a procedure in Oracle which gets the next sequence number and then somehow (I don't know how) call that procedure from within the Data Flow?

Any suggestions would be greatly appreciated.

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

andre_borracha
Explorer
0 Kudos

You can use functions option from mapping in a query transformation, then sql function of Database Functions. Sql has two parameters, first your wished Oracle Database (Who has the sequence), second the Query, here you can use an expression like "SELECT yourowner.yoursequence.nextvalue from dual", this query will return the next value from your sequence for each line from source, then you need only map this transformation int a column in target.

werner_daehn
Active Contributor
0 Kudos

This is very ineffective, though. sql() function does a connect/execute/disconnect for every single invocation, so if the input has 1m rows, these are 1m connect/execute/disconnect. Oracle procedure/function is better as long as it is pushed into the select (statement as part of select).

Former Member
0 Kudos

the SQL that you use in SQL Transform will be executed only once, SQL transform is nothing but a reader (source), if you want to do some tranformation for each row coming out of a source or transform, then in your case you can create a oracle function which return the sequence, import this function in the Datastore and call this function in the mapping like you would call any other DI function

Former Member
0 Kudos

Thanks Manoj! - I'll give that a shot.