Quotes & Escape character

If we want to insert a string with quotes then it would be confusing to prefix many escape characters.

I want to insert 'RAGHU' with quotes; We would do as below:

INSERT INTO EMP_TT VALUES (99,'''RAGHU''','A',NULL,NULL,NULL,NULL,NULL);

In 10G and above there is a new way to do:

Just write the normal single quote and then prefix q'| and suffix |' , wherever single quotes is there it will be retained.

INSERT INTO EMP_TT VALUES (99,q'|'RAGHU'|','A',NULL,NULL,NULL,NULL,NULL);

This can be really useful when you are inserting large string with many quotes.

TO_CHAR Issues

We use TO_CHAR to get month from any date. When we use this to compare, we need to be careful.Did you know TO_CHAR appends white spaces at the end till it is 9 characters long. (Only when used with DATE)

Please see the below demo:

DECLARE
VMON VARCHAR2(20);
BEGIN
select UPPER(to_char(sysdate,'month')) INTO VMON from dual;
DBMS_OUTPUT.PUT_LINE(VMON);
IF VMON = 'JULY' THEN
dbms_output.put_line('Equal');
else
dbms_output.put_line('Not Equal but WHY???');
end if;
IF trim(VMON) = 'JULY' THEN
dbms_output.put_line('Its Equal after TRIM');
else
dbms_output.put_line('Not Equal');
end if;
END;

Workspace Manager

A common requirement in any project would be to have auditing columns in a table.
This is to know who updated/Inserted/Deleted the records and at what time.

Generally programmers, have to write custom update statements, to update name and time of the records before it is being modified.

Did you know oracle has a in-built package to handle this requirement.
Just one line of code is sufficient to fulfill the above requirements.

begin
dbms_wm.enableversioning('EMP_TT','VIEW_WO_OVERWRITE');
END;
Where EMP_TT is the table you want to have versions of old records.

Thats it.

Now anyone who updates or inserts new records, all the changes will be stored in a different view, named as EMP_TT_HIST. This has columns Username and created time, which helps us identifying the source of change.
Even if the complete table is deleted, old records will be in this view.

But one drawback of this is the performance of DMLs might slow down, one needs to test for performance before being implemented in PRODUCTION.

SPLIT function

Often developers have to split the string concatenated with "," (comma).
v_str := 'a,b,c,d,e,f,g,h';
Generally developers use combination of SUBSTR and INSTR to get each value.
Did you know Oracle had a in-built package which would do this for us.
DBMS_UTILITY.comma_to_table is the procedure, which would split the string for us.
No need to loop over to get the next index position etc..

DECLARE
PROCEDURE PARSE(string_in IN varchar2)
is
l_array DBMS_UTILITY.uncl_array;
l_count PLS_INTEGER;
BEGIN
DBMS_UTILITY.comma_to_table(string_in,l_count,l_array);
for indx in 1..l_count
loop
DBMS_OUTPUT.put_line(l_array(indx));
end loop;
exception
when others then
DBMS_OUTPUT.put_line(sqlcode || 'Bad List !');
end;
begin
parse('a,b,c');
end;

Oracle-History of releases

In my first blog, I would like go past the memory lane and note the key keatures each oracle release in the history has to offer:

Summary of key features of prior releases:

Version 6 : SQL Trace, v$Views
7: Replication, stored procedures and triggers
7.1 : Parallel capabilities
7.3 : Bitmap indexes, contexts.
8o : Partitioning and other terabyte necessary features.
8i : Internet capability such as java in databases
9i : Huge advancements in data gaurd availability
10g : Managebility in a grid
11g R1 : Real Application Testing
11g2 : Edition based redefinition (Ability to upgrade plsql objects online, without any downtime)

Surely oracle has come a long way and each release is built on capabilities of past release and it keeps getting better and better.