Call to an Oracle stored procedure from Access 97

Is it possible to call (execute) an Oracle stored procedure from Access 97?

If so, could you please explain the whole process..

neshoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CarmyCommented:
Use Pass-truogh Query (use ODBC).
Your Query should look like that:
"SELECT <myprocedure> FROM DUAL"
This will execute the function and return any value from your procedure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
neshoAuthor Commented:
Could you please give me an example code?
Thanks!
0
CarmyCommented:
This function will return the ORACLE System Date (in-built stored procedure)
As I have no idea what your function is doing I used this example.  Replace 'SYSDATE' with your function.

For more indepth details - search Microsoft knowledge base, use keywords Pass-trough and Oracle.

Function GetOracleDate () as String
   
    Dim MyDB As Database
    Dim MyQuery As QueryDef
    Dim MySet as RecordSet
    Dim SourceConnectString As String
    Dim strSQL As String

    Set MyDB = CurrentDB()
    'Create a temporary query in memory only
    Set MyQuery = MyDB.CreateQueryDef ("")

    strSQL = "SELECT SYSDATE FROM DUAL"
    SourceConnectString = "ODBC;"
    MyQuery.Connect = SourceConnectString
    MyQuery.ReturnsRecords = True
    MyQuery.SQL = strSQL
    'Open the Recordset to return the Date
    Set MySet = MyQuery.OpenRecordSet()
    'Return the system date
    GetOracleDate = MySet!SYSDATE

End function

0
neshoAuthor Commented:
Have you ever got it working with your own function instead of built-in and how about using parameters with function?

Anything special I should do in case I want to pass a parameter to my function?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.