Parallel Hint

SELECT /*+ PARALLEL(a) */ COUNT(*) FROM ABCD a ;

Parallel Hint makes operation faster. It does so by providing more threads to the current SQL statement. Not advisable to run in Production Environment during Peak Hours.

When We are Joyful

WHEN WE ARE JOYFUL

 

When we are joyful, we don't look for perfection. If you are looking for perfection then you are not at the source of joy. Joy is the realization that there is no vacation from wisdom. The world appears imperfect on the surface but underneath, all is perfect. Perfection hides; imperfection shows off.

 

The wise will not stay on the surface but will probe into the depth. Things are not blurred; your vision is blurred. Infinite actions prevail in the wholeness of consciousness. And yet the consciousness remains perfect, untouched. As Satsangees, realize this now and be at Home.

 

Regards,
Arpit Shah

---

See the Web's breaking stories, chosen by people like you. Check out Yahoo! Buzz.

Smart Choice for Cursor Processing

It's nice article desribing when to use
CURSOR FOR Loops
Avoid using it
SELECT INTO
Use when query can return atmost one row. Also put SELECT statements in seperate PROCEDURES/FUNCTION. Which can be optimized/cached in Oracle 11g.
CURSOR BULK COLLECT VARRAY [Fixed number of rows or less]
Use when SELECT query will return multiple rows but you know upper limit. If upper limit is very high say 10000, you may want to go for next approach. As it will consume lots of memory.
CURSOR BULK COLLECT NESTED ARRAY with LIMITS
Use when SELECT query will fetch multiple rows and you don't know the upper limit or you know the upper limit but it is very high.

More Detail with examples at:
http://www.oracle.com/technology/oramag/oracle/08-nov/o68plsql.html

Unix to Dos [How to write Control Character in Unix File]

Suppose following is your file. If you move the file from Windows to Unix, you will see Control M (^M) character at the end of each line.
File in Windows:
export USERNAME
export PASSWORD
export CTLPATH

Same File when you move it to Unix
export USERNAME^M
export PASSWORD^M
export CTLPATH^M
^M

You can see the special characters because Windows has [Carriage Return Line Feed] CRLF (\r\n) as line terminating sequence. Where as Unix has only [Line Feed] LF (\n) as line terminator.

So, when we move file from Dos (Windows) to Unix, we need some kind of special processing which will convert CRLF into LF.

One is to open the file and replace Control M character visible in file to blank. To replace Control M character one should know how to create Control M character in Unix.

Folloing is the way to create Control M character in Unix.

Open File in vi editor.

Come in Instert mode by pressing Esc i
Press Control V
Then Press Control M
Then Esc.

It will generate Control M.

Whatever control character you want to generate, first press Control + V and then Control +

So convert file from Dos to Unix follow the steps mentioned below:

Open file in vi.
Type following command.
:0,$s/^M//

The above command will remove Control M (^M) character from entire file.

I will mention other utilities to convert from Dos to Unix sometime later.

SQL Report Formatting Using HTML

See the magic. Following block of code will format the report in tabular format. The best way to generate SQL Report. Using it, you can view it in Browser. You can open it in Excel. 


SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON