Oracle Parameterized View

Oracle has this feature because sometimes parameterized view can increase the performance because here predicate are compiled within the view; thus for this predicate the query will be always soft parsed.

Here is a simple example -

create view myview as

select * from t where x = userenv('client_info');


Here in this view definition, user's default namespace "userenv " and
its attribute "client_info" is used. To retrieve data from this view
you have to set this attribute by execute -

exec dbms_application_info.set_client_info
('client_info_value') ;

Some remarks on Oracle context -

1. "Userenv" is the default namespace or context.
   You can create your own context also if you have the privilege.
   
   
2. Every context(namespace) has a package within which
   you can write the code to set any attribute of your context.
   You cannot set the attribute outside the package.
   If you want to do it outside you will get error -
   ORA-01031: insufficient privileges.
   
   
3. To retrieve data from this package, we should use sys_context function.

So, you can use sys_context function also for parameterized view by calling
this function in view definition and before query the data set
the attribute used in view definition. Thus you can easily write view like user_tables -

select * from dba_tables
where owner = sys_context('USERENV', 'SESSION_USER');

Related links -
http://forums.oracle.com
http://asktom.oracle.com

Maintain shell script log in CSV

Suppose you want to log the start and end of your shell script execution. Also you want to know the total execution time. Then you should do the followings -

1. Put the start time in a variable at beginning of script;
   command - export start_date=`date +%m/%d/%Y,%H:%M:%S`.
   Be careful and use `` this operator to put the date function in a variable. Use the proper date format string as you need.
   
   
2. Put the end in a variable at the end of script;
   command - export end_date=`date +%m/%d/%Y,%H:%M:%S`.
   
   
3. Write the log and separate the field using ','.
   echo 'start,' $start_date ',end,' $end_date >> scriptexlog.csv
   
   
   

This is so simple!!

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','[^,]+'))

Drop table if it exists in Oralce

If you want to drop a table which is not in existence then surely you will get an error - table or view does not exist. If you put this type of code in a stored procedure then this error halt the whole procedure execution.

So, the better solution for this is to check whether the table exists or not. If it exists then drop that. The logic can be put in a stored procedure (not in a function as function cannot perform DML or DDL) then we can call this procedure from other procedure and we don't have to duplicate this code. So the procedure will be very simple like the following -

CREATE OR REPLACE procedure drop_table_ifexist(tableName varchar2) IS
tableCount NUMBER := 0;
BEGIN
select count(*) into tableCount
from user_tables
where table_name = upper(tableName);

--oracle store all table names in upper cases.

if(tableCount > 0) then
execute immediate 'drop table '||tableName;
end if;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END drop_table_ifexist;
/

This procedure will drop a table of its own if the table exists. Table name can be queried from two other tables user_objects (collection of all objects owned by this user) and all_objects (collection of all objects privileged by this user).

Schedule oralce procedure through crontab

In previous post - Schedule Java application through crontab, I mentioned that the process of scheduling other application is same as Java application.

To accomplish this, you have to set at least two environment variables. The first one is obviously the PATH. The other is ORACLE_HOME. You need to set ORACLE_SID if you want to use a database as default or if you don't have permission in tnsnames.ora file.

PATH=/usr/local/oracle/product/11.2.0/dbhome_1/bin
ORACLE_HOME=/usr/local/oracle/product/11.2.0/dbhome_1

export PATH
export ORACLE_HOME

These should be sufficient if you have permission in tnsnames.ora. Otherwise, the database should be installed in your workstation and ORACLE_SID should be settled to database service name.

Schedule Java Application through crontab

When we want to run java executable (java, javac, etc.) directly (without mentioning full path) then you have to set the path variable of OS. In linux, you can set path by update the path variable in bash profile. This can be done easily by writing the following commands -

PATH=/usr/local/jdk1.6.0/bin:
export PATH

For detail, you can see http://java.sun.com

Unfortunately, the script you schedule in cron will not get this path which you set in bash profile. So to run your script through cron, you have append the above commands in you script too. That is, you script should contain command like this -

PATH=/usr/local/jdk1.6.0/bin:
export PATH
java ...

It is worth mentionable here - this setting of path variable is applicable for all path setting not only for java path settings. For example, if you have to set oracle path for cron, you have to follow the same steps.

For setting java classpath you can see following article -
http://linuxlab.dk/tipsntricks/classpath

Comparison between web application and web service

http://wiki.answers.com gives a nice and precise description of difference between these twos.

So, here I will mainly focus on similarity that seems to be important to me. The main similarity between these two is apparent when web service allows request through Http GET & POST method along with SOAP protocol. Protocol configuration of .asmx web service is discussed in my previous post.

When, a method of a web service invoked through GET/POST, a URI will be addressed. This URI must point to a method located at server. Then a SOAP packet will be returned in response. Similarly when a page of a web application is requested through GET/POST, page load script is executed and HTML of the page will be returned in response. In case of POST request, an action handler will be executed and this action handler will process the POST data.

Include a shell file into another one

It is a easy task indeed. Suppose, we have two shell script file in same directory. These are firstShell.sh and secondShell.sh. To include the first script in the second, we just need to write the following statement in the second shell script -

source firstShell.sh

If the fistShell.sh is not in same directory then we need to refer the script with full path -

source /path/firstShell.sh

One more thing is if the included shell script requires some arguments to execute, you can pass the arguments where you refer the script. So if the first shell requires some arguments, you have to write -

source /path/firstShell.sh arg1 arg2 ...