Much More About Constraints

PRIMARY KEY LOOK UP DURING FOREIGN KEY CREATION


The lookup of matching primary keys at time of foreign key insertion takes time.In realease Oracle 9i , the first 256 primary keys can be cached so the addition of multiple foreign keys become significantly faster .The cache is only set up after the second row is processed.this avoid overhead of setting up a cache for single row DML.

----------------------
Constraints On View
--------------------
Constraint definitions are done on View from Oracle 9i onwards.
Views constraint definitions are declarative in nature;therefore DML operations on view are subject to the constraints defined on base tables.
defining constraints on base table is necessary ,not only for data correctness and cleanliness but also for MV query

NOT NULL and CHECK constraint are not supported on Views

For Creating View with constraints you must specify [RELY|NORELY ](Allows/disallows query rewrites) DISABLE NOVALIDATE (valid state for view constraint )
Otherwise it will result an error message.

Trigger (Insertion on Same Table)

Trigger to insert a field from sequence at user insertion itself by avoiding mutating table error. For example ; for a table as 'TABLE_NAME' with fields N_FIELDNAME,N_CODE,N_ID,VC_NAME,DT_DATE here excluding N_FIELDNAME field all other fields are user input; N_FIELDNAME field value is retrieved from SEQUENCE.If we use
select query on same table 'TABLE_NAME' it will result into Mutating table error .So this piece of trigger code is used to do the above.

CREATE OR REPLACE TRIGGER TRG_NMAE
BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
DECLARE
N_VARIABLE NUMBER(10):=0;
BEGIN
SELECT SEQUENCE_NAME.NEXTVAL INTO N_VARIABLE FROM DUAL;
:NEW.N_FIELDNAME :=N_VARIABLE;
END ;

DB LINK

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Here example are given from Oracle database to another Oracle database .

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY password
USING sid_name

In Some situation even though user_name,sid_name and password is correct database link created will result to a failure ;this is because Oracle cannot able to get the connection because of some reason ;in-order to meet this situation we want to specify more details for DBLink creation then this script cam be used.

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY 'password'
USING '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521)))
(CONNECT_DATA=(SID=sid_name)))';

Errors

1)
By using DBLink DDL operations are not allowed .Such as Create ,Drop ,Truncate etc

Truncate table TABLENAME@DBLINK
ORA-02021: DDL operations are not allowed on a remote database

2)
If a Table which containing LOB datatypes are not able select using dblink

create table test_db ( fild_db clob,num number)

select * from test_db@convert;
ORA-22992: cannot use LOB locators selected from remote tables

Here only selection of datatypes other than LOB are allowed

select num from test_db@convert; --> This Works Fine.

3)

Before dropping a DBLink all transactions should be end else this error will occur.
ORA-02018: database link of same name has an open connection

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

JOBS /// IsNumeric

Job will execute in a current schema only ;It will not switch between two schemas .

------------------------------
IsNumeric in ORACLE
----------------

select ISNUM from (
select LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) ISNUM from table_name ) where ISNUM is null

create or replace function isnumeric (param in char) return boolean as
dummy varchar2(100);
begin
dummy:=to_char(to_number(param));
return(true);
exception
when others then
return (false);
end;
/

Hidden Table

Create table " " (EMPNO NUMBER(3));

Creates a table with 4 spaces as its name.
When you select the list of tables from all_tables, such tables are not visible.

To access such a table use
Select * From " ";

(Remember you need to give exactly the same number of spaces as you gave while creating the table).

This feature can be used to store information that you feel is confidential for you.

For Example :

You might create tables of this kind

2 spaces + "MYTAB" + 1 space + "FILE" + 3 spaces

When you create such a table only you know how to access it (Unless someone is intelligent than you to guess it).