Some PlSql tips

It's been more than 4 years now that I have been programming in SQL and Oracle PlSql. I thought about to put some tips for my fellow coders.

Q1. You are fetching a million records from a table by a select clause followed by updates. Whats the best way to commit the transactions?

A. If database load is a concern, you should take the approach of bulk commit instead of commit after every update. You can define a temp variable t_count as a number, initialize it to zero; and then do something like this.


Update  <table>
Set <col_name> = val
Where id = 1000;

t_count := t_count + sql%rowcount ;
if (t_count >= 5000) then
  Commit;
  t_count := 0 ;
end if ;

Commit ; // put this commit in the outer loop/ outer block/ after the end-loop

This way if your whole dataset has 1 million rows to update, total no. of commit statements can be reduced to 1 m/5000 = 200 +1 = 201; you can see the difference yourself in the execution time of your program.


Q2. In the same program above, if I do little alteration like below (only pseudo-code given)

Case 1:

Update table statement ;
t_count := sql%rowcount ;
Commit;
Print t_count ;


Case 2

Update table statement ;
Commit ;
t_count := sql%rowcount ;
Print  t_count ;

What is the difference in the 2 print statements above?

The first one prints no. of rows updated, the second one always prints 1.

Don't believe it? Try it yourself.



Q3. You want to print the date information with a timestamp, but the DBMS output function many times are not guaranteed to print the timestamp in all IDEs ( in SQL developer, printing date field prints only the date portion, not the time). What's the way to overcome this?

A. Simple, use the powerful to_char function from Oracle with some formatting changes.

In this case, use this:
 to_char (date_in, 'mm/dd/yyyy hh24:mi:ss')

This will solve your problem.