If you want to know the open or active or inactive connections with Oracle DB,
1. Login to that Oracle schema (may be through Toad or Oracle etc) and
2. Run below sql
select sid,serial#,PROGRAM,machine,TO_CHAR(logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon,status from v$session where username='userNameWhichIWouldHaveUsedToConnect';
Showing posts with label db. Show all posts
Showing posts with label db. Show all posts
Monday, January 9, 2012
Monday, July 18, 2011
SQL INSERT syntax to insert a record when no such entry in table! (using MERGE)
Requirement:
Construct a sql query to INSERT the record to table only if there is NO such record in table.
Example:
Insert the new person details to PERSON table only when there is no PERSON with ID 1234.
Background:
If there is a limitation on JAVA layer to handle inserts for existing record in table, then we've to rely on SQL statement to handle this.
If you use iBATIS ORM for batch operations, operation might fail for insert if there is already a record in table (person with ID 1234) you are trying to insert!
Solution:
Instead of having plain INSERT statement, build the needed intelligence in SQL using MERGE statement like below:
Construct a sql query to INSERT the record to table only if there is NO such record in table.
Example:
Insert the new person details to PERSON table only when there is no PERSON with ID 1234.
Background:
If there is a limitation on JAVA layer to handle inserts for existing record in table, then we've to rely on SQL statement to handle this.
If you use iBATIS ORM for batch operations, operation might fail for insert if there is already a record in table (person with ID 1234) you are trying to insert!
Solution:
Instead of having plain INSERT statement, build the needed intelligence in SQL using MERGE statement like below:
MERGE INTO PERSON target USING
(SELECT count(1) record FROM PERSON WHERE ID = '1234') source
ON (source.record = 0)
WHEN MATCHED THEN
INSERT
(PERSON_ID, PERSON_NAME)
VALUES (1234,'RAGSETTY')
Subscribe to:
Posts (Atom)