Did You Know? -- December 2001

By Hervé Deschamps, Oracle Corporation.


Tech Apps

CE: Cash Management
The errors below may be caused by absence of transaction codes in the setup of a CE bank account.
   The payment control total in the statement header must match the total payment amount on the statement lines.
   The receipt control total in the statement header must match the total receipt amount on the statement lines.
   The number of payment lines in the statement must match the payment line control total in the statement header.
   The number of receipt lines in the statement must match the receipt line control total in the statement header.
This is because procedure CE_Auto_bank_rec.statement does a nvl(decode(ctc.trx_type,'CREDIT',l.amount,..),0) so when there is no transaction code there is no transaction type and the amount is counted as 0, therefore control totals do not match.

Oracle 9i

There is a new init.ora called SPFILE. spfile is like only parameters can be changed with an alter system and taken into account without having to reboot the database. Init.ora still exists though so you can carry on working the same way.

Oracle9i also supports databases created with multiple block sizes. One standard block size and up to four other sizes. This is useful when the same database is used for a transactional system (usually smaller blocks) and a Decision support system (larger blocks).

The update of outlines is now supported (dbms_outln_edit). Outlines are created like this: create or replace outline... They are used to specify an execution plan for queries so that you can optimize application queries without touching the code.

Multiple column indexes can now be used in queries that test only the second column of the where clause. That is thanks to a clever index travel technique called 'Skip-scanning'.

SQL: new command called MERGE that does both insert and update.

SQL: Very cool concept of an external table that can be based on a flat file so that we can do a direct SELECT statement on flat files. The syntax is:
create table abc
organization external (type oracle_loader access parameters ...)

PL/SQL: We can write pipelined functions that do intermediate returns. That is useful for something like a filter function. The syntax is:
create function abc ... return ... pipelined ...

PL/SQL: (not new) if I have a function that has a REF CURSOR as a parameter I can it passing an SQL statement:
a:= my_function(cursor(select * from products));

SQL: We can do inserts in several tables with the same insert statement (insert all) with some conditions inside (when) and control the sequence of the inserting with the clause 'first'.

SQL: Using query re-write we can redirect complex queries that use multiple large table joins onto materialized views.

PL/SQL: In 9i DBMS_LOB is no longer necessary for operations line length, insrt and substr on CLOBs. All regular varchar2 functions now work on CLOBs.

SQL: We can now outer join on both side of a where clause.

PL/SQL: CASE is born! It's kind of like a decode only much more powerful and easier to read. Check the doc.

PL/SQL: 9i can compile it. Performance improvement factor from 2 to 10.

PL/SQL: Real object features have arrived: inheritance of both attributes and methods.

We can recover accidentally deleted data using the new FLASHBACK feature of 9i. Will that mean we'll have to add 'Undo' to our applications one of these days?

There is an XML Datatype now!

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve.deschamps@.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.