Why PL/SQL Exceptions Exceptions?

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors:

BEGIN

   SELECT ...

      -- check for 'no data found' error

   SELECT ...

      -- check for 'no data found' error

   SELECT ...

      -- check for 'no data found' error

Error processing is not clearly separated from normal processing; nor is it robust. If you neglect to code a check, the error goes undetected and is likely to cause other, seemingly unrelated errors.

With exceptions, you can handle errors conveniently without the need to code multiple checks, as follows:

BEGIN

   SELECT ...

   SELECT ...

   SELECT ...

   ...

EXCEPTION

   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors

Exceptions improve readability by letting you isolate error-handling routines. Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.

Source Tables in EAM (Enterprise Asset Management)

Below are some the source tables used in EAM Module:

BOM_DEPARTMENTS

BOM_RESOURCES

CST_ACTIVITIES

MTL_TRANSACTION_REASONS

MTL_TRANSACTION_TYPES

MTL_SYSTEM_ITEMS

MFG_LOOKUPS

MTL_TRANSACTION_ACCOUNTS

MTL_MATERIAL_TRANSACTIONS

WIP_ENTITIES

CST_ACTIVITIES

PO_HEADERS

MTL_TRANSACTION_REASONS

WIP_TRANSACTION_ACCOUNTS

WIP_TRANSACTIONS

WIP_ENTITIES

WIP_REPETITIVE_ITEMS

WIP_DISCRETE_JOBS

WIP_LINES

Adding Multiple Columns on Existing Table

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name

ADD ( column_1 column-definition,

  column_2 column-definition,

  ... 

  column_n column_definition );

For example:

ALTER TABLE DEPARTMENTS_D

ADD ( supplier_name varchar2(50),

  city varchar2(45) );

ALTER TABLE ASSETS

ADD ( START_DATE DATE ,END_DATE DATE );

ALTER TABLE COST

ADD(X_CUSTOM VARCHAR2(50),

     DATASOURCE_NUM_ID NUMBER

     TENANT_ID NUMBER

     ETL_PROC_WID NUMBER

     INTEGRATION_ID VARCHAR2(50));

 

Trigger With The When Condition

create table Emp_tab as select * from emp where 1=2;

Table created.

CREATE OR REPLACE TRIGGER Print_salary_changes

BEFORE

DELETE OR INSERT OR UPDATE ON Emp_tab

      FOR EACH ROW

    WHEN (new.Empno > 0)

    DECLARE

        sal_diff number;

    BEGIN

        sal_diff  := :new.sal  - :old.sal;

        dbms_output.put('Old salary: ' || :old.sal);

       dbms_output.put('  New salary: ' || :new.sal);

       dbms_output.put_line('  Difference ' || sal_diff);

 END;

insert into Emp_tab ( select * from emp where deptno=10);

Old salary:   New salary: 2450  Difference

Old salary:   New salary: 5000  Difference

Old salary:   New salary: 1300  Difference

3 rows created.

update Emp_tab set sal=9000;

Old salary: 2450  New salary: 9000  Difference 6550

Old salary: 5000  New salary: 9000  Difference 4000

Old salary: 1300  New salary: 9000  Difference 7700

3 rows updated.

delete Emp_tab;

3 rows deleted.

Use The Referencing Clause

If we want we can use the referencing clause to specify a different name [similar to alias name] for :old and :new correlation names.  This is the syntax:

referencing [old as old_name] [new as new_name]

Example:

Create or replace triggergenerateprofID

before insert on professor

referencing new as new_prof

for each row

begin

if :new_prof.pid is null then

select profids.nextval into :new_prof.pid from dual;

end if;

end triggergenerateprofID;