Some insight on Oracle Exception Handling

Like some high level programming language oracle provide some pretty feature on exception handling. Like other high level language here you can catch specific exception and handle that accordingly. Here you can also create your own exception also.

Firstly, how to catch specific exception :-

Suppose for network problem one of your dblink face problem and generate Oracle Exception ORA-3135. To catch this exception, you should define an exception with this exception number and then use the given name to catch this exception. That is create an exception using following code -

connection_lost Exception;
PRAGMA EXCEPTION_INIT(connection_lost, -3135);

Then in Exception block write the following -

when connection_lost then
/*some statements*/

Secondly, create and raise custom error:

create custom exception by following command -
cust_exception Exception;
PRAGMA EXCEPTION_INIT(cust_exception, -20001);

then command to raise this exception -
RAISE cust_exception;

Then catching this exception is same as other exception. There is an overload of RAISE_application_error which takes a third bool parameter. This parameter determines whether they data will be put on empty stack or top of stack.

Thirdly, Log Exception from the stack:

To get maximum log of exception, call DBMS_UTILITY.FORMAT_ERROR_STACK function and log the output. It will give not only top of stack but also all content of the procedure error stack. Along with this use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to log line from which error is generated. It is available from Oracle 10g.

For more information visit following links -

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm

http://www.oracle-developer.net/display.php?id=318

Console/admin log in Windows server

Sometimes when you want to log in a windows server OS you may get an error like terminal exceeded maximum number of user. But you have to do some admin work on the windows server; suppose disconnecting one of this user, then what you have to do?

just open command prompt, execute remote desktop connection with the following command-

mstsc -v:IP_OF_SERVER /F -admin

Then give your user & password. This is so simple!!

One thing keep in mind every time, that is when exiting the server always use log off. If you do not log off then if disconnect create some problem (which I face several time) then you cannot log in again as admin/console.

How to optimize your PL/SQL code

To increase performance of your pl/sql, you should alter system plsql parameters. It is recommended to use separate parameter value for production and development. For development, the parameter are set to default. So for development plsql_code_type will be 'INTERPRETED' and plsql_optimize_level will be 2. For production environment, you should change the parameter in following way,

alter session set plsql_code_type = 'NATIVE';

alter session set plsql_optimize_level = 3;

One very important effect of setting these two parameters is that - without setting these parameters in such values, you cannot use your function in parallel query.

If your functions are deterministic, you can create function index to increase performance. Thus you can enhance plsql performance.

Interval partitioning : A great feature of oracle 11g

Table partitioning is very much useful for data warehousing. But one problem we face before 11g is have to create the partition manually and it is cumbersome work. If we forget to create partition, the ETL job will be failed.

From 11g, oracle enhance the range partitioning with the feature of auto partitioning. Now along with range partition you only have to mention the interval of partition and create a base partition. The syntax is as follows -

CREATE TABLE SALES_PART
(TIME_ID NUMBER,
REGION_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006'));

The violet lines are for interval and base partition. In this example, the partitions are created on daily basis. If want to create them on week basis, write INTERVAL (NUMTODSINTERVAL(7,'day')) and INTERVAL (NUMTOYMINTERVAL(1,'month')) for monthly basis.

The automatic created partitions have system generated name and hence not follow naming convention. So to alter partition name issue following sql command -

ALTER TABLE SALES_PART RENAME PARTITION sys_p22 TO p_third

Some fact about oracle listener, tns, service & SID

1. Service_Name and SID are two different things. Service name directly maps to service of database. SID is system identification. The difference will be apparent in RAC environment where SID will be instance name and service name will be database service. This difference will also come into picture if your service name contains domain name. For example, your service name is dw.tmib.net.bd but your SID is dw.
   
   
2. For OS authentication, ORACLE_SID should be set to instance name i.e. SID.
   
   
3. For Listener, SID and Service Name are interoperable.
   
   
4. tnsping command confirms that there is a listener which listens to host:port referred by the tns entry.
   
   
5. In sqlplus connect command, @tnsname refers to tns entry in tnsnames.ora file. tns entry always goes to listener.
   
   
6. If the listener is not running, from sqlplus of server the database can be still connected. In this case, it is recommended to set the ORACLE_SID variable. It is required not to use @tnsname.
   
   
7. In a tns entry, under the description several addresses can be added. This helps the fail over. If any host:port service fails then go to the next host:port.
   DW=
   (DESCRIPTION=
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=192.168.8.21)
   (PORT=1521)
   )
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=192.168.8.22)
   (PORT=1521)
   )
   (CONNECT_DATA=
   (SERVER=dedicated)
   (SID=dw2)
   )
   )
   So if 192.168.8.22:1521 fails to serve, the request will be forwarded to 192.168.8.1:1521

Object-Relational Features of Oracle

In my previous post, I showed how to create custom types and that was a type which is a collection of varchar2. You can also create a composite column which is composed by varchar2, number or something else.

For example here is a definition of a point type consisting of two numbers:

     CREATE TYPE PointType AS OBJECT (
        x NUMBER,
        y NUMBER
    );
    /

An object type can be used like any other type in further declarations of object-types or table-types. 
For instance, we might define a line type by:

    CREATE TYPE LineType AS OBJECT (
        end1 PointType,
        end2 PointType
    );
    /


Then, we could create a relation that is a set of lines with ``line ID's'' as:

    CREATE TABLE Lines (
        lineID INT,
        line   LineType
    );

Then you can insert into table Lines in following manner - 

    INSERT INTO Lines
        VALUES(27, LineType(
                        PointType(0.0, 0.0),
                        PointType(3.0, 4.0)
                   )
        );

Here are some other queries about the relation lines. Note that here the table alias is required
as it is treated as the object of a class.

    SELECT ll.line.end1.x, ll.line.end1.y
    FROM Lines ll;

Suppose you want to load data from a flat table to this object relational table. Here is the procedure -

     CREATE TABLE LinesFlat(
        id INT,
        x1 NUMBER,
        y1 NUMBER,
        x2 NUMBER,
        y2 NUMBER
    );

   INSERT INTO Lines
        SELECT id, LineType(PointType(x1,y1), PointType(x2,y2))
        FROM LinesFlat;


Fore more detail, please visit this site. It is really nice.

Split Function with Regular Expression in Oracle 10g

Few days before, I was looking for split function in oracle (like java/C#), but did not find any. Then at first I find that Oracle 10g introduce regular expression support for string operation. You can visit http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm to know about this.

Then I utilize oracle pipelined function (to know more you can visit http://www.oracle-developer.net/display.php?id=207) and build my own Split(str varchar2, regex_of_token varchar2) which takes two arguments. First of which is the input string and the second one is the regular expression of the token. The two steps to build the method are -

1. Build a custom collection type for return type -
   

   CREATE OR REPLACE TYPE str_array AS TABLE OF varchar2(1024);

2. Then the function body -
   

   CREATE OR REPLACE FUNCTION SYS.split
   ( str IN varchar2, regex in varchar2)
   RETURN str_array PIPELINED IS
   next_token varchar2(1024) := '';
   token_index number := 1;
   BEGIN
   LOOP
       select regexp_substr(str,regex,1,token_index)
          into next_token from dual;
       exit when (next_token is null);
       pipe row(next_token);
       token_index := token_index+1;
   END LOOP;
   RETURN;
   END;
   /

Then to test this function, call the function in the following way-

select * from table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','([0-9]+(\.[0-9]+)?|([a-z]|[A-Z])+)'))

The first argument is sample string and second one is regular expression for number or alphabet but not alpha-numeric. If you want to split the string around ',' then the query will be -

select * from
table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','[^,]+'))