How to call a oracle PLSQL procedure with OUT parameter from macro? RRS feed

  • Question

  • Hi,

    I am trying to call a PLSQL stored procedure with in an excel macro code. But not getting any clue how to do it?


    procedure test_excel( p_1 IN number, p_2 out varchar) as
    if p_1 = 1 then
      p_2:= 'piyush';
    end if;

    Macro code:

    Sub callProc()
            Dim rs As Object, com As Object
            Dim dbname As String
            Dim username As String
            Dim password As String
            Dim param as String
            dbname = Cells(1, 1)
            username = Cells(2, 1)
            password = Cells(3, 1)
            Set cn = CreateObject("ADODB.Connection")
      cn.Open ("Provider=OraOLEDB.Oracle.1;Password=" + password + ";Persist Security Info=True;User ID=" + username + ";Data Source=" + dbname)
            Dim SQL
            Set com = CreateObject("ADODB.Command")
            Set com.ActiveConnection = cn
            com.CommandText = "test_excel(1," + param + ")"
            cells(6,1) = param
    End Sub

    It gives me ORA-00900 Invalid SQL statement error. But If I call a procedure with only IN parameters in similar way. It works for me. Please advice..!!

    • Edited by Peeush Thursday, October 3, 2013 8:57 AM
    • Moved by Carl Cai Friday, October 4, 2013 5:23 AM not relate
    Thursday, October 3, 2013 7:56 AM


  • Hi,

    this is a VB.Net forum (Visual Studio Languages forums, .NET Framework forums > Visual Basic )

    I suggest to ask in an Excel VBA or Oracle forum. Excel would be here.


    • Proposed as answer by Cor Ligthert Thursday, October 3, 2013 3:53 PM
    • Marked as answer by Just Karl Tuesday, October 15, 2013 4:13 PM
    Thursday, October 3, 2013 3:47 PM