Triggers - A Depth View...

TRIGGERS
Triggers are block of PL/SQL code that you write to perform your specific tasks.They  are pre-defined runtime events

for which oracle will provide you to associate a trigger.All the trigger name correspond to these events.The events

which can be trapped are GUI events  as well as transactional events.

What are all theValid Statements in Trigger Text
•    Select statements
•    DML statements
•    Restricted built-in subprograms
•    Unrestricted built-in subprograms

What is Restricted Built-in ?
Restricted built-ins are those built-ins which initiate navigation like NEXT_ITEM etc
and also those that involve database transactions,such as CREATE_RECORD.

Different Trigger types

1.    BLOCK PROCESSING TRIGGERS
   
•    WHEN-CLEAR-BLOCK
Fires just before the oracle forms clears the data from the current block.It does not fire when the Oracle forms 

clears the current block during the CLEAR_FORM event.
Defn Level         :         Form or Block.
Enter-Query Mode     :        Yes
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
•    use this trigger to perform action everytime oracle forms flushes the current block.For example you might

want to perform automatic commit whenever this condition occurs
•    The value of  SYSTEM.RECORD_STATUS  is unreliable because there is no current record. An alternative is to

use GET_RECORD_PROPERTY to obtain the record status.because GET_RECORD_PROPERTY requires you to reference a specific

record,its value is always accurate.
Failure of Trigger    :        No effect of block clearing
Fires in        :        CLEAR_BLOCK
                    COUNT_QUERY
                    ENTER-QUERY
                    Open the Query.

•    WHEN-CREATE-RECORD
Fires when Oracle forms creates a new record or the form operator presses the [insert  key] or while scrolling he

scrolls to the last record in the set.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
use this trigger to perform a action every time when oracle attempts to create  a new record.   
Failure of the Trigger    :        Prevents the New record to be created and
                    the control returns to previous location if
                    possible.
Fires in        :        CREATE_RECORD.

•    WHEN-DATABASE-RECORD
Fires when the oracle marks the record as INSERT or UDATE.That is the oracle forms through validation process

determines that record should processed as INSERT or UPDATE in the Next Post or Commit Process.This Occurs after a

Operator modifies the first item of a record and tries to navigate out of the Item.
Defn Level         :        Form or Block
Enter-Query Mode      :        No
Legal Commands    :        SELECT statements,unrestricted built-ins.

When to Use ?
Use this trigger to perform a action when oracle forms marks a record as Insert or Update
Failure of Trigger    :        Nothing happens.

•    WHEN-REMOVE-RECORD
Fires whenever the operator or the application Clear record or Delete the Record.
Defn Level        :        Form,Block and Item
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
Use this trigger to perform a action when oracle forms tries to clear a record or delete record.
Failure of Trigger    :        Oracle Forms navigates to Block level with
                    or without validation and tries to put the
                    cursor at the target block.
Fires in        :        CLEAR_BLOCK
                    DELETE_RECORD
                    PREVIOUS_RECORD.

2.     MASTER-DETAIL TRIGGERS

•    ON-CHECK-DELETE-MASTER
Oracle forms creates this trigger automatically when we define a master-detail relation ship ans set Master Deletes

property to NON- ISOLATED.It fires when a attempt is made to DELETE  record in Master-block  of a master-detail

relation-ship.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins
                    and DML statements.

   
When to use ?
It is used to check whenever you try to delete the master record,whether any detail records of the master exists, in

case any details are present then the Master record is prevented from deletion.

Failure of Trigger    :        Prevents the deletion of the Master record.
Fires in        :        MASTER-DETAIL COORDINATION.

•    ON-CLEAR-DETAIL
Fires whenever a coordination causing event occurs in Master-Block.The coordination causing event is the event which

makes the a different master record as the current record, in the Master Block.
Defn Level        :        Form or Block.
Enter-Query Mode    :        No
Legal Commands    :        Any commands including restricted built ins

Failure of Trigger    :        Causes the coordination-causing operation and any
                    scheduled coordination triggers to abort.
Fires in        :        MASTER-DETAIL COORDINATION.

•    ON-POPULATE-DETAIL
Whenever we create a master-detail relation ship,oracle forms automatically will create this trigger.It fires when

oracle forms normally needs to populate the detail block in master-detail relation.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,PL/SQL,unrestricted and
                    restricted built-ins.
When to Use ?
•    Use this trigger to replace the default populate phase of a Query in Master-detail.
•    When the immediate Coordination is set this causes the details of the instantiated master to be populated

immediately. By default the Coordination is immediate.
•    WhenDeffered is coordination status,then this trigger fires,Oracle forms marks the blocks as needing to be

coordinated.
•    If you intend to manage the coordination on your own then there is built in called as the

SET_BLOCK_PROPERTY(COORDINATION_STATUS).
Note : This trigger will not fire if there is no ON-CLEAR-DETAILS trigger.
Failure of Trigger    :        Causes an inconsistent state in the Form
Fires in        :        MASTER-DETAIL COORDINATION.


3.     MESSAGE-HANDLING TRIGGERS

•    ON-ERROR
This trigger fires whenever a Oracle forms would normally cause an Error message to display.
Defn Level           :        Form,Block or Item
Enter-Query Mode    :        Yes
Legal Commands    :        SELECT statements,unrestricted built-ins
When to Use ?
•    Use this trigger to trap and recover from a error
•    To replace a default error message with a custom error message
Failure of Trigger    :        No effect

•    ON-MESSAGE
This trigger fires whenever oracle form would normally cause a message to display.This trigger will replace the

writing of the message to the message line.
Defn Level        :        Form,Block or Item
Enter-Query Mode    :        Yes
Legal Commands    :        SELECT statements,unrestricted built ins.
When to Use ?
•    Use this trigger to exclude or to replace a message with a custom message.The GET_MESSAGE built does not

return any value.
Failure of Trigger    :        No effect

3.     TRANSACTIONAL TRIGGERS

•    ON-CHECK-UNIQUE
This trigger fires during the commit operation,when oracle forms noramally checks that the primary key values are

unique for each INSERT or UPDATE in a base table.It fires once for each record UPDATED or INSERTED.This record

replaces the DEFAULT checking of UNIQUENESS for records,when PRIMARY key property of the record is set to true.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,PL/SQL and unrestricted
                    built ins
When to Use ?
To check the uniqueness of items before they are inserted or updated.To do the Default processing from this trigger

call the built in CHECK_RECORD_UNIQUENESS.
Failure of Trigger    :        No effect
Fires in        :        CHECK RECORD UNIQUENESS
                    POST AND COMMIT TRANSACTIONS.
•    ON-CLOSE
This trigger normally fires when the operator or an application closes a query.The query is closed by default when

all the records identified by query criteria have been fetched or when the operator or the application aborts the

query.

SQL - A depth View

What is SQL and where does it come from?
Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was

developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard.

SQL is often pronounced SEQUEL.
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data

Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called

SQL2. SQL3 support object extensions and will be (partially?) implemented in Oracle8.


What are the difference between DDL, DML and DCL commands?
DDL is Data Definition Language statements. Some examples:
•    CREATE - to create objects in the database
•    ALTER - alters the structure of the database
•    DROP - delete objects from the database
•    TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
•    COMMENT - add comments to the data dictionary
•    GRANT - gives user's access privileges to database
•    REVOKE - withdraw access privileges given with the GRANT command
DML is Data Manipulation Language statements. Some examples:
•    SELECT - retrieve data from the a database
•    INSERT - insert data into a table
•    UPDATE - updates existing data within a table
•    DELETE - deletes all records from a table, the space for the records remain
•    CALL - call a PL/SQL or Java subprogram
•    EXPLAIN PLAN - explain access path to data
•    LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
•    COMMIT - save work done
•    SAVEPOINT - identify a point in a transaction to which you can later roll back
•    ROLLBACK - restore database to original since the last COMMIT
•    SET TRANSACTION - Change transaction options like what rollback segment to use
How can I eliminate duplicates values in a table?
Choose one of the following queries to identify or remove duplicate rows from a table leaving one record:
Method 1:
   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min(rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);
Method 2:
   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;
Method 3:
   SQL> Delete from my_table where rowid not in(
   SQL>     select max(rowid) from my_table
   SQL>     group by my_column_name );
Method 4:
   SQL> delete from my_table t1
   SQL> where  exists (select 'x' from my_table t2
   SQL>                 where t2.key_value1 = t1.key_value1
   SQL>                   and t2.key_value2 = t1.key_value2
   SQL>                   and t2.rowid      > t1.rowid);
Note: If you create an index on the joined fields in the inner loop, you, for all intents purposes, eliminate N^2

operations (no need to loop through the entire table on each pass by a record).
How can I generate primary key values for my table?
Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:
SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;

Finally, create a unique index on this column.
How can I get the time difference between two date columns
Look at this example query:
select floor(((date1-date2)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600 -
       (floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference
from   ...
How does one count different data values in a column?

        select dept, sum(  decode(sex,'M',1,0)) MALE,
                     sum(  decode(sex,'F',1,0)) FEMALE,
                     count(decode(sex,'M',1,'F',1)) TOTAL
        from   my_emp_table
        group  by dept;
How does one count/sum RANGES of data values in a column?
A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
        select f2,
               sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
               sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
               sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
        from   my_table
        group  by f2;
For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...).

Eg.
        select ename "Name", sal "Salary",
               decode( trunc(f2/1000, 0), 0, 0.0,
                                          1, 0.1,
                                          2, 0.2,
                                          3, 0.31) "Tax rate"
        from   my_table;
Can one retrieve only the Nth row from a table?
provided this solution to select the Nth row form a table:
        SELECT f1 FROM t1
        WHERE  rowid = (
           SELECT rowid FROM t1
           WHERE  rownum <= 10
          MINUS
           SELECT rowid FROM t1
           WHERE  rownum < 10);
Alternatively...
        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
           (SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even

help in the odd situation.
Can one retrieve only rows X to Y from a table?
To display rows 5 to 7, construct a query like this:
        SELECT *
        FROM   tableX
        WHERE  rowid in (
           SELECT rowid FROM tableX
           WHERE rownum <= 7
          MINUS
           SELECT rowid FROM tableX
           WHERE rownum < 5);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even

help in the odd situation.
How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
        SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                             FROM   emp);
Method 2: Use dynamic views (available from Oracle7.2):
        SELECT *
        FROM   ( SELECT rownum rn, empno, ename
                 FROM emp
               ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may

even help in the odd situation.
How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
        SELECT *
        FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
        WHERE  ROWNUM < 10;
Use this workaround with prior releases:
        SELECT *
          FROM my_table a
         WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                        FROM my_table b
                       WHERE b.maxcol >= a.maxcol)
         ORDER BY maxcol DESC;
How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also,

this feature is not often found in other database offerings.
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This

table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number

of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of

up to 255 levels. Look at this example:
        select  LEVEL, EMPNO, ENAME, MGR
          from  EMP
        connect by prior EMPNO = MGR
          start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and

concatenate that to the string. Look at this example:
        select lpad(' ', LEVEL * 2) || ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting

condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables.

The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this

programmatically is difficult as one has to do the top level query first, then, for each of the records open a

cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and

the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table

for later retrieval.
How does one code a matrix report in SQL?
Look at this example query with sample output:
        SELECT  *
        FROM  (SELECT job,
                      sum(decode(deptno,10,sal)) DEPT10,
                      sum(decode(deptno,20,sal)) DEPT20,
                      sum(decode(deptno,30,sal)) DEPT30,
                      sum(decode(deptno,40,sal)) DEPT40
                 FROM scott.emp
                GROUP BY job)
        ORDER BY 1;

        JOB           DEPT10     DEPT20     DEPT30     DEPT40
        --------- ---------- ---------- ---------- ----------
        ANALYST                    6000
        CLERK           1300       1900        950
        MANAGER         2450       2975       2850
        PRESIDENT       5000
        SALESMAN                              5600
How does one implement IF-THEN-ELSE in a select statement?
The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or

columns based on the values of other columns in the select statement.
Some examples:
        select decode(sex, 'M', 'Male',
                           'F', 'Female',
                           'Unknown')
        from   employees;

        select a, b, decode( abs(a-b), a-b, 'a > b',
                                       0,   'a = b',
                                            'a < b')
        from  tableX;

        select decode( GREATEST(A,B), A, 'A is greater than B', 'B is greater than A')...
Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good

things about Oracle, but use it sparingly if portability is required.
From Oracle 8i one can also use CASE statements in SQL. Look at this example:
        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END
        FROM   emp;
How can one dump/ examine the exact content of a database column?

        SELECT DUMP(col1)
        FROM tab1
        WHERE cond1 = val1;

        DUMP(COL1)
        -------------------------------------
        Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for

a space. This tells us that this column is blank-padded.



Can one drop a column from a table?
From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE

table_name DROP COLUMN column_name; command.
With previous releases one can use Joseph S. Testa's DROP COLUMN package that can be downloaded from

http://www.oracle-dba.com/ora_scr.htm.
Other workarounds:
1. SQL> update t1 set column_to_drop = NULL;
   SQL> rename t1 to t1_base;
   SQL> create view t1 as select <specific columns> from t1_base;

2. SQL> create table t2 as select <specific columns> from t1;
   SQL> drop table t1;
   SQL> rename t2 to t1;
Can one rename a column in a table?
No, this is listed as Enhancement Request 163519. Some workarounds:
1. -- Use a view with correct column names...
   rename t1 to t1_base;
   create view t1 <column list with new name> as select * from t1_base;

2. -- Recreate the table with correct column names...
   create table t2 <column list with new name> as select * from t1;
   drop table t1;
   rename t2 to t1;

3. -- Add a column with a new name and drop an old column...
   alter table t1 add ( newcolame datatype ); 
   update t1 set newcolname=oldcolname;
   alter table t1 drop column oldcolname;
How can I change my Oracle password?
Issue the following SQL command: ALTER USER <username> IDENTIFIED BY <new_password>
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type

"password user_name".
How does one find the next value of a sequence?
Perform an "ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence numbers from the Oracle library cache.

This way, no cached numbers will be lost. If you then select from the USER_SEQUENCES dictionary view, you will see

the correct high water mark value that would be returned for the next NEXTVALL call. Afterwards, perform an "ALTER

SEQUENCE ... CACHE" to restore caching.
You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that

would cause gaps in sequence values.
Workaround for snapshots on tables with LONG columns
You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one

location to another. Eg:
COPY TO SCOTT/TIGER@REMOTE     -
CREATE IMAGE_TABLE USING       -
       SELECT IMAGE_NO, IMAGE  -
       FROM   IMAGES;
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.

PL/SQL - Interview Question Collections

What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.



Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database Proprietary to Oracle and difficult to port to other database systems Data manipulation is slightly faster in PL/SQL than in Java Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle Incurs some data conversion overhead between the Database and Java type systems Java is more difficult to use (depending on your background)



How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
        SELECT OBJECT_NAME,
               TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,
               TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
               STATUS
        FROM   USER_OBJECTS
        WHERE  LAST_DDL_TIME > '&CHECK_FROM_DATE';



How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
        SELECT TYPE, NAME, LINE
        FROM   USER_SOURCE
        WHERE  UPPER(TEXT) LIKE '%&KEYWORD%';



How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:
        CREATE TABLE SOURCE_HIST                     -- Create history table
          AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
             FROM   USER_SOURCE WHERE 1=2;

        CREATE OR REPLACE TRIGGER change_hist        -- Store code in hist table
               AFTER CREATE ON SCOTT.SCHEMA          -- Change SCOTT to your schema name
        DECLARE
        BEGIN
          if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                          'PACKAGE', 'PACKAGE BODY', 'TYPE') then
             -- Store old code in SOURCE_HIST table
             INSERT INTO SOURCE_HIST
                SELECT sysdate, user_source.* FROM USER_SOURCE
                WHERE  TYPE = DICTIONARY_OBJ_TYPE
                  AND  NAME = DICTIONARY_OBJ_NAME;
          end if;
        EXCEPTION
          WHEN OTHERS THEN
               raise_application_error(-20000, SQLERRM);
        END;
        /
        show errors



How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
    wrap iname=myscript.sql oname=xxxx.plb



Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
    set serveroutput on
    begin
       dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
    end;
    /
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.



Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
    DECLARE
      fileHandler UTL_FILE.FILE_TYPE;
    BEGIN
      fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
      UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
      UTL_FILE.FCLOSE(fileHandler);
    EXCEPTION
      WHEN utl_file.invalid_path THEN
         raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
    END;
    /



Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
    begin
       EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
    end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.



Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
    EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

    -- Using bind variables...
    sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

        -- Returning a cursor...
    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
    CREATE OR REPLACE PROCEDURE DYNSQL AS
      cur integer;
      rc  integer;
    BEGIN
      cur := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
      rc := DBMS_SQL.EXECUTE(cur);
      DBMS_SQL.CLOSE_CURSOR(cur);
    END;
    /
More complex DBMS_SQL example using bind variables:
    CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
      v_cursor integer;
      v_dname  char(20);
      v_rows   integer;
    BEGIN
      v_cursor := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
      DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
      DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
      v_rows := DBMS_SQL.EXECUTE(v_cursor);
      loop
        if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
           exit;
        end if;
        DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
        DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
      end loop;
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    EXCEPTION
      when others then
           DBMS_SQL.CLOSE_CURSOR(v_cursor);
           raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
    END;
    /



What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
    DECLARE
       v_EmpRecord  emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
    DECLARE
       v_EmpNo  emp.empno%TYPE;



What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
    declare
      a number := NULL;
      b number := NULL;
    begin
      if a=b then
         dbms_output.put_line('True, NULL = NULL');
      elsif a<>b then
         dbms_output.put_line('False, NULL <> NULL');
      else
         dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
      end if;
    end;



How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
    i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
    select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel



Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.



How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
    DECLARE
       CURSOR dept_cur IS
       SELECT deptno
         FROM dept
        ORDER BY deptno;
       -- Employee cursor all employees for a dept number
       CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
       SELECT ename
         FROM emp
        WHERE deptno = v_dept_no;
    BEGIN
       FOR dept_rec IN dept_cur LOOP
          dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
          FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
             dbms_output.put_line('...Employee is '||emp_rec.ename);
          END LOOP;
      END LOOP;
    END;
    /



How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
    FOR records IN my_cursor LOOP
       ...do some stuff...
       COMMIT;
    END LOOP;
... to ...
    FOR records IN my_cursor LOOP
       ...do some stuff...
       i := i+1;
       IF mod(i, 10000) THEN    -- Commit every 10000 records
          COMMIT;
       END IF;
    END LOOP;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.



I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
•    Grant direct access on the tables to your user. Do not use roles!
    GRANT select ON scott.emp TO my_user;
   
•    Define your procedures with invoker rights (Oracle 8i and higher);
•    Move all the tables to one user/schema.



What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
•    A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
•    A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
•    Etc.



Can one pass an object/table as an argument to a remote procedure?
The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example:
    -- Database A: receives a PL/SQL table from database B
    CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
    BEGIN
       -- do something with TabX from database B
       null;
    END;
    /

    -- Database B: sends a PL/SQL table to database A
    CREATE OR REPLACE PROCEDURE pcalling IS
       TabX DBMS_SQL.VARCHAR2S@DBLINK2;
    BEGIN
       pcalled@DBLINK2(TabX);
    END;
    /



Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.



Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.



Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
    SQL> select * from dba_object_size where name = 'procedure_name';


PL

Interview Questions - SQL*Plus, PL/SQL, ORACLE Concepts

1.     What are the minimum priveleges required to be given to a newly created user?
2.     What is a synonym? Diff between a view and a synonym
3.     If Db is mounted but not open, which view can U access
4.     Contents of  a control file
5.     How to mirror a control file?
6.     What is a instance?
7.     Where does oracle log its errors?
8.     Which process does automatic recovery if an instance fails before writing into datafile but after issuing a commit statement?
9.     How can u maintain consistency across forms?
10.     How do constraints differ from DB triggers?
11.     If rollback statement has been dropped or corrupted( eg. By deleting the file in O.S ) what will hapen during startup of the instance? How can the situation be handled?
12.     What are the issues to be attended to when a tablespace has to be designed and created?
13.     When will you experience the following error - “Snapshot too old” How can it be attended?
14.     What is a mutating and a constraining table?
15.     How do you monitor locks in Oracle?
16.     If a user has locked a resource for a long time and is not using it, How will you handle the situation and release the resource?
17.     What are the differences bet a trigger and a stored procedure?
18.     What is a pl/sql table?
19.     What is a record datatype?
20.     What will happen when a table begin accessed by a stored procedure is dropped?
21.     What is table stripping?
22.     Which view will give the details of datafiles?
23.     Which default tablespace is created by ORACLE?
24.     How do you decide the size of the DB block ?
25.     How do you increase the DB block size?
26.     What are priveleges?
27.     What are tuning steps?
28.     What are the diagnostic tools available for Tuning?
29.     What are the advantages of stored procedures?
30.     If a user does have access rights on a table but has executable rights on a procedure accessing that table; what will happen?
31.     what will happen when importing a table with duplicates in it?
32.     What is a Instance?
33.     Can you have multiple instances of the same DB/
34.     Can you share bet two instances?
35.     What features are provided by Oracle for DB Audit Trail?
36.     How do you avoid using indexes?
37.     What is cluster? How is it stored?
38.     What is ananymous block?
39.     If a SQL statements are given one by one from the SQL prompt and if they are written in a file and executed then what will be the difference?
40.     Is it possible to use a index in SQL?
41.     What is the criteria for creating a index?
42.     Will droping a index and recreating it increase performance? If yes, Why?
43.     What is analyzing a table?
44.     Diff bet “Union” and “Union All”?
45.     Result of count(*) and count(field name) , DIFF?
46.     What are DCL statements?
47.     What is a exetent, segment?
48.     How many types of segments exists in Oracle?
49.     Can you create rollback segments and data segments explicitly?
50.     How are execptions raised in pl/sql?
51.     Implicit and explicit cursors, diff?
52.     Can you use DDL statements in PL/SQl?
53.     How is a SQL statement parsed?
54.     How can you delete duplicates in a table?
55.     what is the diff bet rowid and rownum?
56.     What are hierarchical queries? ( connect by prior)
57.     What is a transaction?
58.     What is a read lock?
59.     What are SQL statements you can use in case of read transcation?
60.     In case of a trigger firing another trigger and that in turn firing another and so on ; upto what level is this kind of nesting allowed ? ( ans. 32 )
61.     What is referential integrity?
62.     Normalisation and De-normalisation?
63.     Diff bet 2nd and 3rd normal form?
64.     Optimiser 1. Cost based 2. Rule based . Diff? Which is used by Oracle?
65.     When is index recreated  or remapped?
66.     Diff bet truncate and delete?
67.     Will a “After-delete” trigger fire when the table is truncated?
68.     What are the things you will keep in mind when writing SQL statement?
69.     In case of a join statement which should be the driving table? ( ans. With max number of records.)
70.     Upto what level can you nest blocks in PL/SQL ? ( ans. 200 depending on the stack status ).
71.     What are the multithreaded server  and how it is different from dedicated server .
72.     What are the new features incorporated in ORACLE 7.2 version.
73.     What are the various difference between ORACLE 6.0 ORCALE7.0.
74.     How many types of triggers U have used in your application.
75.     How are the various variables in Proc and how we are declaring it.
76.     What is meant by Indicator variable in Pro*C
77.     What is difference between SQLCA and ORACA.
78.     How You are trapping errors in Pro*C program
79.     What is difference between UserExits and Pro*C program
80.     What are the various difference in UNIX and WINDOWS95 Operating systems.
81.     How You are connecting to Oracle database and what is meant by connect string.
82.     What is meant by partition view.
83.     What are various triggers that You have extensively used in your application.
84.     What is Dynamic SQL.
85.     What are the backup procedures for oracle.
86.     What is meant by 2 phase commit.
87.     Which case designer you have used and what was the version of it.
88.     what is SQL loader.
89.     What are the various security provided by oracle.
90.     What is difference between content and stacked canvas
91.     Database triggers call database procedures and vice versa.
92.     You will define and raise  your own exceptions.
93.     many error codes you  can use to have your own error messages.