Oracle Repository API and Model Reference Guide

Contents API Methods Element Definitions Index

PL/SQL Example

The following script is an example of an interactive SQL*Plus application session that uses CDAPI method calls.

set serveroutput on

REM *******************************************************************
REM insert entity test script
REM
REM When run from SQL*Plus, this script displays information to
REM the session window if the SERVEROUTPUT variable is set to ON
REM *******************************************************************
set serveroutput on
exec dbms_output.enable(60000);

DECLARE
    ent cioentity.data; -- Property list for ENTITY
    act_status VARCHAR2 (1); -- Activity status
    act_warnings VARCHAR2 (1); -- Activity warning flag
    procedure instantiate_messages is
        m_facility VARCHAR2 (3);
        m_code NUMBER;
        arg1 VARCHAR2 (240);
        arg2 VARCHAR2 (64);
        arg3 VARCHAR2 (64);
        arg4 VARCHAR2 (64);
        arg5 VARCHAR2 (20);
        arg6 VARCHAR2 (20);
        arg7 VARCHAR2 (20);
        arg8 VARCHAR2 (20);

    begin
        -- Report all violations regardless of the activity status

        for viol in (select * from CI_VIOLATIONS) loop
            dbms_output.put_line (cdapi.instantiate_message (
            viol.facility, viol.code,
            viol.p0, viol.p1, viol.p2, viol.p3, viol.p4, viol.p5, viol.p6, viol.p7));
        end loop;

        -- Pop messages off the stack and format them into a single text string

        while cdapi.stacksize > 0 loop
            rmmes.pop (m_facility, m_code, arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8
            );
            dbms_output.put_line (cdapi.instantiate_message (m_facility, m_code,
            arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8));
        end loop;
    end;

BEGIN
    -- Set the context workarea

    jr_context.set_workarea('&&context_workarea_name'); 

    -- Initialize API if not already done

    if cdapi.initialized = FALSE then

        -- Initialize the API global items

        cdapi.initialize ('&&application_system_name');

    end if;

    -- Open a new activity

    dbms_output.put_line ('Opening a new activity');
    cdapi.open_activity;

    -- Put values into the property list

    ent.v.ANNUAL_GROWTH_RATE := '&ANNUAL_GROWTH_RATE';
    if ent.v.ANNUAL_GROWTH_RATE is not NULL then
        ent.i.ANNUAL_GROWTH_RATE := TRUE;
    end if;

    ent.v.INITIAL_VOLUME := '&INITIAL_VOLUME';
    if ent.v.INITIAL_VOLUME is not NULL then
        ent.i.INITIAL_VOLUME := TRUE;
    end if;

    ent.v.MAXIMUM_VOLUME := '&MAXIMUM_VOLUME';
    if ent.v.MAXIMUM_VOLUME is not NULL then
        ent.i.MAXIMUM_VOLUME := TRUE;
    end if;

    ent.v.NAME := '&NAME';
    if ent.v.NAME is not NULL then
        ent.i.NAME := TRUE;
    end if;

    ent.v.PLURAL := '&PLURAL';
    if ent.v.PLURAL is not NULL then
        ent.i.PLURAL := TRUE;
    end if;

    ent.v.SHORT_NAME := '&SHORT_NAME';
    if ent.v.SHORT_NAME is not NULL then
        ent.i.SHORT_NAME := TRUE;
    end if;

    ent.v.VOLUME := '&VOLUME';
    if ent.v.VOLUME is not NULL then
        ent.i.VOLUME := TRUE;
    end if;

    dbms_output.put_line ('Inserting a new entity');
    cioentity.ins (NULL, ent);
    dbms_output.put_line ('Selecting the entity');
    cioentity.sel (ent.v.id, ent);

    -- Select the property list

    dbms_output.put_line ('ANNUAL_GROWTH_RATE : '||ent.v.ANNUAL_GROWTH_RATE);
    dbms_output.put_line ('INITIAL_VOLUME : '||ent.v.INITIAL_VOLUME);
    dbms_output.put_line ('MAXIMUM_VOLUME : '||ent.v.MAXIMUM_VOLUME);
    dbms_output.put_line ('NAME : '||ent.v.NAME);
    dbms_output.put_line ('PLURAL : '||ent.v.PLURAL);
    dbms_output.put_line ('SHORT_NAME : '||ent.v.SHORT_NAME);
    dbms_output.put_line ('VOLUME : '||ent.v.VOLUME);

    -- Validate the activity

    cdapi.validate_activity (act_status, act_warnings);

    -- Display all violations and other messages regardless of the activity
    -- warnings flag

    instantiate_messages;

    -- Attempt to close the activity

    cdapi.close_activity (act_status);

    -- If the activity did not close successfully, roll back all changes made
    -- during the activity

    if act_status != 'Y' then
        cdapi.abort_activity;
        dbms_output.put_line ('Activity aborted with constraint violations');
    -- Otherwise, we're done
    else
        dbms_output.put_line ('Activity closed successfully');
    end if;

    EXCEPTION
    WHEN OTHERS THEN

        -- If any messages have been posted on the stack, then print them now
        -- and then roll back all changes made during the activity

        if cdapi.stacksize > 0 then

            -- Print all messages on the API stack

            while cdapi.stacksize > 0 loop
                dbms_output.put_line (cdapi.pop_instantiated_message);
            end loop;

            if cdapi.activity is not NULL then
                cdapi.abort_activity;
                dbms_output.put_line ('Activity aborted with API errors');

            else
                dbms_output.put_line ('API Session aborted with API errors');
            end if;

            raise;

        -- Otherwise, this must have been an ORACLE SQL or internal error so
        -- roll back all changes made during the activity and re-raise the
        -- exception

        else

            if cdapi.activity is not NULL then
                cdapi.abort_activity;
                dbms_output.put_line ('Activity aborted with ORACLE internal errors');
            else
                dbms_output.put_line ('API Session aborted with ORACLE internal errors');
            end if;
            raise;

        end if;
END;
/