Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Oracle stored procedures (A97/SR2)

    Is it possible to use Oracle stored functions in pass through queries <img src=/S/question.gif border=0 alt=question width=15 height=15>,
    i.e. where you somehow push a parameter to the Oracle function
    I've tried the MSDN ADO example Q176086 of using a package/procedure to get data, but I was wondering if it was possible to have a "pass through" query which could take a parameter and then somehow use the stored procedure

    Also how do you create an Access query that is based on a stored procedure, rather than a view <img src=/S/question.gif border=0 alt=question width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Oracle stored procedures (A97/SR2)

    You can't create an Access query based on a stored procedure because Access can't *see* the stored procedures. Views can be linked as tables, so it's possible to create queries based on views. I'll leave the Oracle-specific questions for someone who works with Oracle.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Oracle stored procedures (A97/SR2)

    Blast <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    I'm trying to improve the speed of some of our databases.
    Two things I see as being slow :-
    (1) there's a few functions which basically do a text lookup for a value in a couple of joined tables. Currently the two tables are in Access as linked tables, call the VBA function with the parameters, in the function it creates the SQL SELECT to get the corresponding text. I think I can improve this by creating a stored procedure in Oracle, and then having the VBA call the stored procedure, at least this will reduce the network traffic <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    (2) there is also a couple of queries which do a lot of joins to produce the data for a single event, I'd hoped there might be someway to create this again a stored procedure, pass the event id and only get back the records that match - this was why I was questioning about the Access query based on the Oracle stored procedure, the parameter would be the event selected by the user, then the query would show the records. Currently there are a lot of linked tables
    Maybe I should just leave it as a slow system, but I feel it really could be made a lot better, i.e. quicker <img src=/S/help.gif border=0 alt=help width=23 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Using Oracle stored procedures (A97/SR2)

    We do what you are describing, especially under (2) with SQL Server, and it does give you something like an order of magnitude preformance boost, but I don't know if you can establish the same kind of connection to Oracle.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Oracle stored procedures (A97/SR2)

    Well after a day spent trying to learn both Oracle stored procedures, PL/SQL and how to use parameters in ADO, I'm moving forward, just not very far <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    I tried the MSDN VB example, Q174679, and got it to work, but I was struggling with the syntax for the passing of variables in and out of the ADO connection, e.g.
    QSQL = "{call SimplePackage.Proc1({resultset 3, o_id , ao_course, ao_dept})}"
    Set CPw1 = Cn.CreateQuery("", QSQL)

    What I can't find is the syntax for the call, another example
    QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, lname})}"
    has a ? in as a placeholder for the input, so do I use ?? for my procedure, or ?, ?,
    I was also struggling with debugging the stored procedures in SQL*Plus, just get a warning that something is wrong, but no idea what <img src=/S/frown.gif border=0 alt=frown width=15 height=15>. I wish there was some sort of editor, well I think there is, but we don't have it <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

Posting Permissions

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