Oracle stored procedure as source in BODS

[Former Member]

Hello Experts,

I am working on project where all the source tables are in store procedure (pl/sql scripts) and I will have to exec the procedure to get the data. Is it possible to populate BODS table in such way? Can you provide me with how-to information, please.

Tha data flow should be like this:

stored procedure -> query transform -> target table

[Former Member]

Hi Team,

I am facing the problem while calling the oracle procedure in SAP BODS SQL Transformation. My requirement is I need to get the table records from oracle using procedure and need to dump in to destination table which is in SQL. What I did is, I have created a procedure with return type Refcursor in Oracle Database as i need to get bulk record from table. Then I have imported the procedure in my BODS and called the procedure name in My BODS SQL Transformation. and rest i have done with query transform and destination table. I am getting two error.

1. while importing the procedure in BODS, I am getting warning "return type of output parameter is unknown. It may create ERROR". as my output parameter return type is ref cursor (i need to get bulk record from table).

2. while executing the job, i am getting error "Zero Iteration Count. SQL posted query<'SQL('data source name', 'My procedure Name')>.>.

Please help me, I am trying this from last 3 days.

Thanks,

Sundaram

[former_member187605]

You can keep trying forever . As said before:

"

That approach works when the pl/sql procedure returns scalar values only. OP wants to the pl/sql to return a table, which is not possible in a query transform.

"

[Former Member]

Thanks Venken. So is there any other solution to do the same requirement in BODS. actually this is my requirement, we have a procedure and need to call the procedure in BODS and get all the records from table and dump it in my destination table and my oracle procedure is source in this case not table or view.

Please suggest on this.

[former_member187605]

Not as you state it.

The only possibility in DS:

1/. call the stored proc form a DS script, e.g. using the sql built-in function

2/. use the result table of the procedure as input table for a dataflow

[Former Member]

Thanks Venken.

[Colm]

You can use an sql transform

exec your_proc

You may depending on your proc need to SET FMTONLY ON

[Former Member]

And what about this ? From this disscusion I understood that this is possible to use stored procedure as source for BODS. But from this post this is not clear for me how to recieve data table as output parameter of stored procedure.

[Former Member]

I think that I shoul call pl/sql script with output parameter as table. Is it possible?

[Former Member]

Check this

Calling a stored procedure - Enterprise Information Management - SCN Wiki

[Former Member]

Correct. Embed the call to the stored procedure as a function call. Stored procedures show up as functions under a data store in Data Services. If you have input parameters, then they will show up in the GUI driven screen when you import just like in the link Arun directed.

[former_member187605]

That approach works when the pl/sql procedure returns scalar values only. OP wants to the pl/sql to return a table, which is not possible in a query transform.

[Former Member]

Right. Missed the table part... thanks.

If a table is truly needed then the table would need to be landed or created in Oracle via the stored procedure. Could then bind to the output table via a Data Store table (if the out put is always the same table definition and name) or a SQL Transform with a parameter/DS variable for the FROM clause. If the columns are dynamic that is a bigger problem as the following DS code will have to expect a structure in a consistent format.

Dynamic reads are tough in Data Services.

[former_member205887]

You can execute stored procedure as below, this will execute your stored procedure

http://scn.sap.com/thread/3540312

[Former Member]

But I can't understand how can I set output parameter for procedure as table?

[former_member187605]

Am I right in assuming the pl/sql procedures create and/or populate databases tables?

If so, you can call them from a DS script, using the built-in SQL function:

     sql('DS_ORA',begin  stored_proc(<parameters>); end;');

There's no need to include them in a SQL or Query transform.

Of course, you can then use the result tables as source for further data flow processing.

[Former Member]

No, pl/sql procedures select data from database tables, after this data must be processed in data services job and in the end SAP BW must be populated.

[former_member205887]

Hi Olga,

If you have only select statements, copy them to SQL Transform map the fields to Query Transform then to Target Table.

[Former Member]

And can I copy pl/sql statements to SQL Transform? Or only SQL?

[former_member205887]

Only SQL Select statement can be copied....

[Former Member]

In my case I have pl/sql scripts created by client's developers and I must use them to retrieve data from ora database to BODS...