Tuesday, June 22, 2010
New learning in PLSQL - NO_DATA_NEEDED exception
I have many times come across the NO_DATA_FOUND exception. Thanks for Asktom site for pointing me to NO_DATA_NEEDED exception. There are always enough things to learn as long as mind is keen to observe around.
Thursday, June 3, 2010
H2 Database Engine
After a few days of working with databases, I came across an in-memory DB named H2 database.
CRUISE is an wonderful continuous integration build enabler and this product uses H2 database. Hope to dig this tool a bit.
More details about this can be found at http://www.h2database.com/html/main.html
Thursday, April 8, 2010
Reading data from SQL Server from MS Access
Wow ! I have the way to pull data from SQL server .. sitting in the MS Access application. Thanks to the ODBC linking views available.
Wednesday, April 7, 2010
Hierarchical queries (or recursive queries) in SQL Server 2005/2008
The question is ... how do you run hierarchical queries in SQL server ? I was trying to find something similar to CONNECT BY..PRIOR clause in Oracle. We stepped in to CTE (Common Table Expression). Awesome feature !
* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)
WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr
WHERE mgr_id is null
UNION ALL
-- Recursive member definition
SELECT e.mgr_id, e.emp_id,Level + 1
FROM test_emp_mgr AS e
INNER JOIN DirectReports AS d
ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;
* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)
WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr
WHERE mgr_id is null
UNION ALL
-- Recursive member definition
SELECT e.mgr_id, e.emp_id,Level + 1
FROM test_emp_mgr AS e
INNER JOIN DirectReports AS d
ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;
Tuesday, February 2, 2010
How to know the sessions active in your Oracle database ?
The following query should give you the summary of users and the number of sessions held by them.
SQL> select schemaname, session_count from
(select distinct schemaname, count(sid) as session_count from v$session group by schemaname)
order by session_count desc;
-- To know the total number of sessions in progress...
SQL> select count(*) from v$session;
SQL> select schemaname, session_count from
(select distinct schemaname, count(sid) as session_count from v$session group by schemaname)
order by session_count desc;
-- To know the total number of sessions in progress...
SQL> select count(*) from v$session;
Subscribe to:
Posts (Atom)