REGEXP

REGEXP_LIKE

SELECT * FROM scott.emp WHERE REGEXP_LIKE(ENAME,'FR') ;
OR
SELECT * FROM scott.emp WHERE ENAME LIKE '%FR%'

-------------------

SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');
OR
SELECT testcol FROM test WHERE testcol like 'Steven%' or testcol like 'Stephen%'



parameters can be a combination of (Match Options)
* i: to match case insensitively
* c: to match case sensitively
* n: to make the dot (.) match new lines as well
* m: to make ^ and $ (Anchoring Characters) match beginning and end of a line in a multiline string

If you want all the entries that start with S, search for ^s

End with R, use r$

Start with S and end with H, use ^s.*h$

Start with S or end with R, use ^s|r$

All 4 letter names, use ^….$

Contains B, C, D or K, use [b-d,k]

All names with double letters, use (.)\1

Posix Characters
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting),such as carriage return,newline,vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters

Quantifier Characters
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times

Alternative Matching And Grouping Characters
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations,for quantifiers,or for back referencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - meta characters

SELECT * FROM scott.emp WHERE REGEXP_LIKE(ENAME,'^FR$') ;
or
SELECT * FROM scott.emp WHERE ENAME LIKE 'FR'


SELECT REGEXP_REPLACE('FYICenter.com', '*.com','i') FROM DUAL;
or
SELECT REGEXP_REPLACE ('FYICenter.com', '^*.com$','i') FROM DUAL;



Here we search for strings that have either an "a", "c", or "f" as the second character of the string.
select REGEXP_REPLACE( 'paddy', '^.[acf]' ) from dual

SELECT REGEXP_REPLACE ('FYICenter.com', '^f.*$','i') FROM DUAL;

SELECT REGEXP_REPLACE ('FYICenter.com', '^Y.*$','i') FROM DUAL;

SELECT REGEXP_REPLACE ('FYICenter.com', '*Y.*$','i') FROM DUAL;


SELECT cust_email old_email,REGEXP_REPLACE(cust_email,'@.*\.COM','@BIRDS.COM') new_email FROM oe.customers;


SELECT REGEXP_REPLACE('H1234 H4321 H2345 H2345','(.*) (.*) (.*) (.*)','\4 ,\3, \2, \1')FROM dual;

Instead of Commit

Usually after a DML statement to make the change permenantly in the database we should execute commit statement .

Example

update scott.emp t where t.JOB='ff' where EMPNO=7654 ;

commit;

If we execute a DML statement and then DDL statement then without executing commit statement the effect of DML statement will save permenantly in the database

Example

update scott.emp t where t.JOB='ff' where EMPNO=7654 ;

create table test(test1 number);

Pinning in M/R // Background Process

Pinning stored procedure/function to shared pool in 11G

execute dbms_shared_pool.keep(owner.trigger, 'R');

---------------------------------------------

Script displays instance background process information. The script works when the database is MOUNTed or OPENed.

select A.SID,A.SERIAL#,A.PROGRAM,P.PID,P.SPID,A.OSUSER, /* Who Started INSTANCE */A.TERMINAL,
A.MACHINE,A.LOGON_TIME,B.NAME,B.Description,P.PGA_USED_MEM,P.PGA_FREEABLE_MEM,P.PGA_MAX_MEM
from v$session A,v$process P,v$bgprocess B where A.PADDR=B.PADDR AND A.PADDR=P.ADDR and
A.type='BACKGROUND';

ESCAPE With LIKE Operator

ESCAPE With LIKE Operator

select ename from scott.emp where ename like '%_%';

But you will be surprised to see the results:

ENAME
------------
Will dispaly all Data in the Table

it is because _ is a wild card character. That is _ stands for any character. So the query yielded all the rows.

modified query to get the desired output as below:


select ename from scott.emp where ename like '%#_%' escape '#';

ENAME
------------
FRA_KLIN

About TNS file

What is PRESENTATION=RO in tnsnames.ora file

Check the entry in tnsnames.ora file:

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

In this article we will be discussing about the PRESENTATION clause in the entry.

Little history
The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database).

The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc.

Back to EXTPROC_CONNECTION_DATA
But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared libraries (DLLs).

PRESENTATION in connect descriptor
There must be a presentation layer between client and server, if in case the charactersets of both are different. This layer ensures that information sent from within application layer of one system is readable by application layer of the other system.

The various presentation layer options available are
1. Two-Task Common (TTC)
2. JavaTTC
3. FTP
4. HTTP
5. GIOP (for IIOP)
6. IMAP
7. POP
8. IM APSSL (6, 7, and 8 are for email) etc
9. RO

TTC
TTC/Two-Task Common is Oracle's implementation of presentation layer. It provides characterset and datatype conversion between different charactersets or formats on the client and server. This layer is optimized on a per connection basis to perform conversion only when required.

JavaTTC
This is a Java implementation of TTC for Oracle Net foundation layer capable of providing characterset and datatype conversion.

It is responsible for
a. Negotiating protocol version and datatype
b. Determining any conversions
c. SQL statement execution

RO
For external procedures the PRESENTATION layer value will be normally RO, meaning for "Remote Operation". By this parameter the application layer knows that a remote procedure call (RPC) has to be made.