1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Call a stored procedure in oracle from excel

Discussion in 'General' started by Aidanor89, Mar 5, 2014.

  1. Aidanor89

    Aidanor89 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi all, I want to call a stored procedure in oracle sql from excel.

    Is this possible?

    If so can someone please step it out in basic form, I would really appreciate this, I have checked everywhere and cannot find proper instructions in plain English.
    I am new to macros, etc also so if this is the best way please advise or provide sample.

    Thanks,
    Aidan
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. Aidanor89

    Aidanor89 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    I am using the below code now in excel to create the macro but am getting an error saying "user defined type not found";

    any ideas??

    Thanks again.

    Sub Ora_Connection()

    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim query As String
    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset

    '---- Replace below highlighted names with the corresponding values

    strCon = "Driver={Microsoft ODBC for Oracle}; " & _
    "CONNECTSTRING=(DESCRIPTION=" & _
    "(ADDRESS=(PROTOCOL=TCP)" & _
    "(HOST=Your Host Name)(PORT=Port Number))" & _
    "(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;"

    '--- Open the above connection string.

    con.Open (strCon)

    '--- Now connection is open and you can use queries to execute them.
    '--- It will be open till you close the connection

    End Sub
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    "user defined type not found" :

    Code (SQL):


    Sub Ora_Connection()  
     
    '  Dim con As ADODB.Connection  
    '
    Dim rs AS ADODB.Recordset  
    Dim query AS String  
    SET con = CreateObject("ADODB.Connection")

    SET rs = CreateObject("ADODB.Recordset")
     
    '---- Replace below highlighted names with the corresponding values  
     
    strCon = "Driver={Microsoft ODBC for Oracle}; " & _  
    "CONNECTSTRING=(DESCRIPTION=" & _  
    "(ADDRESS=(PROTOCOL=TCP)" & _  
    "(HOST=Your Host Name)(PORT=Port Number))" & _  
    "(CONNECT_DATA=(SID=SID of your Database))); uid=User ID; pwd=Password;"  
     
    '
    ---  Open   the above connection string.  
     
    con.OPEN (strCon)  
     

    '---  Now connection is open and you can use queries to execute them.  
    '
    ---  It will be open till you close the connection  
     
    rs.close

    con.close

    END Sub                    
     
     
    Aidanor89 likes this.