How to run stored procedure from DAC

Hello Again

It's been a while.. This time I want to share something I just learned over the past few months, it is how to execute a stored procedure directly in DAC without having anything to do with Informatica (as discussed in the previous post).

Let's say we have a store procedure or package 'mck_client_wip_load' in stance 'mck_build_revwip_pkg', or mck_build_revwip_pkg.mck_client_wip_load. In order to execute this stored procedure in directly DAC, we need to create 'action', in this case 'task action'. After all, we will execute DAC task to do the execution.

In the action task window, create a new task with a name. In my case I just use 'MCK_BUILD_REVWIP_PKG_MCK_CLIENT_WIP_LOAD'

Now we have to define the value of this action as show:

Now the most important part is to enter the commend for executing the Store Procedure in Action. The place to enter the commend is shown:

The code that I entered here is:

BEGIN

mck_build_revwip_pkg.mck_client_wip_load(@DAC_$$PARAMETER1),(@DAC_$$PARAMETER2),(@DAC_$$PARAMETER3);

END;

Notice that there are 3 parameters in this code. That is because this specific store procedure requires 3 input values at run time. If the store procedure doesn't need any input values, then it would simply have been:

BEGIN

mck_build_revwip_pkg.mck_client_wip_load;

END;

If you look at the left pane of the above image, you will notice that '@DAC_$$PARAMETER1' is available for you to click on under SQL Statement pane.

In order for this to happen, we need to create these parameters in task. So let's go there:

In the task, we enter the task action name 'MCK_BUILD_REVWIP_PKG_MCK_CLIENT_WIP_LOAD' in both commend for incremental load and full load field.

Then in 'Execution Type' field we select 'SQL file'.

The values for folder name and Primary Source and Primary Target aren't important in this case

as we are not dealing with informatica at all. The value for Primary Source should be the connection parameter for which database the store procedure is located.

Now, in the parameter tab under the Task, we create 3 parameters:

Each of the 3 parameters also have a hard coded value in this case.

This is pretty much it and the rest is simply adding this task in the subject area and execution plan for running it.

When running this execution plan, it is as if executing the following statement directly in the DB with the parameter values:

BEGIN

mck_build_revwip_pkg.mck_client_wip_load(@DAC_$$PARAMETER1),(@DAC_$$PARAMETER2),(@DAC_$$PARAMETER3);

END;

Until next time!

Thanks