What are constraints?

Constraints enforce rules on the tables.  Constraints prevent deletion of a table, if there are dependencies.  Oracle uses constraints to prevent invalid data entry into tables. [Updates and inserts].  The constraints applied must be satisfied for the successful operation.  The constraints are called integrity constraints  (i.e., to maintain security and integrity this means honesty and reliability) of the database.  An integrity constraint is a mechanism used by oracle to prevent invalid data entry into the table and inadvertent deletes.  So constraints enforce rules that the columns in a table have to confirm with.  The various types of Integrity constraints are:

• Domain Integrity Constraints

Not null Constraint

Check Constraint

• Entity Integrity Constraints

Unique Constraint

Primary Key

• Referential Integrity Constraints

Foreign Key

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.