CODEWITHSUNDEEP
oracle-databasems-accessstored-procedures
GRANT CURTIS
GRANT CURTIS

Reputation: 15

Get results of (Oracle) stored procedure output parameters in Access

I've never worked with Stored Procedures, so I'm completely lost. One of our DB admin created a SP in Oracle that accepts three variables, and has one OUT variable. How do I see the results of the procedure once I call it?

DECLARE
v_srch_sw         VARCHAR2(1);
v_srch_crit       VARCHAR2(20);
v_ssn_last_four   VARCHAR2(4);
v_ivr_data        VARCHAR2(4000);

BEGIN
    DBMS_OUTPUT.enable(buffer_size => NULL);
    v_srch_sw       := 'A';
    v_srch_crit     := '1234567890';
    v_ssn_last_four := '1234';


schemaname.sp_name_010(v_srch_sw
                        ,v_srch_crit
                        ,v_ssn_last_four
                        ,v_ivr_data);

DBMS_OUTPUT.PUT_LINE(v_ivr_data);                           
END;
/

Note: The DBMS_OUTPUT.PUT_LINE returns the results as a message, but not an actual result. For example, when I put this code in MS Access '07 as a pass-through query, no results are returned. How do I get this to return the results just like normal SELECT query?

Upvotes: 1

Views: 1757

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

How do I get this to return the results just like normal SELECT query?

First, be aware that the code in the question is not the stored procedure, it is PL/SQL code to invoke the stored procedure and display the results in Oracle.

Second, AFAIK there is no way to invoke that stored procedure from Access "like a normal SELECT query" because the stored procedure does not return a result set. Instead, it returns its value as an output parameter.

However, you can create a Function in VBA that calls the stored procedure and returns the result. The following example works with SQL Server, but the code for Oracle should be very similar. (For example the parameter names might be slightly different, possibly omitting the @ prefix.)

Option Compare Database
Option Explicit

Public Function Call_sp_name_010(v_srch_sw As String, v_srch_crit As String, v_ssn_last_four As String) As Variant
    Const linkedTableName = "dbo_Clients"  ' an existing ODBC linked table

    Dim cdb As DAO.Database
    Set cdb = CurrentDb

    Dim con As New ADODB.Connection
    con.Open "DSN=" & Mid(cdb.TableDefs(linkedTableName).Connect, 5)  ' omit "ODBC;" prefix

    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "schemaname.sp_name_010"
    cmd.Parameters.Append cmd.CreateParameter("@v_srch_sw", adVarChar, adParamInput, 1)
    cmd.Parameters.Append cmd.CreateParameter("@v_srch_crit", adVarChar, adParamInput, 20)
    cmd.Parameters.Append cmd.CreateParameter("@v_ssn_last_four", adVarChar, adParamInput, 4)
    cmd.Parameters.Append cmd.CreateParameter("@v_ivr_data", adVarChar, adParamOutput, 4000)

    cmd.Parameters("@v_srch_sw").Value = v_srch_sw
    cmd.Parameters("@v_srch_crit").Value = v_srch_crit
    cmd.Parameters("@v_ssn_last_four").Value = v_ssn_last_four
    cmd.Execute

    ' function returns output parameter value
    Call_sp_name_010 = cmd.Parameters("@v_ivr_data").Value

    Set cmd = Nothing
    con.Close
    Set con = Nothing
    Set cmd = Nothing
End Function

Now, if necessary, you can use that user-defined function in an Access query.

Upvotes: 1

Related Questions