Sunday, October 30, 2011

Working with Oracle Directory Object

In many situations it is necessary for a session to read or write operating system files. Oracle offers two ways for doing this.

  • UTL_FILE package
  • Oracle directories 

UTL_FILE package contains many PL/SQL procedures for manipulating directory objects. Operating system directories that can be accessed by UTL_FILE are only those listed in UTL_FILE_DIR instance parameter. This parameter default to NULL but can be set to any comma separated list as well as * (wildcard) which means any directory in operating system.

SQL > SELECT   NAME, VALUE
            FROM     V$PARAMETER
            WHERE    UPPER(NAME) = 'UTL_FILE_DIR'

Result
---------------------------------------------------------------------------------------------------
utl_file_dir             NULL

All the directories listed will be visible to all sessions. To give a value for UTL_FILE_DIR parameter file use the following command.

SQL > sqlplus / as sysdba
SQL > ALTER SYSTEM SET UTL_FILE_DIR = 'C:\TEST' SCOPE = SPFILE
SQL > SELECT   NAME, VALUE
            FROM     V$PARAMETER
            WHERE    UPPER(NAME) = 'UTL_FILE_DIR'

Result
---------------------------------------------------------------------------------------------------
utl_file_dir             C:\TEST


Notice the use of SCOPE = SPFILE: it is a static parameter and change can not take effect without restarting the instance.

Oracle directories give a layer of abstraction between the user and the operating system: DBA create a directory within the database, which points to a physical path. Permissions on these Oracle directories can then be granted to individual users. This gives much more finely grained access control than the UTL_FILE_DIR parameter. Oracle does not check if the directory exists or not. Having created the directory read and write permission should be granted to ORACLE users. Directories are always own by SYS user despite who created them.

SQL > GRANT CREATE ANY DIRECTORY TO HR
SQL > CREATE DIRECTORY TEST AS 'C:\Test'
SQL > GRANT READ ON DIRECTORY TEST TO PUBLIC
SQL > GRANT WRITE ON DIRECTORY TEST TO SCOTT

Saturday, October 8, 2011

Understanding TIME INTERVAL Data Types in Oracle

Time Interval data types are part of Oracle database since Oracle 9i. Before, there is no way to store intervals between two dates in such easy way. Designers have to convert the interval into seconds and store that in a numeric format.

INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are two data types added to store interval of two dates. They can be use both in SQL and PL/SQL. The syntax is as below:

INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

There are defaults for precision. Two digest for year and day and six digest for fractional seconds.

SQL>   SELECT INTERVAL '5-9' YEAR TO MONTH FROM DUAL
Result: +05-09
Cover a time interval of 5 years and 9 months

SQL>   SELECT INTERVAL '7 15:20:07.7' DAY TO SECOND FROM DUAL
Result: +07 15:20:07.700000
Cover a time interval of 7 days, 15 hours, 20 minutes, 7 seconds and 7 milliseconds.

SQL>   SELECT INTERVAL '7 15:20:07.7' DAY TO SECOND(1) FROM DUAL
Result: +07 15:20:07.7
Cover a time interval of 7 days, 15 hours, 20 minutes, 7 seconds and 7 milliseconds.

Intervals can be positive or negative.

Below is another example that create a table with interval data types, insert some records in it, and show up the results.

CREATE TABLE EG_TEST_INTERVAL
(
       ID NUMBER,
       TIME1 INTERVAL YEAR TO MONTH,
       TIME2 INTERVAL DAY TO SECOND
);

INSERT INTO EG_TEST_INTERVAL VALUES (1, INTERVAL '5-9' YEAR TO MONTH, INTERVAL '7 15:20:07.7' DAY TO SECOND);
INSERT INTO EG_TEST_INTERVAL VALUES (2, INTERVAL '-5-9' YEAR TO MONTH, INTERVAL '-7 15:20:07.7' DAY TO SECOND);

COMMIT;

SELECT * FROM EG_TEST_INTERVAL;

Results: 1 +05-09 +07 15:20:07.700000
             2 -05-09  -07 15:20:07.700000