execute a stored procedure on a database

[Steffen.Schmidt]

Hi,

I want to execute a stored procedure on an oracle database. Therefore I use the open SQL step configured as follows:
- option "statement requires parameters" is checked
- on the advanced tab: Command Type = Stored Procedure

After this step I use the Data Operation step with operation = Execute. Now, how can I set values for the parameters?

Has anyone an example where I can see how the needed steps have to be configured?

Thanks in advance
Best regards
Steffen

[Watermann]

hi Steffen,

perhaps this helps.
if I understand right this shows how to set a parametre with "statement".
hope this helps.

greets!

[AllenP]

Hi Steffen,

Your sequence should have the following steps:

1) Open Database
2) Open SQL Statement
3) Data Operation
4) Close SQL Statement
5) Close Database

I am using a very basic stored procedure that doesn't really do anything:
CREATE PROCEDURE MySP @myInput int AS SELECT * FROM myTable GO

For step 2, your SQL Statement field should be the name of the stored procedure. In my example, it would simply by "MySP". Make sure you have the "Statement has parameters" field is checked.

For step 3, select "Execute" as the Operation. Under the Column/Parameter field, you can insert all of your parameters. In my case, I just have one parameter. I can refer to it by index (1) or name ("myInput").

Hope this helps!

Allen P.
NI

[Steffen.Schmidt]

Hi Allen,

thanks for your answer! I've already been quite right, the only thing that was wrong was that I declared the SQL Statement in the Open SQL Statement step including the parameters e.g. "MySP(Param_1,Param_2)" - with only "MySP" everything works fine!

Regards
Steffen