|
Newsletters
(Maj, 2000)
- Oracle 8 database programming with collections
- Why Not Be Old-Fashioned?
Oracle 8 database programming with collections Tilbage
123
If you have never tried to construct your PL/SQL code with Oracle 8 collec-tions you may ask, why bother? But col-lections might be an option for you for two reasons:
1. Collections are handy structures we have missed in PL/SQL
2. Collections can be used to boost per-formance, e.g. in comprehensive data retrievals.
ABSTRACT
Lots of us work with a nice Oracle 8.x database. Nevertheless few have taken a closer look at the set of new features - not to mention the object approach.
Though, even if we do not use Oracle 8 with an OO approach, we can gain ad-vantages of some of its useful features. This article will focus on collections in Oracle 8 used in a relational approach (which still is current for most of us).
I will not go into details of any kind, and if you plan to make use of collections please see the documentation. This arti-cle gives an introduction to the topic, some possible areas of use, and a small example.
COLLECTIONS ARE HANDY STRUC-TURES WE HAVE MISSED IN PL/SQL
Collections are list structures, which can be compared to single dimension arrays and tables. Many PL/SQL pro-grammers have for years missed collec-tions like some of you who may know them from other professional environ-ments (e.g. VisualBasic, SmallTalk etc.). Former versions of the Oracle database gave us the PL/SQL (index-by) tables. But they are not collections.
As from Oracle version 8 we have col-lections, there being two different types with different memory and performance properties: VARRAY, which is a kind of memory array, and TABLE (nested table), which is a kind of table. But from the view of PL/SQL they both work the same way.
You can create collections of lots of types, e.g. you decide what type each element in your collections should have. In my example (later in this article) I re-strict myself to my own simple type.
A collection comes with a set of methods. E.g. a method to add elements to a col-lection.
But best of all, while you use your col-lections in PL/SQL, you can use them in SQL too.
All together this means that we can construct more complex structures in PL/SQL than we were ever able to be-fore.
COLLECTIONS CAN BE USED TO BOOST PERFORMANCE, E.G. IN COMPREHENSIVE DATA RETRIEVALS
A typical example of comprehensive data retrieval is from the world of Oracle Reports. We start with a little background. The user wants output. But the data is stored in database structures that must be calculated before it is shown to the user. The output cannot be generated with one or more SQL SELECT state-ments alone. (If you haven’t faced this you’re lucky.) Therefore the necessary calculations must be performed in one or more PL/SQL database program units.
The Report solution would often be to create a database package that can per-form the calculations and place the result in a temporary table. The calculation starts by a call from the Reports BEFORE-REPORT trigger. The report SQL is then based on the temporary ta-ble. (The SELECT uses one or more functions of the database package which returns values used in the WHERE clause (so we avoid fetching other users data). The AFTER-REPORT trigger of the report cleans up).
All hard work of the report is done in the database package. This will typically imply a lot of loops, cursors, inserts, up-dates and deletes in temporary tables. And it is exactly here we would want to optimise our advanced and time-consuming PL/SQL code. And if we can write it more readable it will be welcome too.
By the use of collections we can con-struct a more simple report and under many circumstances improve perform-ance dramatically.
The collection approach is much more concise than the traditionally Oracle Re-ports example above. If we create the necessary collection type, we can create a function (if convenient in a package) that we can call from the SELECT in our report. (Like in the example below.)
If we use th
Why Not Be Old-Fashioned? Tilbage
123
At the present time, where everybody is talking about Internet, e-Business and other new technologies, and where the technology supersedes the person-to-person contact between human beings, I would like to declare myself as being old-fashioned!
I think that the most important thing in life - also in the business world - will re-main to be the direct contact between human beings, in spite of all technological innovations. That is also the case in our consultant business in the areas IBM, Oracle and SAP. In addition to the “right” resources to the project, the trust be-tween ourselves and the customer is very important for the success of the project.
WHAT ARE THE “RIGHT” EXTERNAL RESOURCES?
It is not only a matter of pure technical qualifications. It is also important, that it ”ticks” between the persons in the team. Independence and initiative on one side, sensitivity and co-operation on the other side, are characteristics which should go hand in hand.
To make sure this happens, I find it very important to know the customers personally. You get a very clear under-standing of the needs of the customer (also those, which in a specific case may not be spoken out). On the other side, the customer knows exactly what he can ex-pect from us and our consultants.
The inter-human approach also means that we only focus on the solution of ur-gent problems and bottlenecks in the project - as quickly and uncomplicated as possible. We do not try to sell you addi-tional software modules and the like or try to make your problem bigger in any other way. We are independent of IBM, Oracle, SAP and other vendors. We are only de-pendent on the trust of our customers.
In my opinion, the best guaranty of success is to follow the American saying: “People buy from people”.
That might sound old-fashioned in our Internet and e-Business orientated age. But...
... why not be old-fashioned?
Return to Newsletters
|
