LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: raylee

how do you execute a stored procedure in oracle through brain? Thanks.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Raylee,

Good Day.
To execute stored procedures in ORACLE, you would use the DB Execute node under the Database category of the Core library. This node allows you to run sql code specified with either the sqlSelect or the sqlNonSelect commands. See your CORE library help for node details.

To then execute the statement, you would use the sqlNonSelect brainscript command (since you aren't querying the database).

For example:
I start a new graph and place a DB Execute node. I want to create a simple do-nothing procedure, so I put in the following code in the "Script" edit box
####
sqlNonSelect("CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
NULL;
END;
")
####

I then want to execute this procedure. So I drop another DB Execute node and enter the following code in it's script box
###
sqlNonSelect("BEGIN skeleton; END;")
###

This will execute the the procedure I just created. Of course if the procedure already existed, I wouldn't have needed to use the first node, just the second. But I hope this example points you in the right direction.

Regards
Timon Koufopoulos
MDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: raylee

Thanks! It worked for a procedure with no parameters.

How to you pass parameters to the procedure?

Thanks!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Raylee,

Here is one method you could use.

I modified my original example to include a basic parameter:
####
sqlNonSelect("CREATE OR REPLACE PROCEDURE skeleton (a NUMBER)
IS
BEGIN
NULL;
END;
")
###

One way you could setup a sql execution to pass parameters it by using Node parameters, and then textually substituting.

For example, (again from the previous):

I open the node editor for the second DB Execute node, and I declare a node parameter called NUMBER. I give it the value 1.

I then modify the sql statement to the following:
###
sqlNonSelect("BEGIN skeleton ({{^NUMBER^}}); END;")
###

Note that I have enclosed the {{^^}} with Parenthesis as is required.

This method allows you to construct your sql statements, and then just alter their input via the parameters instead of having to modify the actual statements themselves. You could do this at the Node level, or at the Graph level. OR, you could do both, and have the option of setting some SQL parameters at the Graph level, and overriding the ones you want to at the Node level. ( An example of that would be to declare NUMBER at both graph and node parameters. The node will run the Graph level param unless you give it a value for itself).

Does this help?
Regards
Timon Koufopoulos
MDA Support.