I will need some help in redesigning an application I have written in Acess to work with data in an Oracle database.
The app is working fine but we start to have some performance issues due to the fact that I am doing a big part of the business logic in Access (vs on the oracle server).
In practice I am aggregating / sorting / processing data in access based on some very raw views existing in Oracle.
I am now planning to "move back" this processing server side, as it should most likely yield some significant performances advantages. So I guess it is time for PL/SQL 101…
I guess what I am looking at is stored procedures ? Mostly what I need is performed via tacked SELECT queries and some "inline" access variables. I'm pretty sure this can be done in PL/SQL too. Is it possible to pass parameters to views for Access to Oracle ? To have "global variables" (ie persisting across calls).
Here is a typical query I am doing at the moment
SELECT 1 AS NO_UNIQUE_REGROUPEMENT, fnRefDate()+1 AS DATE_TRANSACTION, 2009 AS Annee, 9999 AS TYPE_TRANSACTION, 0 AS NO_COMPTE, "Initial" AS NATURE_TRANSACTION, ("Report from " & Format(fnRefDate()+1,"dd\.mm\.yyyy")) AS LIBELLE_COURT, "" AS LIBELLE_LONG, "###" AS NO_POLICE_LIEE, "###" AS NO_POLICE_LIEE_CLEAN, "###" AS BRANCHE, Sum(qTransactions.MONT_TRANS) AS MONT_TRANS, qTransactions.NO_ADRESSE, qTransactions.NOM_TIERS, "###" AS ASSUREUR, 2 AS DATE_TRANSACTION_NUM
GROUP BY qTransactions.NO_ADRESSE, qtransactions.NOM_TIERS;
Can something like this be recreated easily ? Note the use of custom functions (fnRefDate()) and "on the fly" calculations (Sum).
Any feedback / suggestion most welcome