10g Features

Oracle 10g Features

1. Enhancements to Spool Command

Spool append

Ex:

>spool myspool.txt

>select * from emp;

spool off

>spool myspool.txt append           >spool myspool.txt

>select * from student;                   -- Replace the existing file.

2. Aggregate Functions Supported in Returning into Clause

Ø  variable a number

Ø  update emp set sal = sal * 1.1 returning avg(sal) into :a;

Ø  print :a

Ø  declare

a number;

begin

delete from emp where sal > 1000

returning avg(sal) into a;

dopl(‘Average is ‘||a);

end;

SQL & PL/SQL Regular Expressions:

Meta Character	Description	Example
*	Wild card matches zero or more characters	Ad* matches Ada,Adc,Adb but not match Acd,add
?,.	Wild card matches single character	A?c matches Aac,AAc,Adc but not matchBcc.ZZc
^	Wild card indicates beginning of line	^abc matches abch but does not match habc
$	Wild card indicated ending of line	Abc$ matches Abc but does not match Abcadd
[  ]	Allows matching of any char found in braces	A[a-c] matches Aab,Acb but not matches Adb,aaa
{ m }	Allows a match for m times	Myname[0-9]{3} matches with myname9,myname99, myname222 Not matches  mname2222
{m,n}	Matches atleast m times but not more than n times	Myname[0-9]{3,5} Matches with myname999,myname3333 Myname55555 But not matches myname2222222
\n	This is called Back Reference expression. Cause previous expression to be repeated n times	[aeiou]\2 matches aaron but not matches aron

Built_in Functions used to search for Regular Expressions:

1.   Regexp_like:  This is similar to like operator used to seach for  regular expressions

Regexp_like(search_string,pattern,[match option])

·         Search_string is the search value

·         pattern is regular expression to be used

limited to 512 bytes

·         Match option is literal text string that allows to change the matching behavior of function

Match Option

c	Case sensitive (default)
i	Case insensitive

Examples:

>desc test_expressions

 char_value varchar2(30)

 date_value date

Ø  select * from test_expressions;

abcd                 27-dec-07

abb                  27-dec-07

zzz                             27-dec-07

zbz                   30-jun-07

zabz                 30-jun-07

aaron               30-jun-07

>select * from test_expressions

where regexp_like(char_value, ‘^ab*’);

abcd                 27-dec-07

abb                  27-dec-07

>select * from test_expressions

where regexp_like(char_value,’^.b.’);

abcd                 27-dec-07

abb                  27-dec-07

zbz                   30-jun-07