Arpit

Oracle Unique Constraint sys.i_procedure1 violated while creating package

2012-01-25T17:03:00.002-06:00

Recently I faced issue in Oracle 11g, where after dropping a package, I was not able to recreate it. Recreating package was giving error, Unique Constraint (sys.i_procedure1) violated.

It was an issue with Oracle Dictionary table not getting updated while dropping package.

DBAs had to take following steps to resolve the issue.

select * from obj$ where name = 'PACK1' ;

select * from user$ where name = 'ABC' ; --This query is to get owner#, which can be joined with obj$

After getting obj#, we had to delete that object from 3 tables.

procedure$, source$ and obj$.

Oracle XML to TABLE using XMLTABLE

2011-12-08T15:07:00.001-06:00

Query:
SELECT seq
, ID
, NAME
FROM XMLTABLE('/xml/emp'
PASSING XMLTYPE('ArpitVenkat')
COLUMNS seq FOR ORDINALITY
, ID VARCHAR2(3) PATH '@id'
, NAME varchar2(10) path 'name'
) AS tbl

Output:
SEQ ID NAME
---------- --- ----------
1 3 Arpit
2 4 Venkat

Escape / (Forward Slash) in Oracle SQL*Plus

2011-01-28T13:12:00.002-06:00

Escape / (Forward Slash) in Oracle SQL*Plus

Problem:
I am running following anonymous block and it's giving error. It's because SQL Plus is considering forward slash (/) in variable "a" assignment as block terminator. How to escape that? The use of following block is to store test case in Database. This is simple example real test cases are complex and involve print after /.
DECLARE
A varchar2(1024) := NULL;
BEGIN
A := 'set serveroutput on;
BEGIN
INSERT INTO table_name
VALUES (5067);
END ;
/

' ;
INSERT INTO x VALUES (A) ;

COMMIT ;
END ;
/

Output
SQL> DECLARE
2 A varchar2(1024) := NULL;
3 BEGIN
4 A := 'set serveroutput on;
5 BEGIN
6 INSERT INTO table_name
7 VALUES (5067);
8 END ;
9 /
ERROR:
ORA-01756: quoted string not properly terminated

SQL>
SQL> ' ;
SP2-0042: unknown command "' " - rest of line ignored.
SQL> INSERT INTO x VALUES (A) ;
INSERT INTO x VALUES (A)
*
ERROR at line 1:
ORA-00984: column not allowed here

SQL>
SQL> COMMIT ;

Commit complete.

SQL> END ;
SP2-0042: unknown command "END " - rest of line ignored.
SQL> /

Commit complete.

SQL>

Running same block from Toad works fine.

Solution
DECLARE
A varchar2(1024) := NULL;
BEGIN
A := 'set serveroutput on;
BEGIN
INSERT INTO table_name
VALUES (5067);
END ;' || '
/' || '

' ;
INSERT INTO x VALUES (A) ;

COMMIT ;
END ;
/

Query to run Dynamic SQL Using XML on SQL> prompt

2010-10-06T15:23:00.001-05:00

Query to run Dynamic SQL Using XML on SQL> prompt

SELECT table_name, extractvalue(dbms_xmlgen.getxmltype('select count(*) from ' || table_name),'//text()')
FROM user_Tables
WHERE table_name LIKE 'A%'

The query above is just sample. Put your query in getxmltype and you are all set.

vi ... How to Refresh Already Open File

2010-08-03T14:03:00.000-05:00

Refresh file from version on disk:
:e!

Awk Script to Find Start Time End Time and Diff

2010-06-30T14:08:00.002-05:00

[0]/ely =>cat f
awk '/Started Lighting for / { bid = toupper(substr($4, 1, 10)) ; start_time[bid] = $6}
/Lighting Successful at|Failed at / { bid = toupper(substr($1, 1, 10))
split(start_time[bid], st, ":")
if ($3 ~ "Successful")
{
end_time[bid] = $5
success_fail = "Success"
}
else
{
end_time[bid] = $4
success_fail = "Failed "
}
split(end_time[bid], et, ":")
start_sec = st[3] + st[2] * 60 + st[1] * 60 * 60
end_sec = et[3] + et[2] * 60 + et[1] * 60 * 60
total_time = end_sec - start_sec
hh = int(total_time / 3600)
rm = total_time % 3600
mm = int(rm / 60)
ss = rm % 60
printf("Bid: %s Start Time: %s %s at: %s Total Time: %02s:%02s:%02s \n", bid, start_time[bid], success_fail, end_time[bid], hh, mm, ss)
}' bb

[0]/ely =>cat bb
Started Lighting for x440019981O.iad at 16:33:31 (x440019981)
x440019981O Failed at 16:33:54 (Domestic)

Started Lighting for x250020161O.iad at 16:35:01 (x250020161)
x250020161O Lighting Successful at 16:35:43 (Domestic)

Started Lighting for x750016701O.iad at 16:38:12 (x750016701)
x750016701O Lighting Successful at 16:38:21 (Domestic)

Started Lighting for x260019961O.iad at 16:38:23 (x260019961)
x260019961O Lighting Successful at 16:38:50 (Domestic)

Started Lighting for x650019976O.iad at 16:40:05 (x650019976)
x650019976O Lighting Successful at 16:40:21 (Domestic)

Started Lighting for x360019763O.iad at 16:41:46 (x360019763)
x360019763O Lighting Successful at 16:42:26 (Domestic)

Started Lighting for x440019981O.iad at 16:44:27 (x440019981)
x440019981O Failed at 16:44:29 (Domestic)

Started Lighting for x370020017O.iad at 16:45:47 (x370020017)
x370020017O Lighting Successful at 16:46:06 (Domestic)

Started Lighting for x260019961O.iad at 16:46:48 (x260019961)
x260019961O Lighting Successful at 16:47:14 (Domestic)

Started Lighting for x920020035O.iad at 16:48:48 (x920020035)
x920020035O Lighting Successful at 16:49:09 (Domestic)

Started Lighting for x560019917O.iad at 16:53:20 (x560019917)
x560019917O Lighting Successful at 16:54:06 (Domestic)

Started Lighting for x030020010O.iad at 16:54:40 (x030020010)
x030020010O Lighting Successful at 16:55:38 (Domestic)

Started Lighting for x260019961O.iad at 16:55:41 (x260019961)
x260019961O Lighting Successful at 16:56:08 (Domestic)

Started Lighting for x460019558O.iad at 16:57:31 (x460019558)
x460019558O Lighting Successful at 16:57:43 (Domestic)

[0]/ely =>./f
Bid: X440019981 Start Time: 16:33:31 Failed at: 16:33:54 Total Time: 00:00:23
Bid: X250020161 Start Time: 16:35:01 Success at: 16:35:43 Total Time: 00:00:42
Bid: X750016701 Start Time: 16:38:12 Success at: 16:38:21 Total Time: 00:00:09
Bid: X260019961 Start Time: 16:38:23 Success at: 16:38:50 Total Time: 00:00:27
Bid: X650019976 Start Time: 16:40:05 Success at: 16:40:21 Total Time: 00:00:16
Bid: X360019763 Start Time: 16:41:46 Success at: 16:42:26 Total Time: 00:00:40
Bid: X440019981 Start Time: 16:44:27 Failed at: 16:44:29 Total Time: 00:00:02
Bid: X370020017 Start Time: 16:45:47 Success at: 16:46:06 Total Time: 00:00:19
Bid: X260019961 Start Time: 16:46:48 Success at: 16:47:14 Total Time: 00:00:26
Bid: X920020035 Start Time: 16:48:48 Success at: 16:49:09 Total Time: 00:00:21
Bid: X560019917 Start Time: 16:53:20 Success at: 16:54:06 Total Time: 00:00:46
Bid: X030020010 Start Time: 16:54:40 Success at: 16:55:38 Total Time: 00:00:58
Bid: X260019961 Start Time: 16:55:41 Success at: 16:56:08 Total Time: 00:00:27
Bid: X460019558 Start Time: 16:57:31 Success at: 16:57:43 Total Time: 00:00:12

Partitioning

2010-06-22T16:00:00.001-05:00

Partitioning
Local Indexes
Index is partitioned for each partition. So one Index Partition will store index keys for only one partition
Global Partitioned Indexes
Partitioning Key for Index is independent of Partitioning Key for table. It can be applied to Regular table, Index Organized Tables, Partitioned Tables
Global Non Partitioned Indexes
It's regular non Partitioned Index. And can be applied to any table

Three Types of Basic Partitioning:
----------------------------------
Range : Partition For Feb 2010, Mar 2010, ...
List : Partition For say America, India, US, Russia
Hash : Partition using Hash Algorithm (I guess Oracle does not publish the algorithm)

Single Level Partitioning: Only one set of partitions
-------------------------
Composite Partitioning: Two level of partitions and can be combination of 3 basic
----------------------
paritioning type. Available composite partitioning techniques are range-hash, range-list,
range-range, list-range, list-list, and list-hash.

Partitioning Extension in Oracle 11g:
------------------------------------
Interval Partitioning: Define Interval and first partition. Oracle will create new partition when data is inserted for first time in new partition

REF Partitioning: Parent-Child Relationship: Child partitions will be created automatically based on parents partition and will have same charecteristics as parent partitions. In Child partitions, Oracle will not store index keys as data

Virtual Column Based Partitioning:
Paritioning based on metadata instead of column data. Let's say account number has first three digit as branch code, then we can have partitions for branch and account level data will go in respective branch partitions

Parallel Hint

2009-11-04T16:27:00.000-06:00

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

2009-08-05T08:41:00.001-05:00

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

2009-07-10T09:51:00.002-05:00

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]

2009-07-06T10:29:00.005-05:00

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

2009-05-27T17:20:00.001-05:00

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

Fw: Love - The Question of an answer

2009-05-05T16:11:00.001-05:00

Love - The Question of an answer

In a congregation, Sri Sri asked, "How many of you feel strong?" Many people raised their hands.
Sri Sri then asked, "Why?"
"Because you are with us," they answered.
"Only those who feel weak can surrender," Sri Sri responded.
All those who were feeling strong were taken aback; they suddenly felt weak!

If you are in love, you feel weak because love makes you weak. Yet there is no power stronger than love. Love is strength. Yet love is the greatest power on earth. You feel absolutely powerful when you are with the Divine.

Someone asked: But why do we keep alternating between strength and weakness?
Sri Sri: That is the fluctuation in life.
When you feel weak - surrender.
When you feel strong - do seva.

Regards,
Arpit Shah
"Celebrate Life. Care for others and share whatever you have with those less fortunate than you. Broaden your vision, for the whole world belongs to you."
- Sri Sri Ravi Shankar, Founder, Art of Living Foundation, www.artofliving.org

Bollywood news, movie reviews, film trailers and more! Click here.

Intelligent people celebrate diversity

2009-04-17T09:23:00.001-05:00

Intelligent people celebrate diversity

Bangalore (India), April 16 (Thursday), 8:10 pm: The 2,000-strong crowd in the Vishalakshi Mantap Hall at the Art of Living Centre sat rapt in attention as Sri Sri answered many questions at the satsang this evening.

Q. What is the science of relativity? How does it work in life?

A.: You go on the internet. There is so much on it. Volumes and volumes. Everything is related: if you've slept well, you see everything better. If not, then things are blurred.

The observer and the observed vary. That is why it is said that different states of consciousness understand different knowledge.

Have you heard the Japanese story?

In Japan, there is a rule that a motel-owner must give free boarding and lodging to monks.

To test if a monk is genuine, the owner would ask a knowledge question. If the question is answered, monk can then stay. If the owner gives the right answer, then the monk will go further.

There was a motel run by two brothers. The elder one was very intelligent. The younger one was dull. The elder brother used to manage affairs such that he did not have to give free rooms to the monks. If the elder brother had to go away, he would tell the younger one: 'If any monk comes here, act dumb. If you're silent, the monk will not stay here."

As soon as the elder brother left, a group of monks arrived. They said: 'Come we will argue.'

The younger brother gestured: 'I am in silence.'

The monks: 'We will have a dialogue in silence.' They showed the forefinger to indicate 'one'.

The younger brother had only one eye. The other eye was bandaged. He showed two fingers.

The monks then showed three fingers.

The brother then showed a fist.

The monks became very happy and left.

When the elder brother came, the younger one explained what happened:

'They told me that you have only one eye.

So I said, 'You have two.'

They then said: The dialogue is between three eyes.

So I said: I will punch you.'

Later the monks returned and told the elder brother that the younger one had shared the highest knowledge in silence.

The monks narrated:

'We asked: What is the one truth?

He said: Not one, there are two: Buddham and Dhammam.

We said: There are three things: – Buddham, Dhammam, and Sangham.

He said: They are all one!'

It was such a mind-blowing realization!

This story shows that different levels of consciousness can interpret different things, differently.

Fools always create conflicts over nothing. And die for it. The intelligent will celebrate diversity. Fools can't tolerate diversity.

The ancient sages in the Rig Veda have said: 'Accept even the atheist and they have included them in prayers: Those who call You as no God and think there is no Divinity, I bow down. Those who say, You are not there, I offer my obeisance.'

One accepts even atheism. That is true wisdom: You have broad vision which accepts people and differences.

Intelligent people celebrate diversity, fools fight over diversity.

More at: http://www.artofliving.org/intl/wisdomblog/tabid/183/Default.aspx

Regards,
AOL Parsippany
"Celebrate Life. Care for others and share whatever you have with those less fortunate than you. Broaden your vision, for the whole world belongs to you."
- Sri Sri Ravi Shankar, Founder, Art of Living Foundation, www.artofliving.org

Add more friends to your messenger and enjoy! Invite them now.

KNOW UR CHAKRAS by Sri Sri

2009-04-13T10:10:00.001-05:00

KNOW UR CHAKRAS
-
Know yourself better by knowing the Colour of Chakras
-
You possess chakras, every human does and they do have a color.
Why you are elated when you see green?
Why is red violent?
Why indigo soothes and relaxes your mind?
The reason lies in the color of the respective chakra

A human body has seven major chakras, namely, Crown , Third Eye , Throat,
Heart, Solar Plexus, Hara and finally the Root chakra with the colors in the
sequence of VIBGYOR (Violet, Indigo, Blue, Green, Yellow, Orange, Red
)respectively.

This is the reason why cardiac patients are advised to eat green vegetables
and asked to walk on green grass as the former not only give nutrients but
also soothes the heart chakra. The more you see green the more you feel
green (good at heart)
So for the heart patients the basic mantra is go green!!!
Exercise each color to strengthen your respective chakra and feel the
difference

Regards,
AOL Parsippany

"Celebrate Life. Care for others and share whatever you have with those less fortunate than you. Broaden your vision, for the whole world belongs to you."
- Sri Sri Ravi Shankar, Founder, Art of Living Foundation, www.artofliving.org

Add more friends to your messenger and enjoy! Invite them now.

A very nice perspective - "Money has no memory. Experience has"

2009-04-13T10:08:00.001-05:00

Money has no memory. Experience has. You will never know what was the total cost of your education, but for a lifetime you will recall and relive the memories of schools and colleges. Few years from now, you will forget the amount you paid to settle the hospitalization bill, but will ever cherish having saved your mother's life or the life you get to live with the just born. You won't remember the cost of your honeymoon, but to the last breath remember the experiences of the bliss of togetherness. Money has no memory. Experience has.

Good times and bad times, times of prosperity and times of poverty, times when the future looked so secure and times when you didn't know from where the tomorrow will come… life has been in one way or the other a roller-coaster ride for everyone. Beyond all that abundance and beyond all that deprivation, what remains is the memory of experiences. Sometimes the wallet was full… sometimes even the pocket was empty. There was enough and you still had reasons to frown. There wasn't enough and you still had reasons to smile. Today, you can look back with tears of gratitude for all the times you had laughed together, and also look back with a smile at all the times you cried alone. All in all, life filled you with experiences to create a history of your own self, and you alone can remember them all.

The first time you balanced yourself on your cycle without support…

The first time she said 'yes' and it was two years since you proposed…

The first cry… the first steps… the first word… the first kiss… all of your child…

The first gift you bought for your parents and the first gift your daughter gave you…

The first award… the first public appreciation… the first stage performance…

And the list is endless… Experiences, with timeless memory…

Not denying that anything that's material cost money, but the fact remains the cost of the experience will be forgotten, but the experience never.

So, what if it's economic recession? Let it be, but let there not be a recession to the quality of your life. You can still take your parents, if not on a pilgrimage, at least to the local temple. You can still play with your children, if not on an international holiday, at least in the local park. It doesn't cost money to lie down or to take a loved one onto your lap. Nice time to train the employees, create leadership availability and be ready for the wonderful times when they arrive. Hey! Aspects like your health, knowledge development and spiritual growth are not economy dependent.

Time will pass… economy will revive… currency will soon be in current… and in all this, I don't want you to look back and realize you did nothing but stayed in gloom. Recession can make you lose out on money. Let it not make you lose out on experiences… If you are not happy with what you have, no matter how much more you have, you will still not be happy.

Make a statement with the way you live your life: How I feel has nothing to do with how much I have.

Add more friends to your messenger and enjoy! Invite them now.

Cursor with variable in an "IN CLAUSE"

2009-01-30T10:10:00.002-06:00

SQL> with t as
(SELECT ',001,002,003,004,005,006,007,008,ALL,' sList
FROM dual)
SELECT LEVEL, SUBSTR(sList, INSTR(sList, ',', 1, LEVEL) + 1,
INSTR(sList, ',', 1, LEVEL + 1) - INSTR(sList, ',', 1, LEVEL) - 1)
2 3 4 5 6 FROM t
CONNECT BY LEVEL <= LENGTH(sList) - LENGTH(REPLACE(sList,',')) - 1 7 ;

LEVEL SUBSTR(SLIST,INSTR(SLIST,',',1,LEVEL)
---------- -------------------------------------
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 ALL

9 rows selected.

SQL>

SQL> with t as
(SELECT q'!('001','002','003','004','005','006','007','008','ALL')!' sList
FROM dual)
SELECT LEVEL, SUBSTR(sList, 3 + 6*(LEVEL - 1), 3)
FROM t
CONNECT BY LEVEL <= LENGTH(sList) - LENGTH(REPLACE( 2 3 4 5 sList, ',')) + 1
6 7 ;

LEVEL SUB
---------- ---
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 ALL

9 rows selected.

SQL> with t as
(SELECT ',001,002,003,004,005,006,007,008,ALL,' sList
FROM dual)
SELECT LEVEL, SUBSTR(sList, INSTR(sList, ',', 1, LEVEL) + 1, 3)
FROM t
CONNECT BY LEVEL <= LENGTH(sList) - LENGTH(REPLACE(s 2 3 4 5 6 List,',')) - 1;

LEVEL SUB
---------- ---
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 ALL

9 rows selected.

SQL>

Using the FOR Clause in Pro*C (Oracle)

2008-10-28T16:06:00.000-05:00

Using the FOR Clause

You can use the optional embedded SQL FOR clause to set the number of array elements processed by any of the following SQL statements:

DELETE
EXECUTE
FETCH
INSERT
OPEN
UPDATE

The FOR clause is especially useful in UPDATE, INSERT, and DELETE statements. With these statements you might not want to use the entire array. The FOR clause lets you limit the elements used to just the number you need, as the following example shows:

char emp_name[100][20];
float salary[100];
int rows_to_insert;

/* populate the host arrays */
rows_to_insert = 25; /* set FOR-clause variable */
EXEC SQL FOR :rows_to_insert /* will process only 25 rows */
INSERT INTO emp (ename, sal)
VALUES (:emp_name, :salary);
The FOR clause can use an integer host variable to count array elements, or an integer literal. A complex C expression that resolves to an integer cannot be used. For example, the following statement that uses an integer expression is illegal:

EXEC SQL FOR :rows_to_insert + 5 /* illegal */
INSERT INTO emp (ename, empno, sal)
VALUES (:emp_name, :emp_number, :salary);
The FOR clause variable specifies the number of array elements to be processed. Make sure the number is not larger than the smallest array dimension. Also, the number must be positive. If it is negative or zero, no rows are processed and Oracle issues an error message.

Restrictions
Two restrictions keep FOR clause semantics clear. You cannot use the FOR clause in a SELECT statement or with the CURRENT OF clause.

In a SELECT Statement
If you use the FOR clause in a SELECT statement, you get the following error message:

PCC-E-0056: FOR clause not allowed on SELECT statement at ...
The FOR clause is not allowed in SELECT statements because its meaning is unclear. Does it mean "execute this SELECT statement n times"? Or, does it mean "execute this SELECT statement once, but return n rows"? The problem in the former case is that each execution might return multiple rows. In the latter case, it is better to declare a cursor and use the FOR clause in a FETCH statement, as follows:

http://www.lsbu.ac.uk/oracle/oracle7/api/doc/PC_22/ch10.htm

Oracle Trigger: Mutating Table

2008-10-28T16:01:00.002-05:00

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.

Consider the following trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*)
INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' n ' employees.');
END;

If the following SQL statement is entered:

DELETE FROM Emp_tab WHERE Empno = 7499;

An error is returned because the table is mutating when the row is deleted:

ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it

If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger which is not subject to this restriction, and the trigger.

If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Declarative integrity constraints are checked at various times with respect to row triggers.
See Also: Oracle Database Concepts for information about the interaction of triggers and integrity constraints
Because declarative referential integrity constraints are not supported between tables on different nodes of a distributed database, the mutating table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.

Bulk Collect NO_DATA_FOUND

2008-10-28T15:37:00.002-05:00

Bulk collect clause do not generate an error when it do not find data in Select query. We need to check whether collection variable has any element or not.

Sample:

create table test_bulk (cola number, colb number) ;
/
Table created.

declare
TYPE tNum IS TABLE OF NUMBER ;
a tNum;
b tNum;
BEGIN
SELECT cola, colb
BULK COLLECT INTO a, b
FROM test_bulk ;
IF a.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('BULK COLLECT: NO DATA FOUND') ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION: NO DATA FOUND') ;
END ;
/
BULK COLLECT: NO DATA FOUND
PL/SQL procedure successfully completed.

Happy Diwali

2008-10-27T17:00:00.002-05:00

As the glow of diyas spread radiance all around…
As the sound of crackers fill the air…
May the Festival of light
Brighten your life with
Peace, prosperity and fortune.
 
Wish you Happy Diwali & prosperous New Year

Using Cursor Expressions [Cursor in Select statement]

2008-09-30T14:58:00.000-05:00

A cursor expression returns a nested cursor. Each row in the result set can contain values as usual, plus cursors produced by subqueries involving the other values in the row. A single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, then from any nested cursors within those rows.

PL/SQL supports queries with cursor expressions as part of cursor declarations, REF CURSOR declarations and ref cursor variables. You can also use cursor expressions in dynamic SQL queries. Here is the syntax:

CURSOR(subquery)

A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:

The nested cursor is explicitly closed by the user

The parent cursor is reexecuted

The parent cursor is closed

The parent cursor is canceled

An error arises during a fetch on one of its parent cursors. The nested cursor is closed as part of the clean-up.

Restrictions on Cursor Expressions
The following are restrictions on cursor expressions:

You cannot use a cursor expression with an implicit cursor.

Cursor expressions can appear only:

In a SELECT statement that is not nested in any other query expression, except when it is a subquery of the cursor expression itself.

As arguments to table functions, in the FROM clause of a SELECT statement.

Cursor expressions can appear only in the outermost SELECT list of the query specification.

Cursor expressions cannot appear in view declarations.

You cannot perform BIND and EXECUTE operations on cursor expressions.

Example of Cursor Expressions
In Example 6-35, we find a specified location ID, and a cursor from which we can fetch all the departments in that location. As we fetch each department's name, we also get another cursor that lets us fetch their associated employee details from another table.

Example 6-35 Using a Cursor Expression

DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
   emp_cur emp_cur_typ;
   dept_name departments.department_name%TYPE;
   emp_name employees.last_name%TYPE;
   CURSOR c1 IS SELECT
      department_name,
-- second item in the result set is another result set,
-- which is represented as a ref cursor and labelled "employees".
      CURSOR
      ( SELECT e.last_name FROM employees e
         WHERE e.department_id = d.department_id) employees
      FROM departments d WHERE department_name like 'A%';
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO dept_name, emp_cur;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
-- for each row in the result set, the result set from a subquery is processed
-- the set could be passed to a procedure for processing rather than the loop
      LOOP
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
      END LOOP;
   END LOOP;
   CLOSE c1;
END;
/
Constructing REF CURSORs with Cursor Subqueries
You can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. For example, this statement passes a parameter to the StockPivot function consisting of a REF CURSOR that represents the rows returned by the cursor subquery:


SELECT * FROM TABLE(StockPivot(
              CURSOR(SELECT * FROM StockTable)));

Cursor subqueries are often used with table functions, which are explained in "Setting Up Transformations with Pipelined Functions".

Art Of Living Birthday Song

2008-09-09T13:29:00.003-05:00

Sagar Mein Ek Leher(Birthday Song)
Sagar Mein Ek Lehar Uthi Aapke Naam ki.
Aapko Mubarak Khushiyan har sansaar ki,
Aapko hai Anant mein jaakar mil jaana,
Aap hain sundar Bund is chetna ki. ……. Sagar

This birthday song was drafted and composed in fifteen minutes. One day when Guruji was sitting at Sumeru and celebrating the fortieth B’day of a devotee. When the gathering started singing the well worn birthday song, Guruji admonished them to stop singing the same old weather-beaten song and compose some new tune---and thus this song was born---with the blessings of the master himself. The original song is in Hindi and the English translation is as below.

Today is the day waves remember
The Ocean where it was born
And today is the day we all remember
That, this world is our very own.
Happy Birthday to you (2)
We belong to you Happy Birthday to Dear Guruji
We all are your very own Yes,
we all are your very own
The Art Of Living Website (All about Meditation, Yoga, Breathing Techniques, Stress Relief)

How to check if environment variable is defined or not in Unix?

2008-08-15T09:39:00.005-05:00

Suppose, I want to check if variable USER_ID is defined or not.

---------------------------------------------------------------

The best solution I found is:

NOTDEFINED=ItsUnDefined

USER_ID=${USER_ID:-$NOTDEFINED}

if [ "$USER_ID" = "ItsUnDefined" ]

then

echo Enter USER ID:

read USER_ID

fi

echo USER_ID

Explanation: USER_ID=${USER_ID:-$NOTDEFINED}

This will check if USER_ID variable is defined or not. If it is undefined, it will assign value of variable NOTDEFINED to it otherwise it will use its own value.

Generally we use this to see if user has passed command line parameters. And if user has not passed parameter, you can set default value using this.

USER_ID=${1:-arpit}

echo $USER_ID

---------------------------------------------------------------

The second solution can be used is:

envgrep "^USER_ID"

if [ $? -eq 1 ]

then

echo Enter USER ID:
read USER_ID
fi
echo USER_ID

Explanation: env lists all environment variables defined for the shell. Then you can grep to see, if the variable is defined or not.

---------------------------------------------------------------

Example of REGEXP_LIKE and explanation of back references

2008-05-29T15:06:00.004-05:00

Requirement:

Suppose we have column named Name1 (consisting of first name and last name) with following data:

Name1
-------------
Arpu Anau
Stev Smith

I want to match first and last characters of first and last names. If they are same show that row or show TRUE for that row.
Means
Arpit Anau is having A=A and u=u. So, TRUE
Stev Smith is having S=S but v!=h. So, FALSE

Example without Regular Exprssion:

WITH t AS
(
SELECT 'Arpu Anau' Name1 FROM DUAL UNION ALL
SELECT 'Stev Smith' Name1 FROM DUAL
)
SELECT FN, LN, CASE WHEN SUBSTR(FN,1,1) = SUBSTR(LN,1,1) AND SUBSTR(FN,-1,1) = SUBSTR(LN,-1,1) THEN 'TRUE' ELSE 'FALSE' END
FROM (SELECT SUBSTR(Name1,1,INSTR(Name1,' ')-1) FN, SUBSTR(Name1,INSTR(Name1,' ')+1) LN
FROM t)
SQL> /

FN LN CASEW
---------- ---------- -----
Arpu Anau TRUE
Stev Smith FALSE

With Regular Expresion [example uses backreferences in Regular Expression]

SQL> WITH t AS
(
SELECT 'Antu Anamentu' Name1 FROM DUAL UNION ALL
SELECT 'Steven Smith' Name1 FROM DUAL
)
SELECT Name1
FROM t
WHERE REGEXP_LIKE (Name1, '^(.).*(.) \1.*\2$') ;

NAME1
-------------
Antu Anamentu

Explanation:
^(.) =>^ specifies the next char is first char
. dot specifies single character
() defines group which can be backrferenced
.* => Any number of characters as we want only first and last character
(.) => (.)space The space signifies that we want to remember last char of first word or char before space
\1 => It recalls first group which we saved for backreference using (.)
.* => Any number of chars
\2 => It recalls first group which we saved for backreference using (.)
$ => It specifies the char before this is last char

My Trip to Niagara Fall

2008-05-28T16:40:00.002-05:00

As 24, 25, 26-May-08 (Memorial Day holiday) was long weekend, we decided to visit Niagara Fall. We includes: Nilang, Keval, Kunal, Hardeep, Pravin, Naveen, Sanjay and myself.

Planning:
Earlier we were planning to rent a Car and drive it. But then where were few hurdles like the rented Van can accomodate only 7 people. And we were 8. And since all were not driving we were not able to rent two cars. Which seemed to be lucrative option as cheaper and comfortable compare to 7 ppl in one van. Finally we decided to go by bus. Kunal booked our ticket on gotobus.com . I bought Haldiram Namkin packets for trip.

Trip Day:
Reached Bus Stop at Top Quality Food Plaza [828 Rt 46 W]
As the route of bus was via Parsippany, all decided to meet at my house and then go to bus stop. For, Kunal New York was near and easier to commute, so he went to New York. Pravin came to my house previous night. On Saturday morning at 8 o'clock, Nilang, Keval, Sanjay, Praving, Hardeep came to my house. I drove three frnds to bus stop and parked my Car at sister's house. My room partner Nirav dropped remaining 4 to bus stop. He also picked me up from my sister's house.

Bus Journey:
We started at 9:15am by bus. Actually bus was waiting for us only. As soon as we get into bus, it started. It was 2X2 coach with 58 seats. Ours were last 8 seats. Bus was full of desis. Out of 58, almost 48 were desis. And bus was of Chinese Travel Agency called "Happy Vacations". Seems desis need to venture into travels ;-)
Guide was chinese guy named Leo. We took first break at almost 12:15pm. Bus stopped at Burger King(BK). It looked like small BK was filled with tourist of 4 buses. I forgot to mention but there were almost 5 buses that were running parallely on same route. There were queue everywhere for food, for restrooms :) So we went to Subway, at distance of 5 mins walk.

Remaining will write later.

Unix: sed to change characters in particular position

2008-05-28T15:53:00.004-05:00

Suppose you have big file. You want to change character 100-110 with some text. So, simply you want to interchange first 4 characters with the next 4 characters in each line. In these kind of scenarios, sed one liners will be very useful.

See few examples below:

$cat a.tmp
20090918ARPITH2010011634
20090918ARPITH2010012050
20090905ARPITH2010011382
20090824ARPITH2010012075
20090921ARPITH2010012075
--Follwing sed command will interchange first 4 digits with next 4 digits.
$sed 's:$[0-9]\{4\}$$[0-9]\{4\}$:\2\1:' a.tmp
09182009ARPITH2010011634
09182009ARPITH2010012050
09052009ARPITH2010011382
08242009ARPITH2010012075
09212009ARPITH2010012075
Explanation:
$[0-9]\{4\}$ It defines first four digits. If you want to replace any 4 characters not only digit then replace [0-9] with . like $.\{4\}$
\ It is to escape (
() This bracket will define different groups. It is used for backreference and can be used again using \1 or \2 etc based on its occurence.
[0-9] Check for digits
\{4\} Four occurence of digit in [0-9]

\2\1 It says first put second set and then first set.

Following sed command will interchange first 8 characters/digits with next 6 characters. And it will also seperate each set with space.
$sed 's:$.\{8\}$$.\{6\}$:\2 \1 :' a.tmp
ARPITH 20090918 2010011634
ARPITH 20090918 2010012050
ARPITH 20090905 2010011382
ARPITH 20090824 2010012075
ARPITH 20090921 2010012075

Following command will replace characters from 9 to 15 with text "NIRAVB"
sed 's:$.\{8\}$$.\{6\}$:\1NIRAVB:' a.tmp
20090918NIRAVB2010011634
20090918NIRAVB2010012050
20090905NIRAVB2010011382
20090824NIRAVB2010012075
20090921NIRAVB2010012075

Limitation: If you specify more than 255 in curly braces "\{ \}", you will get following error.
$sed 's:$.\{256\}$$.\{6\}$:\1NIRAVB:' a.tmp
sed: Function s:$.\{256\}$$.\{6\}$:\1NIRAVB: cannot be parsed.

Solution: To overcome the issue you can break 335 in 255 + 80. As I have shown in following example. Here I want to change character 335 to 338 with text "TEST"
$cat a.tmp
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005

$sed 's:$.\{335\}$$.\{4\}$:\1TEST:' a.tmp
sed: Function s:$.\{335\}$$.\{4\}$:\1TEST: cannot be parsed.

$sed 's:$.\{255\}$$.\{80\}$$.\{4\}$:\1\2TEST:' a.tmp

20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005
20090913476178957893478958937589078903745897123890789074589076238904758906179038759081738904715890378904518978907348907579487592763785691287589072386590827890752890475890274890768902748906892748976898000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000TEST00000000000000000000000000000000005

Query to see partition info for Oracle Table

2008-05-28T15:50:00.002-05:00

Following query will show the column name[s] on which partition is created.
SELECT *
FROM ALL_PART_KEY_COLUMNS
WHERE NAME =

Following query will give the maximum value for particular partitions.
SELECT HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME =

Following query will give all the tables in dictionary related to PARTITION.
SELECT TABLE_NAME
FROM DICT
WHERE TABLE_NAME LIKE '%PART%'

Create virtual rows

2008-05-02T11:00:00.001-05:00

create virtual rows:

The query will run for first level. Then it will see, still it is less than 10, so it will try for next level and so on....

SELECT * FROM (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10) ;

Good Link which explains how it works:
SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method

Another way to create virtual rows is to select from ALL_OBJECTS
One more way to create virtual table:
WITH t AS
(
SELECT '01-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '02-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '03-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '04-MAR-2008' Dt FROM DUAL
)
SELECT * FROM t ;

Query to Calculate Business Days [Query to calculate date difference excluding Saturday/Sunday]
SELECT SUM(DECODE(TO_CHAR(TO_DATE('01-MAR-2008')+ROWNUM-1, 'DY'),'SUN',0,'SAT',0,1))
FROM all_objects
WHERE ROWNUM <= TO_DATE('20-MAR-2008') - TO_DATE('01-MAR-2008') + 1

Query to find Next Date and Previous date in table of current date
WITH t AS
(
SELECT '01-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '02-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '03-MAR-2008' Dt FROM DUAL
UNION ALL
SELECT '04-MAR-2008' Dt FROM DUAL
)
SELECT LEAD(Dt,1) OVER (ORDER BY dt) NEXT_DT, LAG(Dt,1) OVER (ORDER BY dt) PREV_DT
FROM t ;

Oracle: Calendar using single SQL Query

2008-05-02T10:00:00.000-05:00

About Oracle: Calendar

Oracle 11g PL SQL New Features

2008-05-01T13:11:00.001-05:00

http://maclochlainn.wordpress.com/2008/04/17/oracle-database-11g-plsql-new-features/

how to remove carriage return line feed from file in unix

2008-05-01T10:37:00.003-05:00

#cat test_C
hi
my name is arpit
this is test for cr lf
reg
arpit
#od -c test_C
0000000 h i \n m y n a m e i s a r
0000020 p i t \n t h i s i s t e s t
0000040 f o r c r l f \n r e g \n a
0000060 r p i t \n
0000065

#od -b test_C
0000000 150 151 012 155 171 040 156 141 155 145 040 151 163 040 141 162
0000020 160 151 164 012 164 150 151 163 040 151 163 040 164 145 163 164
0000040 040 146 157 162 040 143 162 040 154 146 012 162 145 147 012 141
0000060 162 160 151 164 012
0000065

#cat test_C tr -d "\015\012" > op
#cat op
himy name is arpitthis is test for cr lfregarpit#
#
--tr command with -d option deletes char specified.
#od -b op
0000000 150 151 155 171 040 156 141 155 145 040 151 163 040 141 162 160
0000020 151 164 164 150 151 163 040 151 163 040 164 145 163 164 040 146
0000040 157 162 040 143 162 040 154 146 162 145 147 141 162 160 151 164
0000060

#od -c op
0000000 h i m y n a m e i s a r p
0000020 i t t h i s i s t e s t f
0000040 o r c r l f r e g a r p i t
0000060
#

Unix: Search for a pattern in all the files in all the directory

2008-04-30T13:17:00.003-05:00

find . -name "*" -print xargs grep -i "string_to_find"

OR

grep -i "string_to_find" `find . -name "*"`

Finding for multiple file names in single command:

find . $ -name "*.qry" -o -name "*.sql" $ -print

How are numbers stored in Oracle?

2008-04-30T09:26:00.003-05:00

This is really good link.

It explains how the numbers are converted from Oracle internal format to real format (decimal/hexa..).

How do i store the row counts of all tables …

2008-04-28T09:53:00.002-05:00

The query is really good. Doing dynamic sql in SQL.

SQL> select
table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from 'table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

TABLE_NAME COUNT
------------------------------ ------
DEPT 4
EMP 14
BONUS 0
SALGRADE 5

Ref: http://laurentschneider.com/wordpress/2007/04/how-do-i-store-the-counts-of-all-tables.html

Oracle: Date format element 'ww'

2008-04-25T10:22:00.000-05:00

About Oracle: Date format element 'ww'

2008-04-23T11:21:00.005-05:00

If you fetch past the last row in the result set, the values of the target variables are undefined.
Note: Eventually, the FETCH statement fails to return a row. When that happens, no exception is raised. To detect the failure, use the cursor attribute %FOUND or %NOTFOUND.

Cast in Oracle for type casting

COALESCE function

In Oracle/PLSQL, the coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.
The syntax for the coalesce function is:
coalesce( expr1, expr2, ... expr_n )

IF expr1 IS NOT NULL THEN

RETURN expr1;

ELSIF expr2 IS NOT NULL THEN

RETURN expr2;

....

ELSE

RETURN NULL ;

END IF;

Oracle is an object-relational database management system (ORDBMS), which means that users can define additional kinds of data--specifying both the structure of the data and the ways of operating on it--and use these types within the relational model. This approach adds value to the data stored in a database. User-defined datatypes make it easier for application developers to work with complex data such as images, audio, and video. Object types store structured business data in its natural form and allow applications to retrieve it that way. For that reason, they work efficiently with applications developed using object-oriented programming techniques.

SCN, the System Change Number

2008-04-22T08:43:00.002-05:00

Ref: http://www.adp-gmbh.ch/ora/concepts/scn.html

SCN, the System Change Number

The scn is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.
The current system SCN can be queried using dbms_flashback.get_system_change_number.
The SCN of the last checkpoint can be found in v$database.checkpoint_change#.
The SCN is needed in a flashback table .. to scn ... statement.

Is database in a consistent state?
The SCN plays an important role to determine if the database is in a consistent state when the database is brought online. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don't match, the database is in an inconsistent state.
Converting a time to scn
smon_scn_time allows to roughly find out which SCN was current for a specific time in the last five days.

Incrementing SCN
The SCN is incremented whenever a transaction commits. However, this is not the only source of increments. In a seemingly idle database, the SCN gets incremented also through AQ, SMON, job queues...

The role of the SCN for consistent reads
The SCN plays a vital role for providing consistent reads.
Basically, it works as follows: The query reads a db block. This block has as an attribute the SCN when it was last changed. If this SCN is greater than the SCN that was in place when (our) query began, it means that the block was changed after we have started our query. So we have to find an older version of the block. If this block is found in the rollback segments, we use it for our query.

Misc
The v$ views use change# to report SCN values. Hence, it can be argued that SCN means System Change Number, not System Commit Number.
When exporting, an SCN can be specified with the exp_flashback_scn export parameter.
With Oracle 10g, it is possible to go back to a specific SCN on a table with flashback table to scn.
See also the ora_rowscn pseudo column

rowdependencies vs norowdependencies
[ column-properties ]
[ table-partitioning-clauses ]
[ cache nocache ]
[ parallel-clause ]
[ rowdependencies norowdependencies ]
[ enable-disable-clause-1 [, enable-disable-clause-2 ....] ]
[ row-movement-clause ]
[ as subquery ]
The table properties appear as part within the create table [heap] statement.
rowdependencies vs norowdependencies
The default is norowdependencies which keeps the SCN of the last modification at block level. When a table is created with rowdependencies, the SCN of each row's last modification is stored along with the

How Lenders Interpret Your Credit Report

2008-04-21T12:47:00.001-05:00

Refer: http://www.howstuffworks.com/credit-report3.htm

Inquiries - Every time you apply for a credit card to get a free travel mug, duffel bag, or T-shirt, you are adding another hard inquiry to your credit report. When potential lenders see these inquiries, it may wrongly imply that you're either in some financial situation where you need a lot of credit, or are planning to take on a large debt. Either can flag you as a high credit risk.
Other types of inquiries, such as your own requests to view the report, employer requests to view the report and requests by marketers to get your name in order to sell you something, count as soft inquiries. These inquiries don't show up on the reports that lenders see, and therefore don't affect how they view your credit.

Debt in relation to income - If you have unsecured credit card debt that is more than 20 percent of your annual income, lenders may not want to give you the best deal on a loan -- if they'll take the chance and give you a loan in the first place. Work to reduce the debt-to-income ratio and you'll be able to get better rates on the loans you seek.

Load Oracle Package in SGA (Memory) at Startup

2008-04-21T10:19:00.001-05:00

Load Oracle Package in SGA at Startup

Normally whenever you call package for the first time, the entire package will be loaded in the memory. Memory is a shared area called share pool. Whenever there is a call to another stored procedure/function, oracle will try to find space in shared pool. If memory is available, object will be loaded into the memory. And if memory is not available then oracle will page out old procedures in memory using LRU (Least Recently Used) algorithm. If the large package which fit into the memory initially may not fit into contiguous memory when it is reloaded after paging out. It may give error ORA-4031.

Pin Oracle Package

To prevent paging, you can mark packages as non-swappable, telling the database that after their initial load they must always remain in memory. This is called pinning, or memory fencing. Oracle provides the procedure dbms_shared_pool.keep for pinning a package. You can unpin packages by using dbms_shared_pool.unkeep. In addition to pinning packages at database startup time, you can call the dbmspool.keep procedure at runtime to pin a package of stand-alone stored procedures.

Oracle Corporation recommends that you always pin the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE, and DBMS_OUTPUT packages in the shared pool. You can use the following code:

connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');

You can write a standard procedure to pin all of the recommended Oracle packages into the shared pool. Here is the script:

execute dbms_shared_pool.keep('DBMS_ALERT');
execute dbms_shared_pool.keep('DBMS_DDL');
execute dbms_shared_pool.keep('DBMS_UTILITY');
execute dbms_shared_pool.keep('STANDARD');

You can call this script at database startup. It will load all the required packages and procedures in the memory at startup.

Reference: http://www.dba-oracle.com/art_proc.htm

Getting Cumulative Sum Using Oracle Analytical Functions

2008-04-18T12:39:00.004-05:00

http://www.oracle.com/technology/oramag/code/tips2005/062005.html
http://www.akadia.com/services/ora_analytic_functions.html

Getting Cumulative Sum Using Oracle Analytical Functions
This tip comes from Robert Ware, DBA at TekSystems, in St. Louis, Missouri.

The following code is an alternative way to get a cumulative sum by using Oracle Analytical Function. (This is an alternative approach to the "Getting Cumulative Sum" code tip of May 23, 2005.)
SQL> select deptno,
2 ename,
3 sal,
4 sum(sal) over (partition by deptno
5 order by sal,ename) CumDeptTot,
6 sum(sal) over (partition by deptno) SalByDept,
7 sum(sal) over (order by deptno, sal) CumTot,
8 sum(sal) over () TotSal
9 from emp
10 order by deptno, sal;
DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 8750 1300 29025
10 CLARK 2450 3750 8750 3750 29025
10 KING 5000 8750 8750 8750 29025
20 SMITH 800 800 10875 9550 29025
20 ADAMS 1100 1900 10875 10650 29025
20 JONES 2975 4875 10875 13625 29025
20 FORD 3000 7875 10875 19625 29025
20 SCOTT 3000 10875 10875 19625 29025
30 JAMES 950 950 9400 20575 29025
30 MARTIN 1250 2200 9400 23075 29025
30 WARD 1250 3450 9400 23075 29025
30 TURNER 1500 4950 9400 24575 29025
30 ALLEN 1600 6550 9400 26175 29025
30 BLAKE 2850 9400 9400 29025 29025
14 rows selected.
sum(sal) over (partition by deptno order by sal, ename) CumDeptTot: Computes a running total on salaries within a department, in order of salaries from lowest salary to the highest. Adding the ORDER BY clause changed the behavior of the analytic function. Instead of working on every row in the partition, it worked only on the current and prior rows in the partition after sorting by SAL.
sum(sal) over (partition by deptno) SalByDept: Computes a department total. The SAL column was summed by department now. The individual EMP records can compare their salary against the total salary made in that department. The PARTITION keyword breaks up our result set into virtual partitions on which the analytic functions will be applied.
sum(sal) over (order by deptno, sal) CumTot: Creates a running total of the SAL column after the data was sorted by DEPTNO and then SAL. Since out entire result set would be sorted this way, it becomes a running total for our entire result set.
sum(sal) over () TotSal: Computes a grand total. This is equivalent to select sum(sal) from emp, but that query would return only one row. Here we get the sum of SAL associated with each detail row. We can easily compare each salary in the EMP table to the grand total sum of SAL.

Which Shell I am using in Unix

2008-04-17T13:59:00.003-05:00

Try following commands:

$echo $0
OR
$finger -m
OR
$echo $SHELL
OR
$ps -p $$

Query to find current version of Oracle

2008-04-16T09:04:00.003-05:00

There are two ways to find out the current version of Oracle using sqlplus.

First is is when you start sqlplus, it will display the version. It will look something like this:

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Apr 16 10:06:34 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name:

Second way is to run following query :

SQL> select * from v$version ;

Connected to:Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit ProductionWith the Partitioning optionJServer Release 9.2.0.8.0 - Production

Vi Editor Settings

2008-04-11T09:42:00.002-05:00

http://www.eng.hawaii.edu/Tutor/vi.html#settings

Meet people who discuss and share your passions. Join them now.

Good Topics

2008-03-31T15:54:00.007-05:00

Dictionary managed tablespaces
Extents are allocated according to the following storage parameters
initial
next
pctincrease
The information about used and free extents is stored in the dictionary.
Locally managed tablespaces
A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used.
The extent sizes are either uniform or autoallocate. Hence, the following storage parameters don't make sense and are not permitted:
next
pctincrease
minextents
maxextents
default storage
Locally managed tablespaces have two advantages: recursive space management is avoided and adjacent free space is automatically coalesced.

REF CURSOR Overview
A cursor variable is a cursor that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable.
A cursor variable is not tied to a single particular query like a static cursor. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable.
REF CURSOR types may be passed as parameters to or from stored procedures and functions. The return type of a function may also be a REF CURSOR type. This provides the capability to modularize the operations on a cursor into separate programs by passing a cursor variable between programs.
We can define ref cursor with SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR.
Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types.
The following is the syntax for declaring a SYS_REFCURSOR cursor variable: name SYS_REFCURSOR;
DECLARE
emp_refcur SYS_REFCURSOR;
You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:
1. Create a referenced cursor TYPE
2. Declare the actual cursor variable based on that TYPE
The syntax for creating a user defined REF CURSOR type is as follows: TYPE cursor_type_name IS REF CURSOR [RETURN return_type];
The following is an example of a cursor variable declaration. DECLARE
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
my_rec emp_cur_type;
CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno emp.deptno%TYPE
)
IS
emp_refcur SYS_REFCURSOR;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno ' ' v_ename);
END LOOP;
CLOSE emp_refcur;
END;

A VARRAY is a collection with maximum limit set. A VARRAY can hold any defined datatype. A VARRAY is a dense collection, meaning that you can only add or remove objects from the end. You define a VARRAY first as a type, and then define a variable that is the actual collection.
SQL> declare 2 type auth_var is varray(10) of 3 author.author_last_name%type; 4 a_auth auth_var := auth_var(); --Initilization 5 begin 6 a_auth.extend; 7 a_auth(1) := 'Arpit'; 8 a_auth.extend; 9 a_auth(a_auth.last) := 'Nirav';
10 a_auth.extend; 11 a_auth(a_auth.last) := 'Amit'; 10 dbms_output.put_line( a_auth(1) ' and ' a_auth(2)); 11 end; /
A VARRAY can not be used until it is initialized. In line 4 the auth_var() function actually does the initialization.
Once the array is initialized you can extend it and add elements, which we do in lines 6 through 9. We access each element using the VARRAY variable and the index number. When the PL/SQL block ends (or the array variable goes out of scope), the memory used by the array is recovered automatically by the PL/SQL engine. Unlike a cursor, you do not close a collection.

10g New Features:
Provides support for regular expressions as in Unix/Perl
Ability to receive warning when compiling the code. We can get warnings using parameter plsql_warning or DBMS_WARNING package.
BINARY_FLOAT / BINARY_DOUBLE (Native floating point data types and is alternative to NUMBER datatype)
DBMS_LOB offers support of large LOBs (between 8 and 12 terrabytes)
Handling of quotes in string (') can be done using q'! <text with quotes...>!'
ErWin

When you create a relationship between entities, ERwin automatically migrates the primary key attribute(s) of the parent entity, to the child entity. The designator (FK) indicates the name of the migrated key attribute, also known as a foreign key. This migration occurs automatically in the physical model.
In an identifying relationship (displayed using solid line), the FK migrates above the line and becomes part of the primary key of the child entity. In a non-identifying relationship (displayed using dotted line), the foreign key migrates below the line and becomes a non-key attribute in the child entity. In ERwin, a dashed line represents a non-identifying relationship.

Data warehousing questions
What is a Star Schema?
Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

What is Snow Flake Schema?
Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Differences between star and snowflake schema?
Star schema - A single fact table with N number of Dimension, all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results.
Snow schema - Any dimensions with extended dimensions are know as snowflake schema, dimensions maybe interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

What is Difference between ER Modeling and Dimensional Modeling?
ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is degenerate dimension table?
If a table contains the values, which are neither dimension nor measures is called degenerate dimensions.

Why is Data Modeling Important?
Data modeling is probably the most labor intensive and time consuming part of the development process. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. When data modelling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure.Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video. (Reference : Wikipedia)

Query Examples [Delete Duplicate, Top-N Analysis, Alternate Records]

2008-03-28T11:23:00.016-05:00

SQL Query to delete duplicate records

delete from t2 a
where a.rowid > (select min(rowid)
from t2 b
where a.c1 = b.c1)

delete from t2 a
where a.rowid not in (select max(rowid)
from t2 b
where a.c1 = b.c1)

Query to select nth highest row in the table:
1] select a.sal
from emp a
where (&n-1) = (select count(distinct(b.sal))
from emp b
where b.sal > a.sal)
Oracle 10g new feature : Handling of quotes in string
insert into emp
values (1234, q'!Arpit'sdfs!', 'Engg', 7902, '24-feb-1968',2334, 12 , NULL)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
2] SELECT * FROM ( SELECT deptno, ename, sal, DENSE_RANK() OVER ( PARTITION BY deptno ORDER BY sal desc ) TopN FROM emp)
WHERE TopN <= 3ORDER BY deptno, sal DESC/

3] SELECT * FROM ( SELECT deptno, ename, sal, ROW_NUMBER() OVER ( PARTITION BY deptno ORDER BY sal DESC ) Top3 FROM emp)
WHERE Top3 <= 3 ;

[Gives only first 3. Duplicates r counted as 2]

Query to select alternate records from table

SELECT *
FROM emp
WHERE (rowid,1) IN (SELECT rowid,mod(rownum,2) FROM emp);

SELECT * FROM ( SELECT rownum r, emp.* FROM emp ) temp WHERE MOD(temp.r,2) = 1 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1234 Arpit'sdfs Engg 7902 24-FEB-68 2334 12
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL Report in CSV format with column heading

2008-03-12T10:47:00.004-05:00

In SQL Reports, if you use "set pages 0" then it will remove heading from the report. The solution is to
either use "set pages 99999", which will create page break after specified number of pages or to use "set emb on pages 0 newp 0"

Report::::

set feedback off;
set heading on;
set pages 9999;
set lines 365;
set underline off;
set colsep ',';
spool c:\try.csv
select empno as emp_no from emp ;.
spool off;

To convert Oracle forms 3.0 inp to fmb (Oracle 4.5/ 6i)

2008-03-10T14:58:00.002-05:00

To convert forms 3.0 inp to fmb

Open Start > Programs > Oracle Developer Suite > Forms Developer > forms Compiler

1. Select inp file
2. Give uid,pwd, db
3. Tick Upgrade 3.0 Forms or 5.0 Menu to current release.
4. Tick Widen Field Display length by 1 when upgrading
5. Uncheck Build a runform/runmenu file when upgrading.
6. Version to upgrade : 30
7. Tick Compile all PL/SQL code
8. Tick Strip PL/SQL source from library.
9. Upgrade PL/SQL from V1 to V2.
10. Write output to File :: Give output file name and path
11. Tick Add key-up and down triggers when upgrading.

Unlimited freedom, unlimited storage. Get it now

Developer 2000 installation configuration

2008-03-10T14:56:00.001-05:00

After installing D2K forms 6, you are required to configure tnsnames.ora
file.
Location of tnsnames.ora file.

C:\DevSuiteHome\network\admin

C:\devsuitehome is the folder path in which u have installed oracle forms.

OPEN tnsnames.ora.

Add following lines in it.

As a host you required to give ip or pc name of pc in which oracle
database is installed to which u want to connect.
Port is port at which oracle db listener is installed. It will be 1521
fixed in most of the cases so dont need to change.

And service name is name of SID of Oracle database.

XYZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.6.0.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ABCD)
)
)

After adding this lines in tnsnames.ora file you are ready to use D2K.

In the Connect dialog box give user name , password, and database which
the one you have specified in tnsnames.ora. In above case it will be XYZ.

Get the freedom to save as many mails as you wish. Click here to know how.

Session id of current user who is using Oracle ias form or sqlplus

2008-03-10T14:50:00.002-05:00

Oracle fuction to know session id of current user who is using either
ias form or sqlplus.

SELECT 'SID='TO_CHAR(SID)' SERIAL#='TO_CHAR(SERIAL#)'
AUDSID='sys_context('USERENV','SESSIONID')
' OSUSER='OSUSER' MACHINE='MACHINE' 'NVL(TERMINAL,'')
INTO :session_info
FROM V$SESSION
WHERE AUDSID= sys_context('USERENV','SESSIONID');

select sys_context('USERENV','SESSIONID') from dual;

Using sys_context('USERENV','SESSIONID') this function you
will get audsid of current user and then
by using this id on V$session table
you will get detail like sid,session#,
machine through which user is using sqlplus.

Chat on a cool, new interface. No download required. Click here.

Converting Number to Text using Oracle SQL Explained

2008-03-10T14:32:00.001-05:00

Query:
select to_char(to_date('456','J'),'Jsp') --replace 456 with your number or variablefrom dual
Output:
Four Hundred Fifty-Six

How it works:
Step 1:
Following query will convert your number to date format. As this is number we need to specify 'J'ulian format.

Query:
select to_date('456','J') --replace 456 with your number or variablefrom emp
Output:
01-APR-11

Step 2:
Following query will convert your number (date in 'J'ulian format) to text. If you write 'sp' after any format, the format will be spelled. So the number 456 will be converted to text as "Four Hundred Fifty-Six "

Query:
select to_char(to_date('456','J'),'Jsp') --replace 456 with your number or variablefrom dual
Output:
Four Hundred Fifty-Six

US Elcetions Explained

2008-03-09T16:38:00.000-05:00

In US there are only two parties, namely Democratic and Republican. US presidential election occurs after every four years. In US Congress consist of two parts. House of Representatives and Senators. There are two senators from every state where as number of house of representatives will depend on population of particular state. Currently total senators are 100 and House of representatives are 435. And 3 members from Washington D.C. . So in total there are 538 members of House of Representatives.
Before more than 1 year of election both the parties will start searching for suitable candidate for presidential election. In US presidential candidate will also be chosen by people. For that in every state primary or caucus are organised. Primary or caucus are almost same as election. But they will be held by state government and one for each party. People who are loyal to particular party will participate in Primary/Caucus for that party. In primary people will caste there vote and based on majority, the candidate will be selected. In caucus people marketing for particular candidate will discuss about the candidates with other people in group. The candidate, whose group are smaller will have to merge into other groups. And finally there will be elections between two groups.

Use of truss and lsof in Unix

2008-03-09T16:27:00.001-05:00

First command is truss.
The truss utility executes the specified command and produces a trace of the system calls it performs, the signals it receives, and the machine faults it incurs.
Each line of the trace output reports either the fault or signal name or the system call name with its arguments and return value(s).The command can be used as::: truss ./abc [abc is executable program]On following link you can find examples of the same.
http://www.ibm.com/developerworks/aix/library/au-unix-tracingapps.html
Another useful command is lsof.
lsof lists all the open files used by all the programs.
An open file may be a regular file, a directory, a block special file, a character special file, an executing text reference, a library, a stream or a network file.lsof can also be used to retrieve accidently deleted files, if they are currently being used by any process.On following link you can find examples of the same.
http://www.ibm.com/developerworks/aix/library/au-lsof.html?S_TACT=105AGX20&S_CMP=EDU