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
Tuesday, October 28, 2008
Oracle Trigger: Mutating Table
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.
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
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.
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.
Monday, October 27, 2008
Happy Diwali
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
Tuesday, September 30, 2008
Using Cursor Expressions [Cursor in Select statement]
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".
Tuesday, September 9, 2008
Art Of Living Birthday Song
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)
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)
Friday, August 15, 2008
How to check if environment variable is defined or not in Unix?
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.
---------------------------------------------------------------
---------------------------------------------------------------
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.
---------------------------------------------------------------
Thursday, May 29, 2008
Example of REGEXP_LIKE and explanation of back references
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
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
Wednesday, May 28, 2008
My Trip to Niagara Fall
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.
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
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
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
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%'
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%'
Friday, May 2, 2008
Create virtual rows
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 ;
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 ;
Thursday, May 1, 2008
how to remove carriage return line feed from file in unix
#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
#
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
#
Wednesday, April 30, 2008
Unix: Search for a pattern in all the files in all the directory
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
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?
This is really good link.
It explains how the numbers are converted from Oracle internal format to real format (decimal/hexa..).
It explains how the numbers are converted from Oracle internal format to real format (decimal/hexa..).
Monday, April 28, 2008
How do i store the row counts of all tables …
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
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
Friday, April 25, 2008
Wednesday, April 23, 2008
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
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.
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.
Tuesday, April 22, 2008
SCN, the System Change Number
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
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
Monday, April 21, 2008
How Lenders Interpret Your Credit Report
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.
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
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
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
Friday, April 18, 2008
Getting Cumulative Sum Using Oracle Analytical Functions
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.
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.
Thursday, April 17, 2008
Which Shell I am using in Unix
Try following commands:
$echo $0
OR
$finger -m
OR
$echo $SHELL
OR
$ps -p $$
$echo $0
OR
$finger -m
OR
$echo $SHELL
OR
$ps -p $$
Wednesday, April 16, 2008
Query to find current version of Oracle
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
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
Friday, April 11, 2008
Monday, March 31, 2008
Good Topics
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)
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)
Friday, March 28, 2008
Query Examples [Delete Duplicate, Top-N Analysis, Alternate Records]
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
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
Wednesday, March 12, 2008
SQL Report in CSV format with column heading
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;
Monday, March 10, 2008
To convert Oracle forms 3.0 inp to fmb (Oracle 4.5/ 6i)
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
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
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
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
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
Sunday, March 9, 2008
US Elcetions Explained
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.
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
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
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
Subscribe to:
Posts (Atom)
