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

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));

 

SQLERRM and SQLCODE IN ORACLE SQL/PLSQL

SQLERRM and SQLCODE are two functions we can use for trapping the error-message and error-code in the exception handler when any exception is raised.  They cannot be used directly in a print statement.  They have to be stored in a variable and the variable is used for the print statement.

CREATE TABLE TEMP (ENAME VARCHAR2 (25), SOMENO NUMBER);

DECLARE

pe_ratio NUMBER(3,1);

A VARCHAR2(100);

B NUMBER;

NAME VARCHAR2(25);

BEGIN

NAME:='&NAME';

SELECT SAL/NVL(COMM, 0) INTO pe_ratio FROM EMP WHERE ENAME = NAME;

     INSERT INTO TEMP (ENAME, SOMENO) VALUES (NAME, pe_ratio);

     EXCEPTION

     WHEN No_data_found THEN

     A:=SQLERRM;

     B:=SQLCODE;

     dbms_output.put_line('Error number = ' || B);

     dbms_output.put_line('Error message = ' || A);

     WHEN ZERO_DIVIDE THEN

   A:=SQLERRM;

   B:=SQLCODE;

  INSERT INTO TEMP (ENAME, SOMENO) VALUES (NAME, pe_ratio);

  dbms_output.put_line('Error number = ' || B);

  dbms_output.put_line('Error message = ' || A);

  END;

 /

Enter value for name: SCOTT

old   7: NAME:='&NAME';

new   7: NAME:='SCOTT';

Error number = -1476

Error message = ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

/

Enter value for name: KUMAR

old   7: NAME:='&NAME';

new   7: NAME:='KUMAR';

Error number = 100

Error message = ORA-01403: no data found

PL/SQL procedure successfully completed.

/

Enter value for name: MARTIN

old   7: NAME:='&NAME';

new   7: NAME:='MARTIN';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEMP;

ENAME                         SOMENO

------------------------- ----------

SCOTT

MARTIN                            .9

How to do Oracle 11g 10g Certification??

Given below are the Different available Certification Exams  with Oracle Database 11g and Oracle 1og Database.

Also those who are planning to take up the certification exams for Oracle can visit the below link

http://www.pearsonvue.com/Oracle/

Exam Name

Introduction to Oracle9i SQL®

Oracle Application Express 4: Developing Web Applications

Oracle Database 10g Administrator Certified Master Exam

Oracle Database 10g DBA New Features for Oracle8i OCPs

Oracle Database 10g R2: Administering RAC

Oracle Database 10g: Administration I

Oracle Database 10g: Administration II

Oracle Database 10g: Managing Oracle on Linux for Database Administrators

Oracle Database 10g: New Features for Administrators™

Oracle Database 11g Certified Master Exam

Oracle Database 11g Certified Master Upgrade Exam

Oracle Database 11g Data Warehousing Essentials

Oracle Database 11g Essentials

Oracle Database 11g Release 2: SQL Tuning

Oracle Database 11g Security Essentials

Oracle Database 11g: Administration I

Oracle Database 11g: Administration II

Oracle Database 11g: New Features for 9i OCPs

Oracle Database 11g: New Features for Administrators

Oracle Database 11g: Performance Tuning

Oracle Database 11g: SQL Fundamentals I

Oracle Database Cloud Administration

Oracle Enterprise Manager 11g Essentials

Oracle Enterprise Manager 12c Essentials

Oracle Exadata 11g Essentials

Oracle Real Application Clusters 11g Essentials

Oracle Real Application Clusters 11g Release 2 and Grid Infrastructure Administration

Oracle Spatial 11g Essentials

Oracle9i Database: Fundamentals II ™

Oracle9i Database: Fundamentals I™

Oracle9i Database: Performance Tuning™

Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs

Oracle9i New Features for Administrators™