I wanted to remove sequencenumber tag along with value within tags from following line. I also wanted to move tags salary and suffix to first position after begin.
Following script can help:
$ sed 's:\(
Wednesday, August 28, 2013
Unix sed: Modify xml file or move characters around
Following example can be used when we want to move characters around in the file based on xml tags or other character prefixes.
I wanted to remove sequencenumber tag along with value within tags from following line. I also wanted to move tags salary and suffix to first position after begin.
101886122 arpit 23452345 shah 116056 III
Following script can help:
$ sed 's:\(\)\(.* \)\(.*last>\)\(.*suffix>\)\(.*\):\1\4\3\5:' a
116056 III arpit 23452345 shah
I wanted to remove sequencenumber tag along with value within tags from following line. I also wanted to move tags salary and suffix to first position after begin.
Following script can help:
$ sed 's:\(
Friday, January 11, 2013
My (Arpit's) Art of Living Experience in Initial Days
A lucky break (or, really, drag)
What’s incredible about my story is that my friend dragged me along to this course, which I had no intention of attending, but then, somehow, I liked it. And he…didn’t. Fast forward to now, and I have no idea what my life would be like without it had he not done that that day. What an unrepayable debt I owe to him!! I’m actually not sure if he’s done it yet or not – I will have to check and make him do it soon : ).
Pray, don’t spray
All was well — for a while. The course was going fine, but when I started doing that vigorous breathing exercise (what did they call it?), snot came flying out of my nose in ridiculous quantities. I was worried!!! What would the people around me think? It is actually a miracle, in retrospect, that I stuck with it through all that. But, I guess with the power of the Kriya and pranayams, don’t you know it, in a couple of months, it went away!!!
Tongue (and body)-tied
One thing that I noticed after the course was that I was able to speak. Not that I wasn’t able to speak before – I’m talking about giving speeches. When I used to do it, I would shake like crazy, jumble up my words like I didn’t know my own language, and rush through it, barely enunciating and hardly giving anyone a chance to hear or think. But somehow, after some simple body-breath-coordination exercises, it was gone – not completely, but a lot. Before, when I would give presentations for whatever reason in school, I would always rank near the bottom – I don’t even know how low, they didn’t tell me (probably good, to save my feelings). But the first time I spoke after the course, I got third out of a large group!!!! AMAZING!!!!
Above mentioned is true. I had help of Great writer Sanjay Kapoor in writing and adding images. Thanks Sanjay!
Monday, October 1, 2012
My Aunt Polly Game
|
Likes
|
Dislikes
|
|
Soccer,
Football
|
Cricket
|
|
Nashville
|
Mt
Juliet
|
|
Trees
|
Plants
|
|
Moon
|
Stars
|
|
Speed
|
Fast
|
|
Food
|
Eat
|
|
Tennessee
|
Kentucky
|
|
Jeff,
Willie
|
Everybody
else
|
|
Glass
|
Window
|
|
Beer
|
Wine
|
For secret of the game, please scroll down.
My aunt Polly like each word with double letters.
Tuesday, May 1, 2012
Query to find unused (not referenced in other DB packages, procedures, function) package
Following query can be used to find unused (not referenced in other DB packages, procedures, function) packages. Packages can still be used in DBMS Jobs / Scheduler Jobs / Application
select distinct owner || '.' || name
from dba_DEPENDENCIES
where name in (
select referenced_name
from dba_DEPENDENCIES
where type != 'SYNONYM'
and owner in ('SCOTT', 'SCOTT2')
group by referenced_name
having count(*) = 1)
and owner in ('SCOTT', 'SCOTT2')
and type in ('PACKAGE BODY', 'PACKAGE')
order by 1
Friday, April 27, 2012
Oracle ACL Error Resolution:ORA-24247: network access denied by access control list (ACL)
We were often seeing following network ACL error.
ORA-24247: network access denied by access control list (ACL)
Query to check for existing ACLs.
SELECT NACL.ACLID,ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE NACL.ACLID = ACE.ACLID ;
We had to run following steps to resolve the issue.
EXEC DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'mails.xml' );
EXEC DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'mails.xml', description => 'Mail ACL', principal => 'SCOTT', is_grant => TRUE, PRIVILEGE => 'connect');
Principal ==> says which schema will primarily own the ACL
is_grant => TRUE will allow other schema in DB to access this ACL (this line needs verification)
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (acl => 'mails.xml',
principal => 'SCOTT', --Schema name
is_grant => TRUE, --same as above
PRIVILEGE => 'connect',
position => NULL,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'mails.xml', HOST => 'mailhost', lower_port => 21, upper_port => 30);
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (acl => 'mails.xml',
principal => 'SCHEMA2',
is_grant => TRUE,
PRIVILEGE => 'connect',
position => NULL,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
For more details: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm
Parallel Hint
SELECT /*+ PARALLEL(a) */ COUNT(*) FROM ABCD a ;
Parallel Hint makes operation faster. It does so by providing more threads to the current SQL statement. Not advisable to run in Production Environment during Peak Hours.
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.
Wednesday, January 25, 2012
Oracle Unique Constraint sys.i_procedure1 violated while creating package
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$.
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$.
Thursday, December 8, 2011
Oracle XML to TABLE using XMLTABLE
Query:
SELECT seq
, ID
, NAME
FROM XMLTABLE('/xml/emp'
PASSING XMLTYPE('Arpit Venkat
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
SELECT seq
, ID
, NAME
FROM XMLTABLE('/xml/emp'
PASSING XMLTYPE('
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
Friday, January 28, 2011
Escape / (Forward Slash) in Oracle SQL*Plus
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 ;
/
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 ;
/
Wednesday, October 6, 2010
Query to run Dynamic SQL Using XML on SQL> prompt
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.
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.
Tuesday, August 3, 2010
Wednesday, June 30, 2010
Awk Script to Find Start Time End Time and Diff
[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
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
Tuesday, June 22, 2010
Partitioning
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
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
Wednesday, November 4, 2009
Parallel Hint
SELECT /*+ PARALLEL(a) */ COUNT(*) FROM ABCD a ;
Parallel Hint makes operation faster. It does so by providing more threads to the current SQL statement. Not advisable to run in Production Environment during Peak Hours.
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.
Wednesday, August 5, 2009
When We are Joyful
WHEN WE ARE JOYFUL
When we are joyful, we don't look for perfection. If you are looking for perfection then you are not at the source of joy. Joy is the realization that there is no vacation from wisdom. The world appears imperfect on the surface but underneath, all is perfect. Perfection hides; imperfection shows off.
The wise will not stay on the surface but will probe into the depth. Things are not blurred; your vision is blurred. Infinite actions prevail in the wholeness of consciousness. And yet the consciousness remains perfect, untouched. As Satsangees, realize this now and be at Home.
Regards,
Arpit Shah
See the Web's breaking stories, chosen by people like you. Check out Yahoo! Buzz.
Friday, July 10, 2009
Smart Choice for Cursor Processing
It's nice article desribing when to use
CURSOR FOR Loops
Avoid using it
SELECT INTO
Use when query can return atmost one row. Also put SELECT statements in seperate PROCEDURES/FUNCTION. Which can be optimized/cached in Oracle 11g.
CURSOR BULK COLLECT VARRAY [Fixed number of rows or less]
Use when SELECT query will return multiple rows but you know upper limit. If upper limit is very high say 10000, you may want to go for next approach. As it will consume lots of memory.
CURSOR BULK COLLECT NESTED ARRAY with LIMITS
Use when SELECT query will fetch multiple rows and you don't know the upper limit or you know the upper limit but it is very high.
More Detail with examples at:
http://www.oracle.com/technology/oramag/oracle/08-nov/o68plsql.html
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
Monday, July 6, 2009
Unix to Dos [How to write Control Character in Unix File]
Suppose following is your file. If you move the file from Windows to Unix, you will see Control M (^M) character at the end of each line.
File in Windows:
export USERNAME
export PASSWORD
export CTLPATH
Same File when you move it to Unix
export USERNAME^M
export PASSWORD^M
export CTLPATH^M
^M
You can see the special characters because Windows has [Carriage Return Line Feed] CRLF (\r\n) as line terminating sequence. Where as Unix has only [Line Feed] LF (\n) as line terminator.
So, when we move file from Dos (Windows) to Unix, we need some kind of special processing which will convert CRLF into LF.
One is to open the file and replace Control M character visible in file to blank. To replace Control M character one should know how to create Control M character in Unix.
Folloing is the way to create Control M character in Unix.
Open File in vi editor.
Come in Instert mode by pressing Esc i
Press Control V
Then Press Control M
Then Esc.
It will generate Control M.
Whatever control character you want to generate, first press Control + V and then Control +
So convert file from Dos to Unix follow the steps mentioned below:
Open file in vi.
Type following command.
:0,$s/^M//
The above command will remove Control M (^M) character from entire file.
I will mention other utilities to convert from Dos to Unix sometime later.
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.
Wednesday, May 27, 2009
SQL Report Formatting Using HTML
See the magic. Following block of code will format the report in tabular format. The best way to generate SQL Report. Using it, you can view it in Browser. You can open it in Excel.
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL employee.htm
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
Tuesday, May 5, 2009
Fw: Love - The Question of an answer
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.
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.
Friday, April 17, 2009
Intelligent people celebrate diversity
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.
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.
Subscribe to:
Posts (Atom)
