Page 6 of 10 FirstFirst ... 45678 ... LastLast
Results 101 to 120 of 194

Thread: Geeks - Tip of the Day

  1. #101
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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.


  2. #102
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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.


  3. #103
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

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


  4. #104
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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


  5. #105
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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"


  6. #106
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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



  7. #107
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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;



  8. #108
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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;



  9. #109
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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;



  10. #110
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

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



  11. #111
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

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


  12. #112
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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


  13. #113
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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;



  14. #114
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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.



  15. #115
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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


    Last edited by krishnaindia2007; 06-27-2008 at 12:06 AM.

  16. #116
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    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



  17. #117
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Oracle Built-In Packages- V

    The string function in dbms_random package generates random text string.

    Syntax:- dbms_random.string(opt, len);

    Here len determines length of the string

    Opt Specifies What the returning string looks like. It can take any one of the following codes.
    'u' or 'U' - returning string in uppercase alpha characters
    'l' or 'L' - returning string in lowercase alpha characters
    'a' or 'A' - returning string in mixed case alpha characters
    'x' or 'X' - returning string in uppercase alpha-numeric characters
    'p' or 'P' - returning string in any printable characters.

    Code:
    Ex:- 
     SQL> SELECT dbms_random.string('u', 10) StringEx 
          FROM dual;
    
          StringEx
          --------------------
          YYHFTNZAUU
    We can also generate date random values using to_char function with Julian date format.For example, to generate random dates during the year 2007, first determine the date integer for January 1, 2007

    Code:
    SQL> SELECT TO_CHAR(TO_DATE('01/01/07','mm/dd/yy'),'J')
         FROM DUAL;
    
    TO_CHAR
    -------
    2454102
    Now we can use the above date integer 2454102 to generate random numbers using DBMS_RANDOM.VALUE with a low_value of 2452641 and a high_value of 2452641+364, and convert it to a date.

    Code:
    SQL> SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454102,2454102+364)),'J') 
      2  FROM DUAL;
    
    TO_DATE(T
    ---------
    20-JUL-07



  18. #118
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Julian Day

    A Julian day for a date is number of days since 01-JAN-4712 BC inclusive.

    Code:
    SQL> SELECT to_char(sysdate,'J') 
      2  FROM   dual;
    
    TO_CHAR
    -------
    2454646
    Oracle uses julian date internally for date arithmetic. For example, when adding a number to a date, Oracle first converts the date to a Julian day, then performs the addition,and then converts the resulting Julian day back into a date.

    Generally we use this julian date format to convert a number to words

    Code:
    SQL> SELECT to_char( to_date('4500','J'), 'JSP') 
      2  FROM   dual;
    
    TO_CHAR(TO_DATE('4500','J'
    --------------------------
    FOUR THOUSAND FIVE HUNDRED


    Last edited by krishnaindia2007; 06-27-2008 at 10:41 PM.

  19. #119
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Default reserved word can be used while inserting and updating values.

    Example:
    Code:
    SQL>  CREATE TABLE TEST_DATA (X  VARCHAR2(10) DEFAULT 'X' , Y VARCHAR2(10));
    
    Table created.
    
    SQL> INSERT INTO TEST_DATA VALUES (DEFAULT, DEFAULT);
    
    1 row created.
    
    SQL> SELECT * FROM TEST_DATA;
    
    X          Y
    ---------- ----------
    X
    If default value exists it will be used other wise null will be used.

    Last edited by krishnaindia2007; 07-01-2008 at 06:35 AM.

  20. #120
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    on delete cascade and cascade constraints
    on delete cascade :- when rows from parent table are deleted causing all rows in child tables with dependent foreign key values to also be deleted example
    Code:
     
    sql> create table test_one ( x number constraint test_one_pk primary key); 
    table created. 
    Sql> create table test_two (y number constraint test_one_fk references 
                                test_one(x) on delete cascade); 
    table created. 
    Sql> insert into test_one values(1); 
    1 row created. 
    Sql> insert into test_one values(2); 
    1 row created. 
    Sql> insert into test_one values(3); 
    1 row created. 
    Sql> insert into test_two values(1); 
    1 row created.
    Now delete a row from parent table. It will automatically delete dependent foriegn key value from child table.
    Code:
     
    sql> delete from test_one where x = 1; 
    1 row deleted. 
    Sql> select * from test_one; 
    x 
    --------- 
    2 
    3 
    sql> select * from test_two; 
    no rows selected
    Cascade Constraints:- when cascade constraints is specified , first it deletes all foreign keys that reference the table to be dropped, then drops the table.
    Code:
     
    sql> drop table test_one cascade constraints; 
    table dropped. 
    Sql> select constraint_name from user_constraints 
       2 where table_name = 'test_two'; 
    no rows selected


    Last edited by krishnaindia2007; 07-03-2008 at 02:09 AM.

Page 6 of 10 FirstFirst ... 45678 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact