none
Excute Oracle PL/SQL procedure in Macro

    Domanda

  • Hi,

     

    I have written a VB script in Excel Macro for accessing the oracel data.But i can able to access the data from select statement.

    Then i was tried to execute one Pl/SQL stored procedure then it giving the  RUNTIME Error 404.

    My Code:

    Sub ConnectToOracle()
        'Create a reference to the OO4O dll
        Set objSession = CreateObject("OracleInProcServer.XOraSession")
        'Create a reference to my database
        Set objDataBase = objSession.OpenDatabase("prdncms", "ncmsbase/ncms2_prd", 0&)
        'Set objDataBase = objSession.OpenDatabase("ORCL", "scott/tiger", 0&)

    Dim strSQL As String

     

    'strSQL = "select sysdate from dual"
    strSQL = "BEGIN UserProfileMaintenance_pkg.prAddUser ('asd123',0,'dassad','adasdas345','Mr','M',1000,1000); END"
    'Set OraDynaSet =objDataBase.ExecuteSQL ("BEGIN UserProfileMaintenance_pkg.prAddUser ('asd123',0,'dassad','adasdas345','Mr','M',1000,1000); END")


    Set OraDynaSet = objDataBase.DBCreateDynaset(strSQL, 0&)

     

    Can any one help me to reslove this issue.

     

     

     

    Regards,

    Koti.

     

    • Spostato Reed KimbleMVP giovedì 2 dicembre 2010 20:54 vba (From:Visual Basic General)
    giovedì 2 dicembre 2010 12:23

Tutte le risposte

  • Dear Koti,

    First of all, i believe you are in the wrong forum section to ask this question. This is the Visual Basic forum, and this seems a VBA question to me.  Nevertheless, i did my best and wrote you an example in VBA :-). The example below shows you how to connect to oracle from a macro and  how the data on worksheet.

    Hope this helps,

     

    Cheers,

    John

    Sub ConnectToOracle()
      Dim cn
      Set cn = CreateObject("ADODB.Connection")
      cn.Open ("Provider=OraOLEDB.Oracle.1;Password=****;Persist Security Info=True;User ID=****;Data Source=****")
      'Update the connectionstring
      
      Dim SQL
      SQL = "SELECT * FROM MyTable;"
      
      Dim rs
      Set rs = CreateObject("ADODB.Recordset")
      Call rs.Open(SQL, cn)
      
      Dim xlWs As Worksheet
      Set xlWs = Sheets("Sheet1")
      
      Dim fldCount As Integer
      Dim recCount As Long
      Dim iCol As Integer
      Dim iRow As Integer
      
      ' Copy field names to the first row of the worksheet
      fldCount = rs.Fields.Count
      For iCol = 1 To fldCount
       xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
      Next
      
      'Paste the recordset on the worksheet
      xlWs.Cells(2, 1).CopyFromRecordset rs
    End Sub
    

     

    giovedì 2 dicembre 2010 14:18
  • Below are links to the VBA and Excel for Developers forums:

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    giovedì 2 dicembre 2010 19:45