ORACLE LEARNINGS

Oracle ANYDATA.ACCESSXXXX Built-ins

2009-07-29T07:33:00.003+05:30

In my previous article ANYDATA, we have seen how to store a value in a table column or PL/SQL variable of type ANYDATA. Now we will move further to know, how to access the stored values.

Similar to ANYDATA.ConvertXXXX built-ins, Oracle has provided ANYDATA.AccessXXXX built-ins to read the values of ANYDATA column or Variable.

The below example will show how to use AccessXXXX built-in with table column and PL/SQL variable.
Ex:
SELECT ANYDATA.AccessDate(id) from tab_anydata.
PL/SQL Block:
DECLARE
v_any ANYDATA;
BEGIN
v_any:= ANYDATA.convertNumber(1001);
DBMS_OUTPUT.PUT_LINE('Value in V_ANY variable is : ' v_any.AccessNumber());
v_any:=ANYDATA.convertDate('01-Jul-09');
DBMS_OUTPUT.PUT_LINE('Value in V_ANY variable is : ' v_any.AccessDate());
END;

Below are the list of Access built-ins, that are available with Oracle 9i.
MEMBER FUNCTION AccessBDouble(self IN ANYDATA) return BINARY_DOUBLE
MEMBER FUNCTION AccessBfile(self IN ANYDATA) return BFILE
MEMBER FUNCTION AccessBFloat(self IN ANYDATA) return BINARY_FLOAT
MEMBER FUNCTION AccessBlob(self IN ANYDATA) return BLOB
MEMBER FUNCTION AccessChar(self IN ANYDATA) return CHAR
MEMBER FUNCTION AccessClob(self IN ANYDATA) return CLOB
MEMBER FUNCTION AccessDate(self IN ANYDATA) return DATE
MEMBER FUNCTION AccessIntervalYM(self IN ANYDATA) return INTERVAL YEAR TO MONTH
MEMBER FUNCTION AccessIntervalDS(self IN ANYDATA) return INTERVAL DAY TO SECOND
MEMBER FUNCTION AccessNchar(self IN ANYDATA) return NCHAR
MEMBER FUNCTION AccessNClob(self IN ANYDATA) return NCLOB
MEMBER FUNCTION AccessNumber(self IN ANYDATA) return NUMBER
MEMBER FUNCTION AccessNVarchar2(self IN ANYDATA) return NVARCHAR2
MEMBER FUNCTION AccessRaw(self IN ANYDATA) return RAW
MEMBER FUNCTION AccessTimestamp(self IN ANYDATA) return TIMESTAMP
MEMBER FUNCTION AccessTimestampLTZ(self IN ANYDATA) return TIMESTAMP WITH LOCAL
MEMBER FUNCTION AccessTimestampTZ(self IN ANYDATA) return TIMESTAMP WITH
MEMBER FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC
MEMBER FUNCTION AccessVarchar(self IN ANYDATA) return VARCHAR
MEMBER FUNCTION AccessVarchar2(self IN ANYDATA) return VARCHAR2
Note: We don't have AccessXXXX built-in to read the values of Object, Collection and REF type.

I will explain you on how to read the Object, Collection and REF values stored in ANYDATA variable, in my next article.

ANYDATA.ConvertXXXX Built-ins - 9i New Features

2009-07-22T20:20:00.000+05:30

Whenever we need to pass the value for a database table column of type ANYDATA or to store a variable in PL/SQL block of type ANYDATA, we need to use the ANYDATA.CONVERTXXXX built-in, where XXXX will be the data type on which the value will be stored in ANYDATA column/variable.

Ex:

INSERT INTO tab_anydataVALUES(ANYDATA.convertNumber(1001));

INSERT INTO tab_anydata VALUES(ANYDATA.convertDate('01-Jul-09'));

PL/SQL :

DECLARE

v_any ANYDATA;

BEGIN

v_any:= ANYDATA.convertNumber(1001);

v_any:=ANYDATA.convertDate('01-Jul-09');

END;

Below are the list of convert built-ins, that are available with Oracle 9i.

STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return ANYDATA,

STATIC FUNCTION ConvertBfile(b IN BFILE) RETURN ANYDATA,

STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return ANYDATA,

STATIC FUNCTION ConvertBlob(b IN BLOB) RETURN ANYDATA,

STATIC FUNCTION ConvertChar(c IN CHAR) RETURN ANYDATA,

STATIC FUNCTION ConvertClob(c IN CLOB) RETURN ANYDATA,

STATIC FUNCTION ConvertCollection(col IN "collection_type") RETURN ANYDATA,

STATIC FUNCTION ConvertDate(dat IN DATE) RETURN ANYDATA,

STATIC FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return ANYDATA,

STATIC FUNCTION ConvertIntervalYM(invIN INTERVAL YEAR TO MONTH) return ANYDATA,

STATIC FUNCTION ConvertNchar(nc IN NCHAR) return ANYDATA,

STATIC FUNCTION ConvertNClob(nc IN NCLOB) return ANYDATA,

STATIC FUNCTION ConvertNumber(num IN NUMBER) RETURN ANYDATA,

STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return ANYDATA,

STATIC FUNCTION ConvertObject(obj IN "<object_type>") RETURN ANYDATA,

STATIC FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA,

STATIC FUNCTION ConvertRef(rf IN REF "<object_type>") RETURN ANYDATA,

STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return ANYDATA,

STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP WITH TIMEZONE) return ANYDATA,

STATIC FUNCTION ConvertTimestampLTZ(ts IN TIMESTAMP WITH LOCAL TIMEZONE) return ANYDATA,

STATIC FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA,

STATIC FUNCTION ConvertVarchar(c IN VARCHAR) RETURN ANYDATA,

STATIC FUNCTION ConvertVarchar2(c IN VARCHAR2) RETURN ANYDATA,

Thus by using the above mentioned functions, values of different data types could be stored in column or variable of type ANYDATA .

So far we have seen, how to create a column or variable of type ANYDATA and to pass/store the values to it.

I will give the information on how to access the column or variable of type ANYDATA in my next article.

Key: ORACLE, ANYDATA, ANYDATA.ACCESS BUILT-INS, 9i New Features

ANYDATA - Oracle's Road To Generic Data Types

2009-07-21T22:05:00.000+05:30

ANYDATA is a new data type introduced with Oracle 9i. As the name implies, it allows the user to store values of any data type (Oracle Defined/User defined) in it.

ANYDATA data type, could be used for defining a column in Database Table or to declare a variable in PL/SQL block.

EX:

CREATE TABLE tab_anydata (id ANYDATA);

PL/SQL Block:

DECLARE

v_any ANYDATA;

BEGIN

NULL;

END;

Built-ins to work with ANYDATA.

convertXXXX
accessXXXX
getXXXX
getTypeName
getType

You will come to learn about the above specified built-ins in my succeeding articles on ANYDATA data type.

Key: ORACLE, ANYDATA, 9i New Features, Generic Data Types

Basics Of Tablespace, Data files, Extents And Blocks

2008-07-23T16:19:00.003+05:30

Understanding The Basic Terminologies: Tablespaces, Extents, and Blocks

An Oracle database can contain many files. But you don't directly choose which file to store your data in. Instead, you specify which tablespace to use. A tablespace is simply a logical grouping of files.

To discover the tablespaces in your database, query dba_tablespaces:

SELECT tablespace_name

FROM dba_tablespaces.

Some common tablespaces in Oracle databases include

SYSTEM for Oracle's meta data,

TEMP tablespace for temporary objects and

RBS for rollback segments .

To discover which files are used for each tablespace, execute the following SQL:

SELECT file_name,tablespace_name

FROM dba_data_files;

When you create a table in a tablespace, Oracle allocates a chunk of space for the object. Each chunk is called an extent. As you will see, each extent can vary in size. An extent is itself made up of a number of blocks, the smallest unit of space . So a tablespace is a grouping of files that stores data in extents, of which each is made up of a number of blocks. Now that you hopefully understand the basic terminology involved, let's look at the various decisions one makes when allocating space in Oracle.

Allocating Space for Data

As I said above, the smallest unit of space in Oracle is the block. You can determine the size you want when you create a database in Oracle. Once you've created the database, however, you cannot change this setting. The sample sizes that Oracle provides in its configuration file are 2K for a small database, 8K for a medium-sized one, and 16K for a large one. A row can span more than one block. So if a row in a table takes up 12K and you have chosen an 8K-block size, 8K of the row will be stored in one block and the remaining 4K will be stored in a separate block. This is called row chaining. As you can imagine, it is more effective to fit most of your rows in one block.

Each extent is made up of a number of blocks. As a table grows, the initial extent that Oracle allocated can be used up. Oracle then allocates another extent for the table which again is made up of a number of blocks. In Oracle, you can specify the size of the initial extent and all subsequent extents. Typically, you specify these settings when you create a tablespace. Tables will use the storage settings of the tablespace they are created in. If necessary, however, you can override these tablespace settings by specifying different settings in the CREATE TABLE statement itself.

Creating a Tablespace

Let's create a tablespace with the initails (default) size of extent as 256K to start. If a table needs more space, I'll allocate it in sizes of 500K increments. However, because this tablespace is for smaller tables, I'll set the maximum number of extents to 20. If a table needs more space than that, I'll move it to another tablespace that is built for larger tables.

CREATE TABLESPACE TECHAEON

datafile 'd:\tablespaces\appl_data01.dbf' SIZE 50M,

'd:\tablespaces\appl_data02.dbf' SIZE 50M

DEFAULT STORAGE

(

INITIAL 256K

NEXT 500K

PCTINCREASE 0

MAXEXTENTS 20

)

Locally Managed Tablespaces: Autoallocate And Uniform Clause:

On a locally managed tablespace, you cannot specify the default storage settings. Instead, you have two additional settings that can be used to simplify space allocation. If you specify AUTOALLOCATE, Oracle will automatically handle the sizing for objects. If you specify the UNIFORM SIZE clause, however, Oracle creates all extents in the locally managed tablespace using size you give it.

Here's an example of two tablespaces that I've created using the above options:

create tablespace appl_data

datafile 'd:\tablespaces\appl_data01.dbf' size 50M,

‘d:\tablespaces\appl_data.dbf' size 50M

extent management local uniform size 512K

create tablespace large_acctdata

datafile 'd:\tablespaces\appl_data01.dbf' size 50M,

‘d:\tablespaces\appl_data.dbf' size 50M

extent management local autoallocate;

To see the settings on your tablespaces, you can execute the following query:

select tablespace_name, initial_extent,next_extent,

min_extents, max_extents, pct_increase,

extent_management,allocation_type

from dba_tablespaces

Purity Levels Of Function - PRAGMA RESTRICT_REFERENCES

2008-07-22T20:37:00.004+05:30

An Oracle database cannot determine the work done by a packaged function when the function is executed from inside a DML statement. Therefore, if packaged functions are to be executed from within a DML statement, developers must use a PRAGMA to define a purity level for functions defined as part of the package spec. A PRAGMA is a compiler directive that instructs the compiler to handle code in a specific manner. To define a purity level for a packaged function, the PRAGMA RESTRICT_REFERENCES is used.

A purity level defined within a package spec instructs Oracle about the kinds of operations that the function performs. Below are the four purity levels that can be defined for a function.

WNDS	Write No Database State. The function doesn’t alter the contents of any database table.
RNDS	Read No Database State. The function doesn’t read the contents of any database table.
WNPS	Write No Package State. The function doesn’t alter any variables within another package.
RNPS	Read No Package State. The function doesn’t read any variables within another package.

The following example shows, how a purity level could mentioned for a function, while defining the package specification. The example makes the function get_id to the WNDS purity level.

CREATE OR REPLACE PACKAGE P_TEST

FUNCTION GET_ID RETURN NUMBER;

PRAGME RESTRICT_REFERENCES(GET_ID,WNDS);

END;

Note: The purity level for the functions can also be defined for the functions used in an Oracle object type.

Oracle - USER_JOBS Data Dictionary

2008-05-28T13:27:00.002+05:30

The USER_JOBS Data Dictionary provides the details of all the jobs scheduled by the user. The following columns form the USER_JOBS table.

JOB

      Identifier of job. Neither import/export nor repeated executions change it.

   LOG_USER

      USER who was logged in when the job was submitted

   PRIV_USER

      USER whose default privileges apply to this job

   SCHEMA_USER

      select * from bar means select * from schema_user.bar

   LAST_DATE

      Date that this job last successfully executed

   LAST_SEC

      Same as LAST_DATE. This is when the last successful execution started.

   THIS_DATE

      Date that this job started executing (usually null if not executing)

   THIS_SEC

      Same as THIS_DATE. This is when the last successful execution started.

   NEXT_DATE

      Date that this job will next be executed

   NEXT_SEC

      Same as NEXT_DATE. The job becomes due for execution at this time.

   TOTAL_TIME

      Total wallclock time spent by the system on this job,in seconds

   BROKEN

      If Y,no attempt is being made to run this job.Will be ‘N’ for running jobs.

   INTERVAL

      A date function

   FAILURES

      How many times has this job started and failed since its last success?

   WHAT

      Body of the anonymous PL/SQL block that this job executes

   NLS_ENV

      alter session parameters describing the NLS environment of the job

   MISC_ENV

      a versioned raw maintained by the kernel

   INSTANCE

      Instance number restricted to run the job

Oracle - PL/SQL - ASCIISTR Function

2008-04-29T11:52:00.003+05:30

In Oracle/PLSQL, the asciistr function converts a string in any character set to an ASCII string using the database character set.

The syntax for the asciistr function is:

asciistr( string )

string is a string in any character set that you want converted to an ASCII string in the database character set.

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

For example:

asciistr('A B C Ä Ê')	would return 'A B C \00C4 \00CA'
asciistr('A B C Õ Ø')	would return 'A B C \00D5 \00D8'
asciistr('A B C Ä Ê Í Õ Ø')	would return 'A B C \00C4 \00CA \00CD \00D5 \00D8'

Oracle - PL/SQL - ASCII Function

2008-04-29T11:51:00.003+05:30

In Oracle/PLSQL, the ascii function returns the NUMBER code that represents the specified character.

The syntax for the ascii function is:

ascii( single_character )

single_character is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

ascii('t')	would return 116.
ascii('T')	would return 84.
ascii('T2')	would also return 84.

ORACLE - ASCII Table - A Quick reference

2008-04-29T11:31:00.004+05:30

Dec	Hex	Oct	Char	Description
0	0	000		null
1	1	001		start of heading
2	2	002		start of text
3	3	003		end of text
4	4	004		end of transmission
5	5	005		enquiry
6	6	006		acknowledge
7	7	007		bell
8	8	010		backspace
9	9	011		horizontal tab
10	A	012		new line
11	B	013		vertical tab
12	C	014		new page
13	D	015		carriage return
14	E	016		shift out
15	F	017		shift in
16	10	020		data link escape
17	11	021		device control 1
18	12	022		device control 2
19	13	023		device control 3
20	14	024		device control 4
21	15	025		negative acknowledge
22	16	026		synchronous idle
23	17	027		end of trans. block
24	18	030		cancel
25	19	031		end of medium
26	1A	032		substitute
27	1B	033		escape
28	1C	034		file separator
29	1D	035		group separator
30	1E	036		record separator
31	1F	037		unit separator
32	20	040		space
33	21	041	!
34	22	042	"
35	23	043	#
36	24	044	$
37	25	045	%
38	26	046	&
39	27	047	'
40	28	050	(
41	29	051	)
42	2A	052	*
43	2B	053	+
44	2C	054	,
45	2D	055	-
46	2E	056	.
47	2F	057	/
48	30	060	0
49	31	061	1
50	32	062	2
51	33	063	3
52	34	064	4
53	35	065	5
54	36	066	6
55	37	067	7
56	38	070	8
57	39	071	9
58	3A	072	:
59	3B	073	;
60	3C	074	<
61	3D	075	=
62	3E	076	>
63	3F	077	?

Dec	Hex	Oct	Char
64	40	100	@
65	41	101	A
66	42	102	B
67	43	103	C
68	44	104	D
69	45	105	E
70	46	106	F
71	47	107	G
72	48	110	H
73	49	111	I
74	4A	112	J
75	4B	113	K
76	4C	114	L
77	4D	115	M
78	4E	116	N
79	4F	117	O
80	50	120	P
81	51	121	Q
82	52	122	R
83	53	123	S
84	54	124	T
85	55	125	U
86	56	126	V
87	57	127	W
88	58	130	X
89	59	131	Y
90	5A	132	Z
91	5B	133	[
92	5C	134	\
93	5D	135	]
94	5E	136	^
95	5F	137	_
96	60	140	`
97	61	141	a
98	62	142	b
99	63	143	c
100	64	144	d
101	65	145	e
102	66	146	f
103	67	147	g
104	68	150	h
105	69	151	i
106	6A	152	j
107	6B	153	k
108	6C	154	l
109	6D	155	m
110	6E	156	n
111	6F	157	o
112	70	160	p
113	71	161	q
114	72	162	r
115	73	163	s
116	74	164	t
117	75	165	u
118	76	166	v
119	77	167	w
120	78	170	x
121	79	171	y
122	7A	172	z
123	7B	173	{
124	7C	174
125	7D	175	}
126	7E	176	~
127	7F	177	DEL

Oracle - PL/SQL - TO_CHAR Function

2008-04-28T14:21:00.003+05:30

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9')	would return '1210.7'
to_char(1210.73, '9,999.99')	would return '1,210.73'
to_char(1210.73, '$9,999.00')	would return '$1,210.73'
to_char(21, '000099')	would return '000021'

Examples - Dates

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

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.

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions

Question: Why doesn't this sort the days of the week in order?

select ename, hiredate, to_char((hiredate),'fmDay') "Day"
from emp
order by "Day";

Answer:

In the above SQL, the fmDay format mask used in the to_char function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmDD format mask as follows:

select ename, hiredate, to_char((hiredate),'fmDD') "Day"
from emp
order by "Day";

Oracle - PL/SQL - DECODE Function

2008-04-28T13:58:00.003+05:30

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id,	10000,	'IBM',
	10001,	'Microsoft',
	10002,	'Hewlett Packard',
		'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

The decode function will compare each supplier_id value, one by one.

Sample Scenario :

Question: How to use the decode function to compare two dates (ie: date1 and date2), where if date1 > date2, the decode function should return date2. Otherwise, the decode function should return date1.

Answer: To accomplish this, use the decode function as follows:

decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

The formula below would equal 0, if date1 is greater than date2:

(date1 - date2) - abs(date1 - date2)

Question: Is it possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id,
decode(trunc ((supplier_id - 1) / 10),	0,	'category 1',
	1,	'category 2',
	2,	'category 3',
		'unknown') result
FROM suppliers;

In this example, based on the formula:

trunc ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.

and so on...

Question: I need to write a decode statement that will return the following:

If yrs_of_service < 1 then return 0.04
If yrs_of_service >= 1 and < 5 then return 0.04
If yrs_of_service > 5 then return 0.06

How can I do this?

Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges.

For example:

SELECT emp_name,
decode(trunc (( yrs_of_service + 3) / 4),	0,	0.04,
	1,	0.04,
		0.06) as perc_value
FROM employees;

Oracle - PL/SQL - TO_DATE Function

2008-04-28T13:23:00.003+05:30

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

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
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
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. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

to_date('2003/07/09', 'yyyy/mm/dd')	would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY')	would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd')	would return a date value of Mar 15, 2002.

PL/SQL- COURSOR WITHIN A CURSOR

2008-04-25T12:35:00.004+05:30

Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Answer: Below is an example of how to declare a cursor within a cursor.

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

create or replace procedure MULTIPLE_CURSORS_PROC is
v_owner varchar2(40);
v_table_name varchar2(40);
v_column_name varchar2(100);

/* First cursor */
cursor get_tables is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';

/* Second cursor */
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;

begin

-- Open first cursor
open get_tables;
loop
fetch get_tables into v_owner, v_table_name;

open get_columns;
loop
fetch get_columns into v_column_name;

end loop;
close get_columns;

end loop;
close get_tables;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - 'SQLCODE' -ERROR- 'SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

Oracle - Report 6i - Basics

2008-04-24T14:05:00.006+05:30

1) About reports

A report consists of objects that collectively define the report:
1)data model objects (queries, groups, columns, links, user parameters)
2)layout objects (repeating frames, frames, fields, boilerplate, anchors)
3)parameter form objects (parameters, fields, boilerplate)

2) Report Styles
2.1)About tabular reports
A tabular report is the most basic type of report. Each column corresponds to acolumn selected from the database.
2.2)About group above reports
A group above report contains multiple groups in its data model.
It is a "master/detail" report, where there may be a lot of information in the master group.
For every master group, the related values of the detail group(s) are fetched from thedatabase and are displayed below the master information.
2.3)About group left reports
A group left report also contains multiple groups in its data model, dividing the rowsof a table based on a common value in one of the columns.
Use this type of report to restrict a column from repeating the same value several times while values of relatedcolumns change.
The data model for group above and group left reports is the same,but the layouts differ; group above reports display the master information at the topwhile group left reports display break columns to the side.
2.4)About form-like reports
A form-like report displays one record per page, displaying field values to the right offield labels.
2.5)About form letter reports
A form letter report contains database values embedded in boilerplate text (any textthat you enter or import into a Report Editor.)
2.6)About mailing label reports
A mailing label report prints mailing labels in multiple columns on each page. Usingthe Report Wizard, you can specify the format for your mailing labels.
2.7)About matrix reports
A matrix (cross-product) report is a cross-tabulation of four groups of data:
One group of data is displayed across the page. One group of data is displayed down the page. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations. One group of data is displayed as the"filler" of the cells.
Thus, to create a matrix report, you need at least four groups in the data model: onegroup must be a cross-product group, two of the groups must be within thecross-product group to furnish the "labels", and at least one group must provide theinformation to fill the cells. The groups can belong to a single query or to multiplequeries.

3)About triggers
Triggers check for an event.

When the event occurs they run the PL/SQL code associated with the trigger.

Report triggers are activated in response to report events such as the report openingand closing rather that the data that is contained in the report. They are activated in apredefined order for all reports.

Format triggers are executed before an object is formatted. A format trigger can be usedto dynamically change the formatting attributes of the object.

Validation triggers are PL/SQL functions that are executed when parameter values arespecified on the command line and when you accept the Runtime Parameter Form.

Database triggers are procedures that are stored in the database and implicitly executedwhen a triggering statement such as INSERT, UPDATE, or DELETE is issued against theassociated table.

3.1)About report triggers
Report triggers execute PL/SQL functions at specific times during the execution andformatting of your report.

Using the conditional processing capabilities of PL/SQL forthese triggers, you can do things such as customize the formatting of your report,perform initialization tasks, and access the database.

To create or modify a report trigger, you use the Report Triggers node in the Object Navigator.
Report triggers must explicitly return TRUE or FALSE.

Oracle Reports has five global report triggers.

The trigger names indicate at what point the trigger fires:

3.1.1)Before Report trigger: Fires before the report is executed but after queries areparsed.

3.1.2)After Report trigger: Fires after you exit the Paper Design view, or after reportoutput is sent to a specified destination, such as a file, a printer, or an e-mail ID.

This trigger can be used to clean up any initial processing that was done, such asdeleting tables.

Note, however, that this trigger always fires, whether or not yourreport completed successfully.

3.1.3)Between Pages trigger: Fires before each page of the report is formatted, except thevery first page.
This trigger can be used for customized page formatting.

In the Paper Design view, this trigger only fires the first time that you go to a page.
If you subsequently return to the page, the trigger does not fire again.

3.1.4)Before Parameter Form trigger: Fires before the Runtime Parameter Form isdisplayed. From this trigger, you can access and change the values of parameters,PL/SQL global variables, and report-level columns.

If the Runtime Parameter Form is suppressed, this trigger still fires.
Consequently, you can use this trigger for validation of command line parameters.

3.1.5)After Parameter Form trigger: Fires after the Runtime Parameter Form is displayed.From this trigger, you can access parameters and check their values.

This trigger can also be used to change parameter values or, if an error occurs, return to theRuntime Parameter Form.

Columns from the data model are not accessible from this trigger.

If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires.Consequently, you can use this trigger for validation of command line parameters or other data.

4)Order of report trigger execution
The order of events when a report is executed is as follows:

1. Before Parameter Form trigger is fired.

2. Runtime Parameter Form appears (if not suppressed).

3. After Parameter Form trigger is fired (unless the user cancels from the Runtime
Parameter Form).

4. Report is "compiled".

5. Queries are parsed.

6. Before Report trigger is fired.

7. SET TRANSACTION READONLY is executed (if specified with the READONLY command line keyword or setting).

8. The report is executed and the Between Pages trigger fires for each page except the first one. COMMITs can occur during this time due to: SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.

9. COMMIT is executed (if READONLY is specified) to end the transaction.

10. After Report trigger is fired.

11. COMMIT/ROLLBACK/NOACTION is executed based on what was specifiedwith the ONSUCCESS command line keyword or setting.

Ramya Vivek

Oracle - Complete list of PL/SQL Data Types

2008-04-24T11:40:00.003+05:30

Character Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where *size* is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where *size* is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where *size* is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where *size* is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

Numeric Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

Date/Time Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	*year precision* is the number of digits in the year. (default is 2)	*year precision* is the number of digits in the year. (default is 2)	*year precision* is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	*day precision* must be a number between 0 and 9. (default is 2) *fractional seconds precision* must be a number between 0 and 9. (default is 6)	*day precision* must be a number between 0 and 9. (default is 2) *fractional seconds precision* must be a number between 0 and 9. (default is 6)	*day precision* must be a number between 0 and 9. (default is 2) *fractional seconds precision* must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)

Large Object (LOB) Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
bfile	Maximum file size of 4GB.	Maximum file size of 2³²-1 bytes.	Maximum file size of 2⁶⁴-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores unicode data.

Rowid Datatypes

Data Type
Syntax

Oracle 9i

Oracle 10g

Oracle 11g

Explanation
(if applicable)

rowid

The format of the rowid is: BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

The format of the rowid is: BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

The format of the rowid is: BBBBBBB.RRRR.FFFFF

Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

Fixed-length binary data. Every record in the database has a physical address or rowid.

urowid(size)

Universal rowid.

Where size is optional.

Oracle Database 11g Top New Features:

2008-01-19T11:46:00.002+05:30

Oracle Database 11g Top New Features:
Database Replay
Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.
Transaction Management
Get an introduction to Flashback Data Archive and explore Enterprise Manager's Log Miner interface.
SQL Plan Management
Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.
SQL Access Advisor
Get advice about optimal table design based on actual use of the table, not just data.-->
RMAN
Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs.
Automatic Storage Management
Learn about new SYSASM role, variable extent sizes, and other ASM improvements
Schema Management
Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.
SQL Performance Analyzer
Accurately assess the impact of rewriting of SQL statements and get suggested improvements. --> -->
Manageability
Explore automatic memory management, multicolumn statistics, online patching, and more features.
PL/SQL: Efficient Coding
Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.

About : Trigger Creation

2008-01-17T16:46:00.001+05:30

Hi,

Try to answer the following question.

Is it possible to create a trigger using “CREATE OR REPLACE TRIGGER TRIGGER_NAME”, where the trigger_name is already being used by another trigger under some other table ?. If your answer is YES, then this article may help you.

In general we used to say that, if we create a trigger using Create or replace statement, then the trigger will be altered with the new trigger code. But if use CREATE OR REPLACE statement to create a trigger and if the trigger name is already being used by a trigger under another table, then ORACLE will raise an ORA-04095 exception.

The following code will give you some more clarity on the above explanation:

Table 1 Creation :

create table trig1(id number(3));

Trigger 1 creation for the above table :

create or replace trigger trg_trig1

before insert on trig1

begin

null;

end;

Table 2 Creation :

create table trig1(id number(3));

Trigger 2 creation for the above table :

create or replace trigger trg_trig1

before insert on trig2

begin

null;

end;

create or replace trigger trg_trig1

ERROR at line 1:

ORA-04095: trigger 'TRG_TRIG1' already exists on another table, cannot replace

Explaination :

Thus the above code creates two table called trig1 and trig2. Trig1 has a before insert trigger called trg_trig1.Then we have the code for trg_trig1 trigger on trig2. Oracle doesn’t allow you to create the trigger by raising ORA-04095 exception.