Edi Yanto (何 萬 新)

Assigning a value to a collection element can cause various exceptions:

If the subscript is null or is not convertible to the right datatype, PL/SQL raises the predefined exception VALUE_ERROR. Usually, the subscript must be an integer. Associative arrays can also be declared to have VARCHAR2 subscripts.
If the subscript refers to an uninitialized element, PL/SQL [...]

If you want to exit from any enclosing loop, but not just the current loop, you can label the enclosing loop and use the label in an EXIT statement.
<<outer>>
FOR i IN 1..5 LOOP
. . . . .
FOR j IN 1..10 LOOP
. . . . .
EXIT outer WHEN . . . .
. . . . .
END [...]

NOCOPY is a hint to the compiler about how you would like the PL/SQL engine to work with the data structure being passed in as an OUT or IN OUT parameter. By default, OUT and IN OUT parameters are passed by value. The values of any IN OUT parameters are copied before the subprogram is [...]

In the below example, we can suppress duplicate values in the MGR column, display a blank line between manager number groups, and suppress the display of repeated values in the DEPTNO column using the BREAK command.
SQL> BREAK ON mgr SKIP 1 ON deptno
when the query executes, the report output is organized based on the specifications [...]

The below step are for optimizing performace of Oracle8i Database :

Tune architecture and design of the data model.
Tune Applications. It’s important to design efficient SQL statements. Efficient SQL Statement take less time to process. You must also consider the data access and manipulation operations in detail.
Tune memory structures, memory tuning involves tuning the System [...]

Some programs must build and process a variety of SQL statements, where the exact text of the statement is unknown until run time. Such statements probably change from execution to execution. They are called dynamic SQL statements. Formerly, to execute dynamic SQL statements, you had to use the supplied package DBMS_SQL.
Now, within PL/SQL, you can [...]