Execute Stored Procedure from BODS and then start data extraction from SQL Table

[former_member189153]

Here, I will try to explain the simplest way to execute SQL Stored Procedure from BODS and then continuously start data extraction.

Below the step by step to execute stored procedure and then start data extraction.

1. Create batch job and add Script and Workflow. Script will be used to execute stored procedure.

2. Function sql() will be used to execute SQL stored procedure, call this function in Script as like below

Syntax: sql (datastore, sql_command)

DataStore: A string containing the name of the datastore where the tables involved in the SQL operation reside. This name is the name you specified when you created the datastore in Data Services. Include this string in single quotation marks.

SQL_Command: A string containing the text of the SQL command to execute. This string must be enclosed in single quotation marks ('). If the string contains quoted values, the internal quotation marks must be single quotation marks preceded by the escape character, backslash (\).

Example :

DataStore Name: SQL_FI_SRC

Stored Procedure Name: SP_Fill_FITable

If SQL Stored Procedure name as dynamic input, then

Create Global variable under Batch Job and use the same to pass SQL stored procedure name while executing the job, below the example

Global Variable:  $Exe_Statement

DataStore Name: SQL_FI_SRC

Create Global variable under Batch Job and use the same as stored procedure parameter and pass the value to variable while executing the job, below the example

Global Variable:  $CalMonth

DataStore Name: SQL_FI_SRC

Stored Procedure Name: SP_Fill_FITable

Required Parameter : @CalMonth , @Type

Assume @calmonth is a dynamic and @Type is default value then use the below script

Note: Since the script will be generating on specified datastore object, we cannot provide invalid datastore or variable asdatastore.

3. Above mentioned steps will execute the stored procedure and load the value into source table. Now the source table is ready for extraction.

4. Create dataflow to perform data extraction from source table and add under workflow as like below,

4. Execute the Batch Job, Script will execute the stored procedure and load the value to the source table after that dataflow  will perform the data extraction from source to target table .

Execution status will be available in "Job execution status Log".

[former_member186897]

how to pass the dynamic value to the datastore name? This is hard coded.

[KodandaPani_KV]

Hi,

Nice document.

thanks,

Phani.

[former_member189153]

You cannot provide dynamic  value to the datastore name.

[former_member186897]

oh, I c.

I think this a problem when code moves from one environment to another environment and database name changes.

[Former Member]

No it is not a problem.  A datastore is a logical object, the actual database name and other environmental settings can be changed using datastore configurations or during code promotion.

Michael

[former_member189153]

Yes I agree with you.

[former_member186897]

that's right. we may have different config for different environment.

[mohan_salla]

useful for me...thank you

[Former Member]

Hello,

Thank you for the useful write-up.

I have a question - if I've a custom data type as an input parameter type for an Oracle PL/SQL Stored Procedure -- is there anyway I can invoke that procedure from SAP Data Services (BODS)?

Please let me know

Thanks in advance for your kind response.

[chandrasekhar6]

Hi Ramesh,

What are field required in the source table.

Thanks Advance.

Thanks,

Chandra

[malinisomu]

avmrams

How do I know that the stored procedure result is going to be reside inside this table TBL_OPP_STAGE_CHI..(as per your example)

Do we get this information(table name) from the owner of the Stored procedure?

Please let me know.

Thanks,

Malini