Geeks - Tip of the Day

krishnaindia2007 · #**101** (**permalink**) 06-17-2008

We can create a view with the same name in two ways.

1. A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.

2.A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This option replaces the current definition of a view, but preserves the present security authorizations.

krishnaindia2007 · #**102** (**permalink**) 06-19-2008

If you use DROP TABLE or DROP VIEW to remove triggering tables or views from the database, all triggers on those tables or views are also dropped.

krishnaindia2007 · #**103** (**permalink**) 06-20-2008

The table on which you create a trigger must exist in the current database.

krishnaindia2007 · #**104** (**permalink**) 06-20-2008

Firing sequence of Database trigger on a single row

Before statement trigger
Before row trigger
After row trigger
After statement trigger

Firing sequence of Database triggers on multiple rows

Before statement trigger
Before row trigger for first row
After row trigger for first row
Before row trigger for second row
After row trigger for second row
.........
After statement trigger

krishnaindia2007 · #**105** (**permalink**) 06-22-2008

when referring to the new or old values in a WHEN clause, you must omit the colon:
WHEN (:NEW.mgr is null)-- is wrong.
It will raise error
"ORA-00920: invalid relational operator"

krishnaindia2007 · #**106** (**permalink**) 06-22-2008

In triggers , intregrity constraints are applied before executing after row triggers.

Example

Code:

 
create table test_11 (num number(10)); 

-- To create primary key constraint 
alter table test_11 add constraint test_11_num_pk primary key (num) 

create or replace trigger tr_before_tab_inst 
before insert on test_11 
begin 
dbms_output.put_line('before insert on table'); 
end; 

create or replace trigger tr_after_tab_inst 
after insert on test_11 
begin 
dbms_output.put_line('after insert on table'); 
end; 

create or replace trigger tr_before_row_inst 
before insert on test_11 
for each row 
begin 
dbms_output.put_line('before insert on row'); 
end; 

create or replace trigger tr_after_row_inst 
after insert on test_11 
for each row 
begin 
dbms_output.put_line('after insert on row'); 
end; 

--for the first statement all the four triggers will be fired insert into test_11 values(1); 
before insert on table 
before insert on row 
after insert on row 
after insert on table 

--for the second statement only before statement and before row triggers were fired. Error message is raised before firing after row trigger 
-- I have executed this in toad 

insert into test_11 values(1); 
before insert on table 
before insert on row 

After firing first two triggers it will display error message 
ora-00001: unique constraint (cmsjan.test_11_num_pk) violated

krishnaindia2007 · #**107** (**permalink**) 06-24-2008

Join Types - I

Join is a process of combining data from two or more tables using matching columns

Equi Join or Inner Join or Simple Join
It used to combine data from two or more tables using equivalent operator (=)

Code:

Ex:- SELECT a.empno, a.ename, a.job, a.sal, a.deptno, b.dname
     FROM   emp a, dept b
     WHERE  a.deptno = b.deptno;
ANSI Syntax
Ex:- SELECT a.empno, a.ename, a.sal , a.deptno, b.dname
     FROM   emp a INNER JOIN dept b
     ON     a.deptno  = b.deptno;

Non Equi Join or Theta Join
It uses relational operators other than = or between to match rows from different tables.

Code:

Ex:- SELECT a.ename, a.job, a.sal , b.grade 
     FROM   emp a, salgrade b
     WHERE  a.sal between b.losal and b.hisal;
ANSI SYNTAX       
       SELECT a.ename, a.job, a.sal , b.grade 
       FROM   emp a INNER JOIN salgrade b
       ON     a.sal BETWEEN  b.losal and b.hisal;

krishnaindia2007 · #**108** (**permalink**) 06-24-2008

JOIN TYPES - II

Cartesian Join or Cross Join
It joins every row of one table with every row of another table. Cartesian join is obtained when you give invalid join condition or omit join condition. To avoid Cartesian join a should have at least N-1 join condition.

Code:

Ex:- SELECT a.ename,a.job,a.sal , b.dname 
     FROM   emp a , dept b;
ANSI SYNTAX        
        SELECT ename, job, sal, dname 
        FROM   emp CROSS JOIN dept;

Natural Join
Natural join combines data from two tables based upon all common columns in both the table.

Code:

Ex:- SELECT ename, job, sal, dname  
     FROM   emp  NATURAL JOIN dept;
     -  Natural joins between two tables without at least one
        common column results in Cartesian product.
     -  We can’t join more than two tables using this method.
     -  It may cause problems if columns are added or renamed

Self Join
Join the table with itself is called self join.

Code:

 
Ex:- SELECT a.ename, a.job, a.sal, b.ename Manager 
     FROM   emp a, emp b
     WHERE  a.mgr  = b.empno;
 ANSI SYNTAX
        SELECT a.ename, a.job, a.sal, b.ename Manager 
        FROM   emp a JOIN emp b
        ON     a.mgr = b.empno;

krishnaindia2007 · #**109** (**permalink**) 06-24-2008

Join Types - III

Outer Join
outer join extends the results of inner join. Along with inner join results it will also return non matched rows from the table with outer join operator(+). Missing values are filled with null.

Left Outer Join
It returns row that meets the join condition + rest of the rows from left table

Code:

 
Ex:- select b.dname, a.ename, a.job, a.sal 
     from   emp a , dept b 
     where  b.deptno = a.deptno(+); 
Ansi Syntax 
     select b.dname, a.ename, a.job, a.sal 
     from   dept b left outer join emp a 
     on a.deptno = b.deptno;

Right Outer Join
It returns row that meets the join condition + rest of the records from table on the right side.

Code:

 
Ex:- select b.dname, a.ename, a.job, a.sal 
     from emp a , dept b 
     where a.deptno(+) = b.deptno; 
Ansi Syntax 

    select b.dname, a.ename, a.job, a.sal 
    from   emp a right outer join dept b 
    on     a.deptno = b.deptno;

Full Outer Join
It returns rows that meet the join codition + rest of the records from both the tables

Code:

 
Ansi Syntax 
select b.dname, a.ename, a.job, a.sal 
from   emp a full outer join dept b 
on     a.deptno = b.deptno;

krishnaindia2007 · #**110** (**permalink**) 06-24-2008

JOIN TYPES - IV

Anti Join
It returns rows from first table for which there are no corresponding rows in the second table. Anti joins are written using NOT EXISTS or NOT IN.

Code:

Ex:-   SELECT a.dname 
       FROM   dept a
       WHERE  NOT EXISTS ( SELECT 1 FROM emp b WHERE b.deptno = a.deptno);
Which is same as 
        SELECT dname
        FROM   dept 
        MINUS
        SELECT b.dname
        FROM   emp a, dept b
        WHERE  a.deptno = b.deptno;

Semi Join
It return rows from the first table for which one or more joins are found in the second table. It is written using IN or EXISTS.

Code:

Ex:- SELECT a.dname 
     FROM   dept a
     WHERE EXISTS ( SELECT 1 FROM emp b WHERE b.deptno = a.deptno);

krishnaindia2007 · #**111** (**permalink**) 06-25-2008

How to goto dos prompt from Sql *Plus?
At sql prompt type $ or host and press enter

krishnaindia2007 · #**112** (**permalink**) 06-25-2008

Oracle database contains both logical and physical structures.

Logical structures are
Table space
Schema
Extent
Segment

Physical structures are
Data files
Redo Log files
Control files

krishnaindia2007 · #**113** (**permalink**) 06-27-2008

Oracle Built-In packages - I

Dbms_Metadata.Get_Ddl
It is used to retrieve complete database object definitions (metadata) from dictionary. By specifying the type of object, for example, tables, indexes, or procedures optional selection criteria, such as owner etc.

Code:

 
EX:- set heading off; 
     set pages 999; 
     set long 5000; 
     
     select dbms_metadata.get_ddl('table','emp','cmsjan') 
     from dual; 
 
     Here table is object type 
          emp is object name 
          cmsjan is schema name.

To get whole schema table definitions

Code:

 
    set heading off 
    Set pagesize 0 
    set long 90000 
    set feedback off 
    spool cmsjanschema.sql 
 
    select dbms_metadata.get_ddl('table',x.table_name) 
    from user_tables x; 

    spool off; 
    set feedback on; 
    set heading on;

krishnaindia2007 · #**114** (**permalink**) 06-27-2008

Oracle Built-in Packages - II

DBMS_UTILITY provides various utility procedures and functions.

In this package Get_time function provides elapsed time in seconds interval. Finds out the current time in 100th's of a second.

Code:

Example
   DECLARE
   time_before BINARY_INTEGER;
   time_after  BINARY_INTEGER;
   
   BEGIN
   time_before := DBMS_UTILITY.GET_TIME;
   CMSRPT_MONTHWISETARGET_INS('200607','200708','Sugar Cane');     
   time_after := DBMS_UTILITY.GET_TIME;
   
   DBMS_OUTPUT.PUT_LINE (time_after - time_before);
   
   END;

OUTPUT
SQL> @krk;
623

i.e. it takes 6.23 seconds time to execute the procedure.

krishnaindia2007 · #**115** (**permalink**) 06-27-2008

Oracle Built-In Packages - III

Generally we use v_$version to know oracle version number as follows

Code:

 
sql> select * 
   2 from sys.v_$version; 
oracle9i enterprise edition release 9.2.0.6.0 - production 
pl/sql release 9.2.0.6.0 - production 
core 9.2.0.6.0 production 
tns for 32-bit windows: version 9.2.0.6.0 - production 
nlsrtl version 9.2.0.6.0 - production

The pl/sql package dbms_db_version supplies several useful constants that you can use in your code to determine the currently running version of oracle. This package is introduced in oracle9i version release 2. This package contains no functions and procedures. It contains only a series of constants to determine running version.

Code:

 
sql> set serveroutput on; 
sql> exec dbms_output.put_line (dbms_db_version.version); 
9 
Here 9 is oracle version number

krishnaindia2007 · #**116** (**permalink**) 06-27-2008

Oracle Built-In Packages - IV

Using DBMS_RANDOM package functions and procedures we can generate random numbers , strings and dates.

The value function in dbms_random is used to generate random numbers. This function comes in two specifications

1. DBMS_RANDOM.VALUE( )
2. DBMS_RANDOM.VALUE( low IN NUMBER, high IN NUMBER)

Value function without arguments generates a random number greater than or equal to 0 and less than 1, with 38 digits of precision.

Code:

Ex:- SQL> SELECT dbms_random.value() FROM dual;
 
          DBMS_RANDOM.VALUE()
          -------------------
             .480358

VALUE function with two arguments generates a number in between low values and high value. The high value is not included as a possible value.

Code:

Ex:- The following function generates a random number in between 0 and 99

     SQL> SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) 
          FROM DUAL;
          
          TRUNC(DBMS_RANDOM.VALUE(0,100))
          -------------------------------
               92

The Following User Says Thank You to krishnaindia2007 For This Useful Post:
babi_geek