Using Oracle stored procedures (A97/SR2)

Topic

New Reply

Is it possible to use Oracle stored functions in pass through queries ,
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

Reply | Quote

Replies

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.

Reply | Quote

Blast
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
(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

Reply | Quote

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.

Reply | Quote

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
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 . I wish there was some sort of editor, well I think there is, but we don’t have it

Reply | Quote