PL/SQL Array example

declare

    type table_list is table of varchar(128) index by binary_integer;
    tabs table_list;
    i number;

begin

    tabs(1) := 'aaa';
    tabs(2) := 'bbb';
    tabs(3) := 'ccc';
    
    for i in 1..3
    loop
        dbms_output.put_line(tabs(i));
    end loop;

end;

Simple Select and Loop in PL/SQL

Simple example of using PL/SQL to select and loop through a cursor.
Note: Results are output to the output window.

CREATE OR REPLACE PROCEDURE test(p_table_name in user_tables.table_name%type) IS

    TYPE ref_cursor IS REF CURSOR;
    l_cursor        ref_cursor;

    v_query         varchar2(5000);
    v_name          varchar2(64);

BEGIN
    v_query := 'select T120F005_COST_CATEGORY_CODE from ' || p_table_name;
    OPEN l_cursor FOR v_query;
    LOOP
        FETCH l_cursor INTO v_name;
        EXIT WHEN l_cursor%NOTFOUND;
        dbms_output.put_line('Category Code: ' || v_name);
    END LOOP;
    CLOSE l_cursor;
END;
/

-- Test.
EXEC test('T120_COSTING_CATEGORY');

Access to HE90PS PS

(little note to self)

GU_DEVELOPER_BSS_D0

Viewing Keys (indexes) at Oracle Level

select * from system.dba_ind_columns where table_name='ps_table_name';

Application Engine Tracing

You can turn on tracing for a distinct Application Engine process by appending AE parameters. Append parameter "-TRACE 3" to enable the tracing.

Oracle 10

From email:

Oracle (since version 8 ? ) has been recommending to all that we move toward the Cost Based Optimizer (CBO). Prior to this Oracle used (and still can use) the RULE based Optimizer, which pretty much just uses predicate logic and knowledge of indexes to do its job. (Also, the order of tables in the FROM clause is important, and the order of the WHERE clauses)

The CBO attempts to come up with the cheapest row access plan based on the actual cost (CPU and IO) of the plan. To do this, Oracle needs accurate statistics on the tables and indexes involved. These statistics are gathered using the Oracle supplied PLSQL procedures within the DBMS_STATS package. These procedures gather a bunch of different information about the tables and indexes, and can also gather histogram information, which can be invaluable on (mainly indexed) columns where there is highly skewed data. Stats gathering is becoming more automagic as time goes on. Everytime Oracle release a new version, there are new and improved auto options for stats gathering. there is also the possibility (we are not doing this presently) of gathering "system stats" as well. These are separate to the data object stats, and must be gathered by monitoring a live system. These stats attempt to place real time values on IO based on system load. If these are available, the CBO correlates its IO estimates with these real time estimates.

The CBO is not perfect though, and occasionally it will choose a less than optimal access path. This happens mainly because stats usually contain imprecise estimates made during stats gathering. It is possible to collect complete and accurate stats, but this is extremely expensive. To give you an example, it takes over 30 hours to gather stats in HE90U5 (using the auto options, which will estimate some things).

Portal Navigation Structure

select t.portal_name, t.portal_reftype, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_objname as portal_objname, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_label as portal_label, t.portal_ispublic, t.portal_seq_num, t.lastupdoprid, level lev, t.portal_urltext
from sysadm.psprsmdefn t
where t.portal_name = 'EMPLOYEE'
start with t.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR t.portal_objname = t.portal_prntobjname and PRIOR t.portal_name = t.portal_name and PRIOR t.portal_reftype = 'F';