Working with REF Cursors – With Examples

A ref cursor is a variable, defined as a cursor type, which points to, or references a cursor result. A cursor variable which is based on REF CURSOR data type can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Examples:

REF CURSOR WITH %TYPE:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  en employee.ename%TYPE;
BEGIN
  OPEN c_employee FOR select ename from employee;
  LOOP
      FETCH c_employee INTO en;
      EXIT WHEN c_employee%NOTFOUND;
      dbms_output.put_line(en);
  END LOOP;
  CLOSE c_employee;
END;

REF CURSOR WITH %ROWTYPE:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  er employee%ROWTYPE;
BEGIN
  OPEN c_employee FOR select * from employee;
  LOOP
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.ename || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
END;

REF CURSOR WITH RECORD TYPE:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  TYPE rec_emp IS RECORD
  (
    name       VARCHAR2(40),
    position   VARCHAR2(40)
  );
  er rec_emp;
BEGIN
  OPEN c_employee FOR select ename,position from employee;
  LOOP
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
END;

REF CURSOR WITH MULTIPLE QUERIES:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  TYPE rec_emp IS RECORD
  (
    name       VARCHAR2(40),
    position   VARCHAR2(40)
  );
  er rec_emp;
BEGIN
  OPEN c_employee FOR select ename,position from employee where deptname = ‘IT’;
  dbms_output.put_line('Department: IT');
  dbms_output.put_line('--------------');
  LOOP
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
  OPEN c_employee FOR select ename,position from employee where deptname = ‘Finance’;
  dbms_output.put_line('Department: Finance');
  dbms_output.put_line('--------------');
  loop
      fetch c_employee into er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
END;

Normal Cursor Vs. Ref Cursor:

Normal Cursor	REF Cursor
It is static in definition.	It may be dynamically opened or opened based on logic.
It can’t be returned to a client.	It can be returned to a client.
A normal cursor can be global.	A ref cursor cannot. (you cannot define them OUTSIDE of a procedure / function)
A normal cursor can’t be passed from subroutine to subroutine.	A ref cursor can be passed from subroutine to subroutine.

The performance of normal cursors is much more efficient than ref cursors and hence you should use ref cursor only when you absolutely have to.

Reference:

http://asktom.oracle.com

http://www.devshed.com

Playing with Dates in Oracle!

In and around Oracle Application, as a developer you need to play with Dates while writing your code. Playing with dates is fun, although sometimes we faced some challenging tasks. Therefore it is always helpful to go through this feature of Oracle in detail. I did the same and here is what I have found.

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY. Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.

Unlike other datatypes, DATE datatypes are bit complex. However, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.

Get the current date and time:

Often it is required to retrieve the current date and time in our code and use them. Many developers go with SYSDATE function, but Oracle Database now offers several other functions as well.

SELECT SYSDATE FROM DUAL;
--27-FEB-12 (You will get the DATE from Oracle Database Server)
SELECT CURRENT_DATE FROM DUAL;
--27-FEB-12 (You will get the Current DATE from Oracle Session Time Zone)
SELECT LOCALTIMESTAMP FROM DUAL;
--27-FEB-12 01.16.42.486809000 AM (You will get the TIMESTAMP from Oracle Session Time Zone)
SELECT SYSTIMESTAMP FROM DUAL;
--27-FEB-12 01.19.41.059413000 AM -06:00 (You will get the TIMESTAMP WITH TIME ZONE from Oracle Database Server)
SELECT CURRENT_TIMESTAMP FROM DUAL;
--27-FEB-12 01.20.57.839733000 AM AMERICA/CHICAGO (You will get the TIMESTAMP WITH TIME ZONE from Oracle Session Time Zone)

DATE Format:

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY”. Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format.

Guidelines

• The format model must be enclosed in single quotation marks and is case sensitive.
• The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.
• The names of days and months in the output are automatically padded with blanks.
• To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

Examples:

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL; -- Returns 2012/02/27
SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM DUAL; -- Returns February  27, 2012
SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012
SELECT TO_CHAR(SYSDATE, 'MON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012
SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012
SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012
SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY') FROM DUAL; -- Returns Monday, 27TH February 2012
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- Returns 2012-02-27 02:05:59
SELECT TO_CHAR(SYSDATE, 'FMDdspth "of" Month YYYY FMHH:MI:SS AM') FROM DUAL; -- Returns Twenty-Seventh of February 2012 02:06:36 AM
SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY','NLS_DATE_LANGUAGE=Spanish') FROM DUAL; -- Returns Lunes, 27TH Febrero 2012
SELECT TO_CHAR(SYSDATE, 'Day') TODAY_EN,
TO_CHAR(sysdate, 'Day', 'nls_date_language=Dutch') TODAY_DT
FROM DUAL; -- Returns Monday   	Maandag

TO_DATE & TO_TIMESTAMP Functions:

TO_DATE function converts a character string representing a date to a date value according to the fmt specified. If fmt is omitted, the format is DD-MON-YY. The nlsparams parameter has the same purpose in this function as in the TO_CHAR function for date conversion.

TO_DATE(char,[fmt],[nlsparams])

The DD-MON-YY format is usually used to insert a date value. If a date must be entered in a format other than the default format, for example, with another century, or a specific time, you must use the TO_DATE function.

Examples:

SELECT TO_DATE('2012/02/27', 'yyyy/mm/dd') FROM DUAL;
SELECT TO_DATE('022712', 'MMDDYY') FROM DUAL;
SELECT TO_DATE('20120227', 'yyyymmdd') FROM DUAL;
SELECT TO_DATE('February 27, 2012, 04:00 P.M.','Month dd, YYYY, HH:MI A.M.')FROM DUAL;

Similarly TO_TIMESTAMP used to convert char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples:

SELECT TO_TIMESTAMP('2012/FEB/27 04:12:34', 'YYYY/MON/DD HH:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP('February 27, 2012, 04:12:34 P.M.','Month dd, YYYY, HH:MI:SS A.M.')FROM DUAL;
SELECT TO_TIMESTAMP ('27-Feb-12 04:12:34.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

Arithmetic with Dates:

Here are 3 golden roles:

1. You can compare DATE values using the standard comparison operators such as =, !=, >, etc.
2. You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.
3. You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

Date Functions:

Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

• MONTHS_BETWEEN (date1, date2):

This function returns the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

• ADD_MONTHS(date, n):

This function adds n number of calendar months to date. The value of n must be an integer and can be negative.

• NEXT_DAY(date, ‘char’):

This function finds the date of the next specified day of the week (‘char’) following date. The value of char may be a number representing a day or a character string.

• LAST_DAY(date):

This function finds the date of the last day of the month that contains date.

• ROUND(date[,’fmt’]):

This function returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

• TRUNC(date[, ‘fmt’]):

This function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Examples:

SELECT SYSTIMESTAMP FROM DUAL;  --Returns 27-FEB-12 05.16.41.676947000 AM -06:00
SELECT MONTHS_BETWEEN(SYSDATE , TO_DATE('01-JAN-2012','DD-MON-YYYY')) FROM DUAL; --Returns months between Sysdate and '01-JAN-2012' (1.84580906511350059737156511350059737157)
SELECT ADD_MONTHS (SYSDATE, 1) FROM DUAL; -- Move ahead one month (27-MAR-12)
SELECT ADD_MONTHS (SYSDATE, -4) FROM DUAL; -- Move backward four months (27-OCT-11)
SELECT NEXT_DAY (SYSDATE, 'MONDAY') FROM DUAL;  -- Go to next Monday after today’s date (05-MAR-12)
SELECT LAST_DAY (SYSDATE) FROM DUAL;  -- Returns the last day of the month (29-FEB-12)
SELECT ROUND (SYSDATE, 'MONTH') FROM DUAL; --01-MAR-12
SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL; --01-FEB-12
SELECT ROUND (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT ROUND (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12
SELECT TRUNC (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12

EXTRACT Function:

An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation

The syntax of EXTRACT function is

EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

Example:

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --Returns 2012
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --Returns 2
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --Returns 27
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL; --Returns 11
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL; --Returns 34

Have  a nice Day!

FAQ’s in SQL & PL/SQL

FAQ’s in SQL & PL/SQL

 

What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

 

What are the components of a PL/SQL Block?

Declarative part

Executable part

Exception part

 

What are the datatypes a available in PL/SQL?

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

 

What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are:

I. Need not know about variable’s data type

ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

 

What is difference between % ROWTYPE and TYPE RECORD?

% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE RECORD is to be used whenever query returns columns of different table or views and variables.

 

Explain the two types of Cursors?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries.

User defined cursors are called Explicit Cursors. They can be declared and used.

 

What are the cursor attributes used in PL/SQL?

% ISOPEN – Used to check whether a cursor is open or not.

% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.

% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.

 

What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

 

What is the difference between implicit and explicit cursors?

An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.

 

What are the different types of joins available in Oracle?

Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.

Self Join: If comparison comes in a single table

Cartesian Join: When tables are joined without giving any join condition.

Inner Join: The resultant set includes all the rows that satisfy the join condition.

Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition.  The outer join operator Plus sign (+) will be included in the join condition.

 

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

 

What is an autonomous transaction?

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

 

What is the difference between View and Materialized view?

Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.

 

What is dynamic SQL?

Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.

 

Can you use COMMIT in a trigger?

Yes but by defining an autonomous transaction.

 

What is the difference between anonymous blocks and stored procedures?

Anonymous block is compiled only when called. Stored procedure is compiled and stored in database with the dependency information as well. Former is PL/SQL code directly called from an application. Latter is stored in database. Former has declare statement. Latter doesn’t.

 

What is a package spec and package body? Why the separation?

Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.

Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.

 

What is Correlated Subquery?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.

 

What is Sequence?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

 

What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

 

What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader: the data itself and the control file.

 

What is the use of CASCADE CONSTRAINTS?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Explain forward declaration used in functions?

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.

Forward declarations are required in one specific situation: mutual recursion.

 

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

 

What is the difference between Truncate and Delete Commands?

 

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

 

What is the Purpose of HAVING Clause?

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

 

What is INLINE View in SQL?

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

 

While creating a sequence, what does cache and nocache options mean?

With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

 

Does the view exist if the table is dropped from the database?

Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.

 

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

 

What types of index data structures can you have?

An index helps to faster search values in tables. The three most commonly used index-types are:

• B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
• Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only little space and is very fast. (However, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
• Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.

 

What is the difference between a “where” clause and a “having” clause?

“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.

 

Can a view be updated/inserted/deleted? If Yes – under what conditions?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

 

What is tkprof and how is it used?

The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

 

What is explain plan and how is it used?

The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

 

What are the Lock types?

Share Lock: It allows the other users for only reading not to insert or update or delete.

Exclusive Lock: Only one user can have the privileges of insert or update and delete of particular object, others can only read.

Update Lock: Multiple user can read, update delete .

 

What is Pragma EXECPTION_INIT? Explain the usage?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error.
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

 

What is Raise_application_error?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.

 

What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

 

What is the difference between Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

Function is a subprogram written to perform certain computations and return a single value.
Unlike subprograms packages cannot be called, passed parameters or nested.

 

How do you make a Function and Procedure as a Private?

Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.

 

What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.

Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.

Rollback – This statement is used to undo work.

 

What is the difference between DDL, DML and DCL structures?

DDL statements are used for defining data. Ex: Create, Alter, Drop, Truncate, Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving. Ex; Commit, Rollback, Savepoint.

 

What is the difference between the snapshot and synonym?

A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.

A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

 

What is the difference between data types char and varchar?

Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).

 

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.

 

Tell some new features in PL/SQL in 10g?

-Regular expression functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and   REGEXP_SUBSTR

-Compile time warnings

– Conditional compilation

– Improvement to native compilation

– BINARY_INTEGER made similar to PLS_INTEGER

– Implicit conversion between CLOB and NCLOB

– Improved Overloading

– New datatypes BINARY_FLOAT, BINARY_DOUBLE

– Global optimization enabled

– PLS_INTEGER range increased to 32bit

– DYNAMIC WRAP using DBMS_DDL

 

What is Overloading in PL/SQL?

Overloading is an oops concept (Object Oriented Programming). By using the same name we can write any number of Procedures or functions in a package but either number of parameters in the procedure/function must vary or parameter datatype must vary.

 

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.

 

What is Nested Table?

A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within main table, the nested table may contain multiple rows.  In a sense, it’s a way of storing a one-to many relationship within one table.

 

What is Varying Array?

A varying array is a set of objects, each with the same data types. The size of the array is limited when it is created. (When the table is created with a varying array, the array is a nested table with a limited set of rows). Varying arrays also known as VARRAYS, allows storing repeated attributes in tables.

 

Give some most often used predefined exceptions?

a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occurred)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)

 

Give some important Oracle supplied packages?

DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.

DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.

UTL_FILE: With this package, you can read from & write to Operating system files

UTL_HTTP: This package allows to make HTTP Requests directly from the database.

 

What is Instead Of Trigger?

This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.

 

What is the Sequence of Firing Database Triggers?

a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger

 

What is the Difference between PL/SQL Table & Nested Table?

PL/SQL Table: Index by Tables are not Stored in Database.
Nested Table: Nested Tables are Stored in Database as Database Columns.

 

What is the Difference between Nested Table & Varray?

Nested Table
a) This are Sparse
b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table

Varray
a) This are Dense
b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) These are Stored in Tablespaces

 

What are the various SQL Statements?

a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.

What is Rowid?

It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.

 

What is Partitioning?

It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning

What is a Cluster?

A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.

 

What is the Difference between Nested Subquery & Correlated Subquery?

Nested Subquery
a) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query
b) Outer query is driver by Inner Query

Correlated Subquery
a) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query
b) Inner Query is Driven by Outer Query

 

What is the Difference between Translate & Replace?

Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.

 

PL/SQL Collections

PL/SQL Collections

These are composite variables in PL/SQL and have internal components that you can treat as individual variables. You can pass these composite variables to subprograms as a parameters.

To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type.

• In a collection, the internal components are always of the same data type, and are called elements. You access each element by its unique subscript. Lists and arrays are classic examples of collections.
• In a record, the internal components can be of different data types, and are called fields. You access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

PL/SQL Collection Types:

PL/SQL has three collection types, whose characteristics are summarized below.

1] Associative array (or index-by table)

• Number of Elements: Unbounded
• Subscript Type: String or integer
• Dense or Sparse: Either
• Where Created: Only in PL/SQL block

2] Nested Table

• Number of Elements: Unbounded
• Subscript Type: Integer
• Dense or Sparse: Starts dense, can become sparse
• Where Created: Either in PL/SQL block or at schema level

3] Variable size Array (Varray)

• Number of Elements: Bounded
• Subscript Type: Integer
• Dense or Sparse: Always Dense
• Where Created: Either in PL/SQL block or at schema level

Note:

Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there are limits, but they are very high.

Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).

A collection that is created in a PL/SQL block is available only in that block. A nested table type or varray type that is created at schema level is stored in the database, and you can manipulate it with SQL statements.

A collection has only one dimension, but you can model a multidimensional collection by creating a collection whose elements are also collections.

Associative Arrays (Index-By Tables):

An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value.

DECLARE
  -- Associative array indexed by string:

     TYPE ODI_RUNS IS TABLE OF NUMBER  -- Associative array type
  INDEX BY VARCHAR2(64);

     odi_batsman_runs  ODI_RUNS;        -- Associative array variable
  i VARCHAR2(64);

  BEGIN
    -- Add new elements to associative array:

     odi_batsman_runs('Virender Sehwag')  := 7380;
     odi_batsman_runs('Ricky Ponting')    := 13082;
     odi_batsman_runs('Sachin Tendulkar') := 17629;

   -- Print associative array:

  i := odi_batsman_runs.FIRST;

  WHILE i IS NOT NULL LOOP
      DBMS_Output.PUT_LINE
  ('Total ODI Runs on Jan 2010 by ' || i || ' is ' || TO_CHAR(odi_batsman_runs(i)));
  i := odi_batsman_runs.NEXT(i);
    END LOOP;
    END;

Output:

Total ODI Runs on Jan 2010 by Ricky Ponting is 13082
Total ODI Runs on Jan 2010 by Sachin Tendulkar is 17629
Total ODI Runs on Jan 2010 by Virender Sehwag is 7380

• Like a database table, an associative array holds a data set of arbitrary size, and you can access its elements without knowing their positions in the array.
• An associative array does not need the disk space or network operations of a database table, but an associative array cannot be manipulated by SQL statements (such as INSERT and DELETE).
• An associative array is intended for temporary data storage.
• To make an associative array persistent for the life of a database session, declare the associative array (the type and the variable of that type) in a package, and assign values to its elements in the package body.

Nested Tables: 

A nested table is like a one-dimensional array with an arbitrary number of elements.

Within the database, a nested table is a column type that holds a set of values. The database stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. These subscripts give you array-like access to individual rows.

A nested table differs from an array in these important ways:

• An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
• An array is always dense (that is, it always has consecutive subcripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Variable-Size Arrays (Varrays):

A variable-size array (varray) is an item of the data type VARRAY. A varray has a maximum size, which you specify in its type definition. A varray can contain a varying number of elements, from zero (when empty) to the maximum size. A varray index has a fixed lower bound of 1 and an extensible upper bound. To access an element of a varray, you use standard subscripting syntax.

DECLARE
  TYPE nested_type IS TABLE OF VARCHAR2(30);
  TYPE varray_type IS VARRAY(5) OF INTEGER;
  v1 nested_type;
  v2 varray_type;
BEGIN
  v1 := nested_type('Shipping','Sales','Finance','Payroll');
  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
FOR i IN v1.FIRST .. v1.LAST
   LOOP
  DBMS_OUTPUT.PUT_LINE('Element #' || i || 'in the nested table = ' || v1(i));
   END LOOP;

FOR j IN v2.FIRST .. v2.LAST
   LOOP
  DBMS_OUTPUT.PUT_LINE('Element #' || j || 'in the varray = ' || v2(j));
   END LOOP;
END;

Nested tables Vs. Varrays:

• Nested tables are unbounded and are initially dense but can become sparse through deletions. Varrays are always bounded and never sparse.
• When stored in the database, the order and subscripts of Nested tables are not preserved while varrays keep their ordering and subscripts.
• Nested table data is stored in a separate store table, a system-generated database table while a  varray is stored as a single object in the database.

Autonomous Transactions in Oracle

Autonomous Transactions in Oracle

Autonomous Transaction is a new feature in ORACLE starting from 8i. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction.

Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. Changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to READ COMMITTED (which is the default).

The following types of PL/SQL blocks can be defined as autonomous transactions:

• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.

Any of the routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability):

Example of an autonomous transactions:

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;

Delete Duplicate Records in Oracle

Delete Duplicate Records in Oracle

There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

CREATE TABLE dup_test (
    Emp_Id VARCHAR2(5),
  Name VARCHAR2(15),
    Phone  NUMBER);

INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);

SELECT * FROM dup_test;

Use subquery to delete duplicate rows:

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
   dup_test A
WHERE
  a.rowid >
   ANY (
     SELECT
        B.rowid
     FROM
        dup_test B
     WHERE
        A.Emp_Id = B.Emp_Id
     AND
        A.Name = B.Name
     AND
        A.Phone = B.Phone
        );

Use analytics to delete duplicate rows:

You can also detect and delete duplicate rows using Oracle analytic functions:

DELETE FROM dup_test
WHERE ROWID IN
  (SELECT ROWID FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
    WHERE rnk>1);

Use another table to delete duplicate rows:

This is the simplest method to remove duplicity.

CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;

Use RANK to delete duplicate rows:

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

DELETE FROM dup_test where rowid in
  (
  select "rowid" from
  (select "rowid", rank_n from
  (select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
             from dup_test
             )
         )
     where rank_n > 1
);

The above methods are only standard methods. You can also use your own techniques to remove duplicate records.

Normalization / De-Normalization

Normalization:

It is the process of efficiently organizing data in a database. There are two goals of the normalization process:

• Eliminate redundant data (for example, storing the same data in more than one table).
• Ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.
Eliminate Redundant Data – If an attribute depends on only part of a multi-valued key, remove it to a separate table.
Eliminate Columns Not Dependent On Key – If attributes do not contribute to a description of the key, remove them to a separate table.
Isolate Independent Multiple Relationships – No table may contain two or more 1:n or n:m relationships that are not directly related.
Isolate Semantically Related Multiple Relationships – There may be practical constrains on information that justify separating logically related many-to-many relationships.

1st Normal Form (1NF):

Def: A table (relation) is in 1NF if

1. There are no duplicated rows in the table.
2. Each cell is single-valued (i.e., there are no repeating groups or arrays).
3. Entries in a column (attribute, field) are of the same kind.

Note:

• The order of the rows and columns are immaterial(of no importance).
• The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).

2nd Normal Form (2NF):

Def: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.

Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, “A table is in 2NF if it is in 1NF and if it has no partial dependencies.”

3rd Normal Form (3NF):

Def: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

Note: A transitive dependency is a type of functional dependency in which the value in a non-key field is determined by the value in another non-key field and that field is not a candidate key.

Boyce-Codd Normal Form (BCNF):

Def: A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

4th Normal Form (4NF):

Def: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies. A multi-valued dependency occurs when, for each value in field A, there is a set of values for field B and a set of values for field C but fields B and C are not related.

5th Normal Form (5NF):

Def: A table is in 5NF, also called “Projection-Join Normal Form” (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain-Key Normal Form (DKNF):

Def: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.

De-Normalization:

Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply Denormalization in the process of deriving a physical data model from a logical form.

All about triggers

All about triggers

Triggers in Oracle:

A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. The triggers executes implicitly whenever a particular event takes place.

Types of Triggers:

1] Application Triggers:

Application triggers execute implicitly whenever a particular data manipulation language (DML) event occurs within an application (ex. Oracle Forms, Oracle Reports)

2] Database Triggers:

These triggers are fired whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database.

There are four types of database triggers:

• Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event.
• View-level triggers define what can be done to the view.
• Database-level triggers can be activated at startup and shutdown of a database.
• Session-level triggers can be used to store specific information.

Benefits of Database triggers:

• Improved data security:

          – Provide enhanced and complex security checks

          – Provide enhanced and complex auditing

• Improved data integrity:

         – Enforce dynamic data integrity constraints

         – Enforce complex referential integrity constraints

         – Ensure that related operations are performed together implicitly

Creating DML Triggers:

A triggering statement contains:

1] Trigger timing:

BEFORE: Execute the trigger body before the triggering DML event on a table.

AFTER: Execute the trigger body after the triggering DML event on a table.

INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

2] Triggering event:

Triggering user event tells which DML statement causes the trigger to execute? You can use any of the following:

• INSERT

• UPDATE

• DELETE

3] Trigger type:

Trigger type tells should the trigger body execute for each row the statement affects or only once?

• Statement: The trigger body executes once for the triggering event. This is the default. A statement trigger fires once, even if no rows are affected at all.

• Row: The trigger body executes once for each row affected by the triggering event. A row trigger is not executed if the triggering event affects no rows.

4] Trigger body:

Trigger body tells what action should the trigger perform? The trigger body is a PL/SQL block or a call to a procedure.

PL/SQL Trigger Execution Hierarchy:

The following hierarchy is followed when a trigger is fired.

1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This event will alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

Syntax of Triggers:

The Syntax for creating a trigger is:

 CREATE [OR REPLACE ] TRIGGER trigger_name
 {BEFORE | AFTER | INSTEAD OF }
 {INSERT [OR] | UPDATE [OR] | DELETE}
 [OF col_name]
 ON table_name
 [REFERENCING OLD AS o NEW AS n]
 [FOR EACH ROW]
 WHEN (condition)
 BEGIN
   --- sql statements
 END;

 

CREATE [OR REPLACE ] TRIGGER trigger_name – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
[OF col_name] – This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
CREATE [OR REPLACE ] TRIGGER trigger_name – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
[ON table_name] – This clause identifies the name of the table or view to which the trigger is associated.
[REFERENCING OLD AS o NEW AS n] – This clause is used to reference the old and new values of the data being changed. By default, you reference the values as : old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
[FOR EACH ROW] – This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
WHEN (condition) – This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Example of DML Statement Trigger:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
    (TO_CHAR(SYSDATE,'HH24:MI')
    NOT BETWEEN '08:00' AND '18:00')
    THEN RAISE_APPLICATION_ERROR (-20500,'You may
                  insert into EMPLOYEES table only during business hours.');
END IF;
END;

 
What are conditional predicates?
When creating a database trigger, several triggering events can be combined into one by using the INSERTING, UPDATING, and DELETING conditional predicates within the trigger body. In this way, a user can create a trigger that runs a different code based on the type of the triggering statement that fires the trigger. For example, conditional predicates can be used to create a database trigger that restricts all data manipulation events (INSERT, UPDATE, DELETE, etc.) on a table to certain business hours Monday through Friday.
Example of using conditional predicates in triggers:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')

THEN

IF DELETING THEN
RAISE_APPLICATION_ERROR (-20502,'You may delete from
EMPLOYEES table only during business hours.');

ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into
EMPLOYEES table only during business hours.');

ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR (-20503,'You may update
SALARY only during business hours.');

ELSE
RAISE_APPLICATION_ERROR (-20504,'You may update
EMPLOYEES table only during normal hours.');

END IF;

END IF;
END;

 
What are OLD and NEW qualifiers?
The OLD and NEW qualifiers are used to reference the values of a column before and after the data change, respectively.
The OLD and NEW qualifiers can be used only with row triggers. They cannot be used with statement triggers. The OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL and PL/SQL statement except when they are referenced in a WHEN restricting clause.
Example of using OLD and NEW qualifiers:
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees

FOR EACH ROW

BEGIN

     INSERT INTO audit_emp_table (user_name, timestamp,
     id, old_last_name, new_last_name, old_title,
     new_title, old_salary, new_salary)
     VALUES (USER, SYSDATE, :OLD.employee_id,
     :OLD.last_name, :NEW.last_name, :OLD.job_id,
     :NEW.job_id, :OLD.salary, :NEW.salary );
END;

 
INSTEAD OF trigger:
The Oracle INSTEAD-OF trigger has the ability to update normally non-updateable views. Simple views are generally updateable via DML statements issued against the view. However, when a view becomes more complex it may lose its “updateable-ness,” and the Oracle INSTEAD-OF trigger must be used.
INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has INSTEAD OF triggers, then the triggers take preference. In other words, the database fires the triggers instead of performing DML on the view.
Restrictions on INSTEAD OF Triggers

INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.

Example of INSTEAD OF trigger:
In this example, an order_info view is created to display information about customers and their orders:
CREATE VIEW order_info AS
   SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
   FROM customers c, orders o
   WHERE c.customer_id = o.customer_id;

Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.
CREATE OR REPLACE TRIGGER order_info_insert
	INSTEAD OF INSERT ON order_info
DECLARE
	  duplicate_info EXCEPTION;
 	 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
 	 INSERT INTO customers
   	 (customer_id, cust_last_name, cust_first_name)
 	 VALUES (
 	:new.customer_id,
 	:new.cust_last_name,
  	:new.cust_first_name);

	INSERT INTO orders (order_id, order_date, customer_id)
	VALUES (
  	:new.order_id,
  	:new.order_date,
  	:new.customer_id);

EXCEPTION
  	WHEN duplicate_info THEN
    	RAISE_APPLICATION_ERROR (
     	num=> -20107,
      	msg=> 'Duplicate customer or order ID');

END order_info_insert;

You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):
INSERT INTO order_info VALUES
   (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
 
Difference between Database Triggers and Stored Procedures:
Database Triggers:

Database triggers are defined with CREATE TRIGGER statement.
The data dictionary that contains the source code is USER_TRIGGERS.
They are implicitly invoked
COMMIT, SAVEPOINT, and ROLLBACK are not allowed.
We cannot pass parameters to Database triggers and they cannot return a value.

Stored Procedures:

Stored Procedures are defined with CREATE PROCEDURE statement.
The data dictionary that contains the source code is USER_SOURCE.
They are explicitly invoked.
COMMIT, SAVEPOINT, and ROLLBACK are allowed.
We can pass parameters to Stored procedures and they can return a value.

How to know Information about Triggers:
We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.
The below statement shows the structure of the view ‘USER_TRIGGERS’
 DESC USER_TRIGGERS; 

TRIGGER_NAME — Name of the trigger

TRIGGER_TYPE  — The type is BEFORE, AFTER, INSTEAD OF

TRIGGER_EVENT — The DML operation firing the trigger

TABLE_OWNER   — Owner of the table

BASE_OBJECT_TYPE  — Object type

TABLE_NAME    — Name of the database table

COLUMN_NAME – Name of the Column

REFERENCING_NAMES  — Name used for :OLD and :NEW

WHEN_CLAUSE     — The when_clause used

STATUS           — The status of the trigger

DESCRIPTION  — Description

ACTION_TYPE  — Type of action

TRIGGER_BODY — The action to take
We can see PL/SQL Syntax errors (compilation errors) in the ‘USER_ERRORS’ data dictionary view
Managing triggers:

Disable or reenable a database trigger:

             ALTER TRIGGER trigger_name DISABLE | ENABLE

Disable or reenable all triggers for a table:

             ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

Recompile a trigger for a table:

           ALTER TRIGGER trigger_name COMPILE

To remove a trigger from the database, use the DROP TRIGGER syntax:

           DROP TRIGGER trigger_name;
Oracle mutating trigger table errors:
The Oracle mutating trigger error occurs when a trigger references the table that owns the trigger, resulting in the “ORA-04091: table name is mutating, trigger/function may not see it.” message.
How to solve it?
Don’t use triggers – The best way to avoid the mutating table error is not to use triggers.  While the object-oriented Oracle provides “methods” that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
Use an “after” trigger – If you must use a trigger, it’s best to avoid the mutating table error by using an “after” trigger. For example, using a trigger “:after update on xxx”, the original update has completed and the table will not be mutating.
Use autonomous transactions – You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.


				

Exception Handling in Oracle

Exception Handling in Oracle

What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.

An exception is an identifier in PL/SQL that is raised during the execution of a block that terminates its main body of actions. A block always terminates when PL/SQL raises an exception, but can you specify an exception handler to perform final actions.

Types of Exception

There are 3 types of Exceptions.

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Named System Exceptions (or Predefined Oracle Server Exceptions) and Unnamed System Exceptions (or Nonpredefined Oracle Server Exceptions) are implicitly raised.

User-defined Exceptions are explicitly raised.

Structure of Exception Handling

DECLARE
   Declaration section
BEGIN
   Exception section
EXCEPTION
  WHEN ex_name1 THEN
     -Error handling statements
  WHEN ex_name2 THEN
     -Error handling statements
  WHEN Others THEN
     -Error handling statements
END;

 

You can trap any error by including a corresponding routine within the exception handling section of the PL/SQL block. Each handler consists of a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception is raised.
The exception-handling section traps only those exceptions that are specified; any other exceptions are not trapped unless you use the OTHERS exception handler.
Exceptions Trapping Rules:

Begin the exception-handling section of the block with the EXCEPTION keyword.
You can define several exception handlers, each with its own set of actions, for the block.
When an exception occurs, PL/SQL processes only one handler before leaving the block.
Place the OTHERS clause after all other exception-handling clauses.
WHEN OTHERS is the last clause and you can have only one OTHERS clause.

a) Named System Exceptions 
System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.
Note: PL/SQL declares predefined exceptions in the STANDARD package.
Few Predefined Exceptions:

NO_DATA_FOUND (ORA-01403) — When a SELECT…INTO clause does not return any row from a table.
TOO_MANY_ROWS (ORA-01422) — When you SELECT or fetch more than one row into a record or variable.
ZERO_DIVIDE (ORA-01476) — When you attempt to divide a number by zero.
CURSOR_ALREADY_OPEN (ORA-06511) — You tried to open a cursor that is already open.
INVALID_CURSOR (ORA-01001) — Illegal cursor operation occurred. You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before Opening the cursor.
INVALID_NUMBER (ORA-01722) — You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
DUP_VAL_ON_INDEX (ORA-00001) — Attempted to insert a duplicate value.
LOGIN_DENIED (ORA-01017) — You tried to log into Oracle with an invalid username/password combination.
NOT_LOGGED_ON (ORA-01012) — You tried to execute a call to Oracle before logging in.
VALUE_ERROR (ORA-06502) — You tried to perform an operation and there was an error on a conversion, truncation, or invalid constraining of numeric or character data.

Named system exceptions are:

Not declared explicitly.
Raised implicitly when a predefined Oracle error occurs.
Caught by referencing the standard name within an exception-handling routine.

For Example: 
BEGIN
  Execution section
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;

 

 
b) Unnamed System Exceptions
Those system exception for which oracle does not provide a name is known as unnamed system exception. These exceptions do not occur frequently. These Exceptions have a code and an associated message.
There are two ways to handle unnamed system exceptions:

1. By using the WHEN OTHERS exception handler, or

2. By associating the exception code to a name and using it as a named exception.
We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name.
Steps to be followed to use unnamed system exceptions are

They are raised implicitly.
If they are not handled in WHEN Others they must be handled explicitly.
To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT as given above and handled referencing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:
DECLARE
   exception_name EXCEPTION;
   PRAGMA
   EXCEPTION_INIT (exception_name, Err_code);
BEGIN
   Execution section
EXCEPTION
   WHEN exception_name THEN
     Handle the exception
END;

 

For Example: 
Let’s trap for Oracle server error number –2292, which is an integrity constraint violation.
1] Declare the name for the exception within the declarative section.
exception EXCEPTION;
Where: exception is the name of the exception.
2.] Associate the declared exception with the standard Oracle server error number using the
PRAGMA EXCEPTION_INIT statement.
PRAGMA EXCEPTION_INIT(exception, error_number);
Where: exception is the previously declared exception.
error_number is a standard Oracle Server error number.
3] Reference the declared exception within the corresponding exception-handling routine.
DEFINE p_deptno = 10
DECLARE
     e_emps_remaining EXCEPTION;
     PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292);
BEGIN
     DELETE FROM departments
     WHERE department_id = &p_deptno;
     COMMIT;
EXCEPTION
     WHEN e_emps_remaining THEN
     DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||
     TO_CHAR(&p_deptno) || '. Employees exist. ');
END;

 

 
c) User-defined Exceptions
Apart from system exceptions we can explicitly define exceptions based on business rules. These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:

• They should be explicitly declared in the declaration section.

• They should be explicitly raised in the Execution Section.

• They should be handled by referencing the user-defined exception name in the exception section.
For Example:
DECLARE
     e_invalid_department EXCEPTION;
BEGIN
    UPDATE departments
    SET department_name = '&p_department_desc'
    WHERE department_id = &p_department_number;
    IF SQL%NOTFOUND THEN
      RAISE e_invalid_department;
    END IF;
    COMMIT;
EXCEPTION
    WHEN e_invalid_department THEN
    DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

 

 
Functions for Trapping Exceptions:
When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or message, you can decide which subsequent action to take based on the error.
• SQLCODE: Returns the numeric value for the error code. 
• SQLERRM: Returns the message associated with the error number.
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:
DECLARE
    err_num NUMBER;
    err_msg VARCHAR2(100);
BEGIN
    ...
EXCEPTION
    ...
  WHEN OTHERS THEN
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM, 1, 100);
    INSERT INTO errors VALUES (err_num, err_msg);
END;

 

 
RAISE_APPLICATION_ERROR ( ) :
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,

a) to create a unique id for an user-defined exception.

b) to make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message); 
 

• The Error number must be between -20000 and -20999

• The Error_message is the message you want to display when the error occurs.
RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The returned error is consistent with how the Oracle server produces a predefined, nonpredefined, or user-defined error. The error number and message is displayed to the user.
Executable section:
BEGIN
...
DELETE FROM employees
WHERE manager_id = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR (-20202,'This is not a valid manager');
END IF;
...

 

 
Exception section:
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.');
END;

 

 
What is Exception propagation in Oracle? 
Exceptions which are not handled in a sub block get propagated to the outer block. When an exception occurs, it terminates from the line where the exception occurs and the control goes to the calling program or the next outer block. If not handled in the outer block, it terminates that block and propagates to the next outer block and so on. And, if exception occurs in the outermost block, then the whole program gets terminated.


				

Few Interesting SQL Queries

Few Interesting SQL Queries

Here I have listed few interesting and useful SQL queries. Have fun! If you know more, you can share also.

1] Check Database / instance & machine you are using.

SQL> select name from v$database;

SQL> select instance_name from v$instance;

SQL> select * from global_name; 

2] Retrieve a random number using sql statement.

SQL> select dbms_random.random from dual;

3] Copy a table from one instance to another instance.

SQL> copy from apps/apps@vip replace xxtest1 or create xxtest1 using select * from xxtest1;

4] Check if your oracle applications instance is setup for Multi-Org ? 

SQL> select multi_org_flag from fnd_product_groups;

5] Check what patches have been already loaded.

SQL> select * from AD_APPLIED_PATCHES;

6] Check the database name.

SQL> select value from v$system_parameter where name = ‘db_name’;

7] Check the data Dictionary (includes all views and tables of the database).

SQL> select * from dictionary;

8] Check the definition data from a specific table (in this case, all tables with string “XXX”)

SQL> select * from ALL_ALL_TABLES where upper(table_name) like ‘%XXX%’;

9] Check the tables from actual user.

SQL> select * from user_tables;

10] Check all the objects of the connected user.

SQL> select * from user_catalog;

11] Check the Oracle products installed and version number.

SQL> select * from product_component_version;

12] Check the roles and roles privileges.

SQL> select * from role_sys_privs;

13] Check the integrity rules.

SQL> select constraint_name, column_name from sys.all_cons_columns;

14] Check all the tablespaces.

SQL> select * from V$TABLESPACE;

15] Oracle SQL query to know the database size.

SQL> select sum(BYTES)/1024/1024 MB from DBA_EXTENTS;