Calling an oracle stored procedure with no parameters ADODB

Question

I have not found anywhere on how to call an Oracle stored procedure using ADODB from Excel where the stored procedure has no input parameters.

Fake example to illustrate lack of input parameters:

CREATE OR REPLACE PROCEDURE Get_Data
(OUTPUT OUT SYS_REFCURSOR) IS

******************************************************************************/
BEGIN

OPEN OUTPUT FOR 

SELECT DISTINCT
       B.ITEM_ID
       B.ITEM_DESC
    FROM ITEM_FILE B
WHERE ITEM_ID IS NOT NULL
 ORDER BY B.ITEM_ID
; 

END Get_Data;
/

Oh, and the stored procedure is required because we don't want to give users SQL access to create whatever SQL they want.

Is this even possible? And if so, what kind of code would it take to call it?

Thanks, Dan

That's no problem. Do you have an example with IN parameter? btw, you may prefer to write a FUNCTION rather than a PROCEDURE. — Wernfried Domscheit
– Wernfried Domscheit, Commented May 30, 2018 at 17:11

Wernfried Domscheit · Accepted Answer · 2018-05-31 08:05:30Z

0

Transform your procedure to a function:

CREATE OR REPLACE FUNCTION Get_Data RETURN SYS_REFCURSOR IS
res SYS_REFCURSOR;

BEGIN

OPEN OUTPUT FOR 
SELECT DISTINCT
       B.ITEM_ID
       B.ITEM_DESC
    FROM ITEM_FILE B
WHERE ITEM_ID IS NOT NULL
ORDER BY B.ITEM_ID;  
RETURN res;

END Get_Data;
/

Call in VBA would be as this:

cmd.CommandText = "{CALL Get_Data()}"
cmd.Properties("PLSQLRSet") = True
Set Rst1 = cmd.Execute 
cmd.Properties("PLSQLRSet") = False

Note, by default an OUT parameters are used like this:

cmd.Parameters.Append cmd.CreateParameter("OUTPUT", adVarChar, adParamOutput, 100)

However, for RefCursor parameters you must not declare them with cmd.Parameters.Append.

Have a look at Provider for OLE DB Developer's Guide, it contains several examples. Chapter Stored Procedures and Functions Returning Rowsets should be the most relevant for you.

answered May 31, 2018 at 8:05

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dan Over a year ago

Thanks. I figured it out. I had forgotten the prefix/alias in my stored procedure name when calling it Prefix.stored_proc_name and once the prefix was added it worked fine. The VBA code I use to call the stored procedure, coincidentally is almost exactly as the code in your post. Thank you so much for your reply. Hopefully, this code will help another. Also, thanks for the links.

Collectives™ on Stack Overflow

Calling an oracle stored procedure with no parameters ADODB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related