Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    74

    Unanswered: Can I run an Oracle PL/SQL Procedure from Access ?

    I would like to execute a PL/SQL procedure on an Oracle Database that I have an ODBC connection to.

    I can't think of how to do it but I am sure many people have wanted so maybe someone could tell me if it can be done and how ?

    Thanks very much.

    B.Dimple
    Junior DBA

  2. #2
    Join Date
    Aug 2003
    Posts
    12

    PL/SQL in Access

    I have done it in Access 2000 and I assume it would work in newer versions but here's the basic steps.

    1. create a blank query called whatever you want to name it.
    2. Go to the SQL view and change the query type to a Pass-Through Query.
    3. Call your package/stored proc just as if you were in SQLPlus
    4. Off of the View menu, Click on the Query Properties. One of the properties is an ODBC Connection String.

    You're done. What the pass through does is allow you to type in PL/SQL or T-SQL or any non-access SQL and execute. Access assumes you know what you are doing. You can just call
    "Exec package_name.stored_proc param1, param2" and as long as your proc returns a cursor you will get it.

    hth

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    You can also use ADO's connection object:

    Code:
    Dim cn As ADODB.Connection
    
    Set cn = New ADODB.Connection
    
    cn.ActiveConnection = "Your Connection String Here"
    
    cn.Open
    
    'If you are not returning records
    cn.Execute "Exec StoredProcedureName", adExecuteNoRecords
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •