OracleVillage

INTERVAL YEAR TO MONTH data type is normally used for data warehouse purpose. Sometime we want to create calculation that how long any employee has worked with ABC organization. For that,Given below is a very simple example to understand the working of a data type INTERVAL YEAR TO MONTH.

Consider a human resource department that maintains the record of their employees contract period.

CREATE TABLE EMP_CONTRACT
(
EMPNO INTEGER,
NAME VARCHAR2(30 BYTE),
DURATION INTERVAL YEAR(3) TO MONTH);

SET DEFINE OFF;
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7369, 'SMITH', TO_YMINTERVAL('+01-00'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7499, 'ALLEN', TO_YMINTERVAL('+03-00'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7521, 'WARD', TO_YMINTERVAL('+00-10'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7566, 'JONES', TO_YMINTERVAL('+01-03'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7654, 'MARTIN', TO_YMINTERVAL('+05-00'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7698, 'BLAKE', TO_YMINTERVAL('+07-00'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7782, 'CLARK', TO_YMINTERVAL('+01-08'));
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values
(7788, 'SCOTT', TO_YMINTERVAL('+06-02'));

COMMIT;

Also above thing can be done by this way
Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values(7934, 'MILLER', INTERVAL '0-5' YEAR TO MONTH);

Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values(7934, 'MILLER', INTERVAL '11' MONTH);

Insert into EMP_CONTRACT
(EMPNO, NAME, DURATION)
Values(7934, 'MILLER', INTERVAL '1' YEAR);


select * from emp_contract;

EMPNO NAME DURATION
------ ---------- ------------
7369 SMITH +001-00 (Means 1 year 0 months)
7499 ALLEN +003-00 (3 year 0 months)
7521 WARD +000-10 (0 year 10 months)
7566 JONES +001-03
7654 MARTIN +005-00
7698 BLAKE +007-00
7782 CLARK +001-08
7788 SCOTT +006-02
7839 KING +003-00
7844 TURNER +006-09
7876 ADAMS +006-00
7900 JAMES +002-00
7902 FORD +060-00
7934 MILLER +001-00

Hope it help you guys someway.

Consider modifying the Fetch_From_Cursor procedure to use bulk fetch, thus…

create or replace procedure Bulk_Fetch_From_Cursor
( p_cursor in sys_refcursor )

is
type names_t is table of varchar2(4000)
index by binary_integer;
the_names names_t;
begin
fetch p_cursor bulk collect into the_names;


for j in the_names.first..the_names.last
loop
Dbms_Output.Put_Line ( the_names(j) );
end loop;
end Bulk_Fetch_From_Cursor;
/
It can be invoked with a cursor variable which has been assigned using native dynamic SQL, thus…


declare
the_cursor sys_refcursor;
begin
open the_cursor for
'select last_name from employees order by last_name';

Bulk_Fetch_From_Cursor ( the_cursor );
close the_cursor;

open the_cursor for
'select department_name from departments order by department_name';
Bulk_Fetch_From_Cursor ( the_cursor );
close the_cursor;
end;
/

If this is attempted in a pre-Oracle9i environment (making appropriate substitution for sys_refcursor), then: either bulk fetch can be used when the cursor variable is assigned using static SQL; or explicit row by row fetch can be used when the cursor variable is assigned using native dynamic SQL. But the attempt to do bulk fetch when the cursor variable is assigned using native dynamic SQL causes “ORA-01001: invalid cursor”.

Cursor best practices
Asked by Ajendra Narayansamal from Unknown Location on Tuesday, March 8, 2005

Question:
I have two questions regarding best practices:

1. Which of the following methods is most efficient for a cursor:
a. Declaring a cursor
b. Using cursor FOR-LOOP
c. Bulk collect and then loop through the collection.

2. For best performance should I use capital letters for table names and keywords?

Answer:
Without a doubt, you should use BULK COLLECT whenever possible to query information from your tables. It will be significantly faster than either a cursor FOR loop or loop with explicit cursor. Be aware, however, that a BULK COLLECT of many rows can consume a large amount of memory. You can balance memory utilization against performance improvements by using the LIMIT clause with BULK COLLECT. Here's an example:

An Error FORALL?
Asked by Shira from Indianapolis on Wednesday, April 25, 2007

Question:
We have been just amazed at how much better our programs perform when we use FORALL to do our inserts and updates. We are now building a new application on Oracle Database 10g Release 2, and we have run into a problem. In all previous usages of FORALL, we would generally take a collection that was populated with a BULK COLLECT and push it into one or more tables. Now we have a more complicated scenario, in which we must go through our collection of "candidate" data for inserts and remove some (perhaps all) of the rows before doing the insert. When we try to use FORALL, we get this error message: ORA-22160: element at index [2750] does not exist How can we avoid this error and get all our data inserted?

Answer:
I agree that FORALL is wonderful—one of the most important enhancements to PL/SQL since Oracle8i was released. And back in Oracle8i and Oracle9i Database, it is true that the only format with which you could use FORALL was this:
For more details; visit site below:
http://htmldb.oracle.com/pls/otn/f?p=2853:4:3267864591257035::::P4_QA_ID:11902