Oracle PL/SQL Functionalities

Few New DB and Pl/sql Functionalities, hope this may help while coding.

ð Using DBMS_SYSTEM.KSDWRT to write in Alert Log File.

Used to write messages to trace files and/or alert log use

DBMS_SYSTEM.KSDWRT

(

DEST NUMBER, – 1 = Trace File, 2 = Alert Log, 3 = Both

TST VARCHAR2 – Message

);

eg:

DBMS_SYSTEM.KSDWRT (1, ‘Output to trace file’);

DBMS_SYSTEM.KSDWRT (2, ‘Output to alert log’);

DBMS_SYSTEM.KSDWRT (3, ’Output to both’);

DBMS_SYSTEM.KSDWRT (2, Messages);

ð To Flush Cache in older release of oracle :-

ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME FLUSH_CACHE’;

ð An undocumented feature of Oracle :-

To Bypass the buffer cache and read straight from disk using direct path reads.

alter session set "_serial_direct_read"=true;

Note:- Causes a tablespace (9i) or fast object (10g+) checkpoint, so careful on busy OLTP systems.

ð Using the undocumented function SYS_OP_MAP_NONNULL:

This function has been around for a while, but is still undocumented as of the latest Oracle database release (10gR2). Being undocumented means that it should not be used in production code without proper testing, but it does not hurt to know that it exists and explore how it works. This function makes it possible to have NULL = NULL:

EDDIE@XE> SELECT sys_op_map_nonnull (NULL) FROM DUAL;

SY

--

FF

EDDIE@XE> SELECT 'hi there' FROM DUAL WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);

'HITHERE

--------

hi there

EDDIE@XE> SELECT NVL (name, 'I am null') col1, NVL (id, 1) col2 FROM test WHERE sys_op_map_nonnull(name) = sys_op_map_nonnull(name);

COL1 COL2

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

Deepak Deepak

I am null I am null

ð REVERSE Function:-

This function reverse’s the Word

Select reverse(‘DEEPAK’) from dual;

ð Using ROWTYPE in PL/SQL efficiently

CREATE TABLE CUSTOMERS (

id NUMBER,

name VARCHAR2(100),

birth DATE,

death DATE

)

PROCEDURE insert_customer IS

customer CUSTOMERS%ROWTYPE;

BEGIN

customer.id := 45;

customer.name := 'John Smith';

customer.birth := TO_DATE('1978/04/03', 'YYYY/MM/DD');

INSERT INTO CUSTOMERS VALUES customer; -- No need to insert values, we can directly pass rowtype.

END;