--Hierarchical queries
--Top down
select lpad(ename,length(ename)-1+level,'****') from emp connect by prior empno=mgr start with mgr is null;
--Down--up
SELECT LPAD (' ', LEVEL * 3 - 3)||ENAME ENAME
FROM EMP
CONNECT BY PRIOR mgr = empno
START WITH MGR=7788;
--Write a query to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).
with t as ( select level n
from dual
connect by level <= 1000)
select listagg(N,'&') within group(order by n) from (select n,
case
when exists
(select null
from t t2
where t2.n > 1 and t2.n < t.n and t.n / t2.n = trunc (t.n / t2.n)) then 'no prime'
else 'prime'
end is_prime
from t t where n<>1 ) where is_prime='prime';
P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):
*
* *
* * *
* * * *
* * * * *
Write a query to print the pattern P(20).
select lpad('* ',level*2,'* ') from dual connect by rownum< 21;--P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):
* * * * *
* * * *
* * *
* *
*
Write a query to print the pattern P(20).
select * from (select lpad('* ',level*2,'* ') t from dual connect by rownum< 21) order by length(t) desc;
--Analytical queries
Query to find out departments those are having maximum number of employees. In other words you can say the customer who placed maximum number of orders.
select deptno from emp group by deptno having count(*)=(select max(count(*)) from emp e group by e.deptno);
--order by analytical clause
select distinct deptno from (select deptno,dense_rank() over (order by cnt desc) rnk from(select deptno,count(*) over (partition by deptno) cnt from emp order by cnt desc)) where rnk=1
--Analytical functions those do not need ordering of the elements.
--sum
select a.*,sum(sal) over (partition by deptno) part_tot_sal from emp a;
--In the absence of partition analytical functions work on entire set of rows.
--count
select a.*,count(ename) over (partition by deptno) part_tot_emp from emp a;
--avg
select a.*,trunc(avg(sal) over (partition by deptno)) part_avg_sal from emp a;
--min
select a.*,min(sal) over (partition by deptno) part_min_sal from emp a;
--max
select a.*,max(sal) over (partition by deptno) part_max_sal from emp a;
--Analytical function those require order by clause. Notice that you don't really need to give input parameter to these functions
--row_number()--I can be used in TOP-N(top 5,last 5 or like 90-100 number of records from rows)
select a.*,row_number() over (order by sal desc NULLS LAST) part_max_sal from emp a;
--Numbering with partition
select a.*,row_number() over (partition by deptno order by sal desc NULLS LAST) part_max_sal from emp a;
--rank()--To define rank-- In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.
--dense_rank()
select a.*,row_number() over (order by sal desc NULLS LAST) row_order,rank() over (order by sal desc NULLS LAST) rank_order ,dense_rank() over (order by sal desc NULLS LAST) dense_rank_order from emp a;
--LEAD,LAG--Note that it needs column on which it needs to be applied
select a.*,row_number() over (order by sal desc NULLS LAST) row_order,rank() over (order by sal desc NULLS LAST) rank_order ,dense_rank() over (order by sal desc NULLS LAST) dense_rank_order,
lead(sal,1,0) over(partition by deptno order by sal desc nulls last) next_sal,lag(sal,1,0) over (partition by deptno order by sal desc nulls last) prev_sal from emp a;
--FIRST_VALUE and LAST_VALUE
select a.*,first_value(sal) over (partition by deptno order by sal desc nulls last) first_sal,last_value(sal) over (partition by deptno order by sal desc nulls last) last_sal from emp a;
-- How each employee's salary compare with the average salary of the first
-- year hires of their department?
--This usage FIRST
SELECT empno, deptno, TO_CHAR(hiredate,'YYYY') HIRE_YR, sal,
TRUNC(
AVG(sal) KEEP (DENSE_RANK FIRST
ORDER BY TO_CHAR(hiredate,'YYYY') )
OVER (PARTITION BY deptno)
) AVG_SAL_YR1_HIRE
FROM emp
WHERE deptno IN (20, 10)
ORDER BY deptno, empno, HIRE_YR;
--Find total employee count under manager down the whole hierarchy path
select ename, count (*) - 1 cnt
from (select connect_by_root ename ename
from emp
connect by prior empno=mgr)
group by ename having count(*) > 1
order by cnt desc;
--Bulk collection practice
CREATE TABLE bulk_collect_test AS
SELECT owner,
object_name
name,
object_id
FROM all_objects;
create table bulk_collect_test1 as select * from bulk_collect_test where 1=2;
DECLARE
CURSOR cur
IS
SELECT * FROM bulk_collect_test;
TYPE t_cur IS TABLE OF cur%rowtype;
v_cur t_cur;
l_limit NUMBER:=100;
BEGIN
OPEN cur;
LOOP
FETCH cur BULK COLLECT INTO v_cur LIMIT l_limit;
FORALL i IN 1 .. v_cur.COUNT
INSERT INTO bulk_collect_test1
VALUES (v_cur (i).owner, v_cur (i).name, v_cur (i).object_id);
exit when v_cur.count<l_limit;
END LOOP;
close cur;
--Another way by using direct select. In this way we cannot apply limit
select * bulk collect into v_cur from bulk_collect_test;
FORALL i IN 1 .. v_cur.COUNT
INSERT INTO bulk_collect_test1
VALUES v_cur (i);
commit;
END;
/
--SQL%BULK_ROWCOUNT example
--It gives granular information of rows affected by each iteration of forall statement
--above code can be re-written by using SQL%BULK_ROWCOUNT
DECLARE
TYPE t_array_tab IS TABLE OF VARCHAR2 (30);
l_array t_array_tab
:= t_array_tab ('SCOTT',
'SYS',
'SYSTEM',
'DBSNMP',
'BANANA');
BEGIN
FORALL i IN l_array.FIRST .. l_array.LAST
DELETE FROM bulk_collect_test1
WHERE owner = l_array (i);
FOR i IN l_array.FIRST .. l_array.LAST
LOOP
DBMS_OUTPUT.put_line (
'Records deleted for owner: '
|| RPAD(l_array (i),15,' ')
|| ' is: '
|| SQL%BULK_ROWCOUNT (i));
END LOOP;
COMMIT;
END;
/
--exception handling in forall
create table id_tab(id number not null);
DECLARE
TYPE t_table IS TABLE OF id_tab%ROWTYPE;
v_tab t_table := t_table ();
l_error_count NUMBER;
--Create exception and bind it with expected error code so that we can handle a named exception
dml_error EXCEPTION;
PRAGMA EXCEPTION_INIT (dml_error, -24381);
BEGIN
FOR i IN 1 .. 100
LOOP
v_tab.EXTEND;
v_tab (v_tab.LAST).id := i;
END LOOP;
--Creating error records as table does not allow null values
v_tab (50).id := NULL;
v_tab (61).id := NULL;
BEGIN
FORALL i IN v_tab.FIRST .. v_tab.LAST SAVE EXCEPTIONS
INSERT INTO id_tab
VALUES v_tab (i);
EXCEPTION
WHEN dml_error
THEN
l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE ('The number of errors: ' || l_error_count);
FOR i IN 1 .. l_error_count
LOOP
DBMS_OUTPUT.PUT_LINE (
'Index'
|| i
|| ' Array index: '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| ' Error: '
|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP;
END;
END;
/
---User defined exceptions
--Define exception and use it. Note that by default error code is 1 if no binding is used.
DECLARE
my_exception EXCEPTION;
-- PRAGMA EXCEPTION_INIT (my_exception, -20221);
BEGIN
RAISE my_exception;
EXCEPTION
WHEN my_exception
THEN
DBMS_OUTPUT.put_line (SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
END;
/
--Bind exception with error code
DECLARE
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception, -20221);
BEGIN
RAISE my_exception;
EXCEPTION
WHEN my_exception
THEN
DBMS_OUTPUT.put_line (SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
END;
/
--give description of error by using RAISE_APPLICATION_ERROR
DECLARE
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception, -20221);
BEGIN
RAISE_application_error(-20221, 'This is exception description');
EXCEPTION
WHEN my_exception
THEN
DBMS_OUTPUT.put_line (SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
END;
/
--Ref cursor advance options, return results as ref cursor without parameter.
CREATE table t1 (
id NUMBER,
description VARCHAR2(30),
created_date DATE
);
INSERT INTO t1 VALUES (1, 'The value 1', SYSDATE-2);
INSERT INTO t1 VALUES (2, 'The value 2', SYSDATE-1);
INSERT INTO t1 VALUES (3, 'The value 3', SYSDATE);
COMMIT;
--Now we can create a stored procedure to return one or more resultsets containing this data.
CREATE OR REPLACE PROCEDURE get_my_results (p_id IN NUMBER DEFAULT NULL)
AS
l_cursor_1 SYS_REFCURSOR;
l_cursor_2 SYS_REFCURSOR;
BEGIN
IF p_id IS NOT NULL THEN
OPEN l_cursor_1 FOR
SELECT description, created_date
FROM t1
WHERE id = p_id;
DBMS_SQL.RETURN_RESULT(l_cursor_1);
END IF;
OPEN l_cursor_2 FOR
SELECT COUNT(*)
FROM t1;
DBMS_SQL.RETURN_RESULT(l_cursor_2);
END;
/
exec get_my_results();
--Storing collections into database
--Nested table
create type t_nemp as OBJECT(ID NUMBER, NAME VARCHAR2(30));
create type t_nemplist as table of t_nemp;
create table t_emp (id number ,emps t_nemplist) nested table emps store as emp_table;
insert into t_emp values (10,t_nemplist(t_nemp(10,'Anand'),t_nemp(11,'Ved')));
insert into t_emp values (20,t_nemplist(t_nemp(21,'Jitendra'),t_nemp(25,'Ansh')));
select * from t_emp;
--Associative arrays.
create type t_aaemp as OBJECT(ID NUMBER, NAME VARCHAR2(30));
--You notice that below line gives errors and cannot be stored in the database. It proves that associative arrays cannot be stored in database.
create type t_aaemplist as table of t_nemp index by pls_integer;
--varrays
create or replace type t_vemp as OBJECT(ID NUMBER, NAME VARCHAR2(30));
create or replace type t_vemplist as VARRAY(10) OF t_vemp;
create table t_vaemp (id number ,emps t_vemplist);
insert into t_vaemp values (10,t_vemplist(t_vemp(10,'Anand'),t_vemp(11,'Ved')));
insert into t_vaemp values (20,t_vemplist(t_vemp(21,'Jitendra'),t_vemp(25,'Ansh')));
select * from t_vaemp;
--Deadlocks
--Deadlock by bitmap indexes
create table lock1(id number);
create bitmap index lock1_idx on lock1(id);
--From session #1
insert into lock1 values(1);
--from session#2
insert into lock1 values(2);
insert into lock1 values(1);
--Back to session #1
insert into lock1 values(2);
--Bamm ...deadlock detected
--Deadloacks by foreign keys(parent-child hierarchy)
create table lock1(id number);
alter table lock1 add constraint lock1_uk unique(id);
create table lock2(id number);
alter table lock2 add constraint lock2_lock1_fk foreign key(id) references lock1(id);
--From session #1
insert into lock1 values(1);
--from session#2
insert into lock1 values(2);
insert into lock2 values(1);
--Back to session #1--any of below
insert into lock2 values(2);
insert into lock1 values(2);
--Bamn...Deadlock
--Deadlock in autonomous transaction
CREATE OR REPLACE PROCEDURE auto_deadlock_test (id IN NUMBER)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO lock2
VALUES (id);
COMMIT;
END;
/
BEGIN
INSERT INTO lock1
VALUES (1);
auto_deadlock_test (1);
END;
/
--Bamn...Deadlock detected
--Mutating trigger error solution...real example
CREATE OR REPLACE PACKAGE state_pkg
AS
v_is_primary_relation md_table_relation.is_primary_relation%TYPE;
v_from_table_name md_table_relation.from_table_name%TYPE;
v_to_table_name md_table_relation.to_table_name%TYPE;
v_from_schema_name md_table_relation.from_schema_name%TYPE;
v_to_schema_name md_table_relation.to_schema_name%TYPE;
v_schema_name MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_SCHEMA%TYPE;
v_table_name MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_TABLE%TYPE;
v_column_name MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_COLUMN%TYPE;
v_dimension_name MD_HIERARCHY.DIMENSION_NAME%type;
v_hierarchy_name MD_HIERARCHY.HIERARCHY_NAME%type;
v_oldispwbenabled MD_HIERARCHY.IS_PWB_ENABLED%type;
v_newispwbenabled MD_HIERARCHY.IS_PWB_ENABLED%type;
END;
/
CREATE OR REPLACE TRIGGER BIU_MD_TABLE_RELATION
BEFORE INSERT OR UPDATE OF is_primary_relation
ON md_table_relation compound
v_is_primary_relation md_table_relation.is_primary_relation%TYPE;
v_from_table_name md_table_relation.from_table_name%TYPE;
v_to_table_name md_table_relation.to_table_name%TYPE;
v_from_schema_name md_table_relation.from_schema_name%TYPE;
v_to_schema_name md_table_relation.to_schema_name%TYPE;
v_schema_name MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_SCHEMA%TYPE;
v_table_name MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_TABLE%TYPE;
v_column_name MD_COLUMN_INFO.ALT_DISPLAY_COLUMN_COLUMN%TYPE;
v_dimension_name MD_HIERARCHY.DIMENSION_NAME%type;
v_hierarchy_name MD_HIERARCHY.HIERARCHY_NAME%type;
v_oldispwbenabled MD_HIERARCHY.IS_PWB_ENABLED%type;
v_newispwbenabled MD_HIERARCHY.IS_PWB_ENABLED%type;
ncount NUMBER;
BEFORE EACH ROW
BEGIN
IF :NEW.is_primary_relation = 1
THEN
BEGIN
state_pkg.v_from_table_name := :NEW.from_table_name;
state_pkg.v_to_table_name := :NEW.to_table_name;
state_pkg.v_from_schema_name := :NEW.from_schema_name;
state_pkg.v_to_schema_name := :NEW.to_schema_name;
END;
END IF;
END BEFORE EACH ROW;
AFTER EACH STATEMENT IS
BEGIN
SELECT COUNT (*)
INTO ncount
FROM md_table_relation
WHERE from_table_name = state_pkg.v_from_table_name
AND to_table_name = state_pkg.v_to_table_name
AND from_schema_name = state_pkg.v_from_schema_name
AND to_schema_name = state_pkg.v_to_schema_name
AND is_primary_relation = 1;
IF ncount > 1
THEN
raise_application_error
(-20000,
'Primary relation already exists on the table set'
);
END IF;
END AFTER EACH STATEMENT;
END;
/
select * from emp
select * from dept
select b.deptno,max(sal) from emp a , dept b where a.deptno=b.deptno group by b.deptno
select b.deptno,max(sal) from emp b group by deptno;
select * from (
select b.ENAME,job,max(sal) over(partition by deptno) dept_max,max(sal) over(partition by job) job_max, sal from emp b
)
where sal=job_max;
select * from (
select b.ENAME,deptno,job,dense_rank() over (partition by deptno order by sal desc) dept_rank,sal from emp b
)
where dept_rank<6;
SELECT ENAME, deptno, job, dept_rank, sal, hiredate, CASE WHEN sal < LEAD ( sal) OVER ( PARTITION BY deptno ORDER BY sal DESC) THEN 'No' ELSE 'Yes' END seniority_salARY
FROM (SELECT *
FROM (SELECT b.ENAME, deptno, hiredate, job, DENSE_RANK () OVER ( PARTITION BY deptno ORDER BY sal DESC) dept_rank, sal
FROM emp b))
WHERE dept_rank < 6 order by deptno, dept_rank ;
set serveroutput on;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
-- dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2) ||
' Seconds...' );
end;
/
drop table id12;
create table id12 (id number);
insert into id12 values(1);
insert into id12 values(2);
insert into id12 values(3);
select index_name from user_indexes where table_name='ID12';
CREATE OR REPLACE FUNCTION funct
RETURN number
IS
BEGIN
DBMS_OUTPUT.put_line ( 'Executed');
RETURN 10;
END;
/
set serveroutput on ;
select nvl(id,funct) from id12;
select coalesce(id,funct, 1,funct) from id12;
select coalesce('2',1) from dual
--
select nullif(1,1) from dual;
select * from id12
CREATE TABLE t1
AS
SELECT LEVEL AS c1, LEVEL AS c2, RPAD ( 'x', 100) AS c3
FROM DUAL
CONNECT BY LEVEL <= 10000;
CREATE TABLE t2
AS
SELECT LEVEL AS c1, LEVEL AS c2, RPAD ( 'x', 100) AS c3
FROM DUAL
CONNECT BY LEVEL <= 10000;
ALTER SESSION ENABLE PARALLEL DML;
EXPLAIN PLAN
FOR merge /*+ parallel */ into t1
using (select c1, c2 from t2) t2
on (t1.c1 = t2.c1)
when matched then
update set t1.c2 = t1.c2
when not matched then
insert(c1, c2) values(t2.c1, t2.c2);
create table my_objects as
insert into my_objects_temp
select /*+ APPEND */ * from my_objects
create table my_objects_temp as
select /*+ APPEND */ * from my_objects
union all
select /*+ APPEND */ * from my_objects
union all
select /*+ APPEND */ * from my_objects
insert into my_objects
select /*+ APPEND */ * from MY_OBJECTS_GTT where rownum<100
INSERT INTO MY_OBJECTS_GTT
select * from my_objects
SELECT object_name, DBMS_ROWID.rowid_relative_fno ( ROWID) rfile#, DBMS_ROWID.rowid_block_number ( ROWID) block#,
DBMS_ROWID.rowid_row_number(ROWID) row#, rowid rd FROM my_objects;
select dbms_utility.make_data_block_address(1,48409) from dual;
select * from user_objects where object_name='MY_OBJECTS'
SELECT objd,file#, block#, DECODE (class#, 1, 'data block', 2, 'sort block', 3, 'save undo block', 4, 'segment header', 5, 'save undo header', 6, 'free list', 7, 'extent map', 8, '1st level bmb', 9, '2nd level bmb', 10, '3rd level bmb', 11, 'bitmap block', 12, 'bitmap index block', 13, 'file header block', 14, 'unused', 15, 'system undo header', 16, 'system undo block', 17, 'undo header', 18, 'undo block') class_type, status
FROM v$bh
WHERE objd = 85568
ORDER BY 1, 2, 3;
select hladdr
from x$bh
where dbarfil = 1
and dbablk = 48409;
select * from MY_OBJECTS_GTT
truncate table MY_OBJECTS_GTT
drop table my_objects_temp
truncate table my_objects_temp
select owner,object_name from MY_OBJECTS order by 2 desc
create table id20 (id number unique)
create table id21 (id number, constraint id21_id20_FK foreign key(id) references id20(id))
insert into id20 values(1)
create table big as select * from all_objects
drop table small
create table small as select * from all_objects where rownum<10
create index big_idx on big(object_name)
create index small_idx on small(object_name)
alter table big modify(object_name null)
alter table small modify(object_name null)
update small set object_name=null where rownum<2
DECLARE
CURSOR c1
IS
SELECT *
FROM emp FOR UPDATE NOWAIT;
Z c1%ROWTYPE;
BEGIN
OPEN C1;
FETCH c1 INTO Z;
COMMIT;
FETCH c1 INTO Z;
END;
/
create table bitmap_users as select * from all_objects;
select * from bitmap_users
create index bitmap_users_bitidx on bitmap_users(object_id);
create bitmap index bitmap_users_bitidx on bitmap_users(object_id);
alter table bitmap_users add constraint bitmap_users_pk primary key(object_id);
SET TIMING ON;
SELECT COUNT ( *)
FROM big
WHERE object_name IN (SELECT object_name FROM small);
SELECT COUNT ( *)
FROM big s
WHERE NOT EXISTS
(SELECT 1
FROM small b
WHERE b.object_name = s.object_name)
select * from emp order by sal desc
select * from (select a.*, rownum rn from emp a order by sal desc) b where b.rn=1
select a.*, rownum rn from emp a order by sal desc
select sal from (
select rownum num, c.* from (
select sal from emp order by 1 desc) c)d
where num = 1
drop table id22
create table id22(id number primary key)
create table id23(id number, constraint id23 foreign key(id) references id22(id));
alter table id22 drop primary key
alter table id23 disable constraint ID23
select * from user_constraints where table_name='ID23'
select * from (select ename,deptno,sal , rank() over (partition by deptno order by sal DESC) rnk from emp) a
where a.rnk=2
select e.deptno ,e.ename, max_sal from emp e,(select deptno,max(sal) max_sal from emp group by deptno) b
where e.deptno=b.deptno and e.sal=b.max_sal
select 'a' from dual
minus
select 1 from dual;
alter table id22 drop primary key cascade
create table teams(name varchar2(30));
insert into teams values('A');
insert into teams values('B');
insert into teams values('C');
begin
for i in (select rownum rn, name from teams order by rn desc)
loop
dbms_output.put_line(i.name);
for r in (select name from(select rownum rn, name from teams order by rn desc) where rn<3 )
loop
end loop;
end loop;
end;
/
select name from(select rownum rn, name from teams order by rn desc) where rn<3
select TO_CHAR(sysdATE+2,'DAY') FROM
select * from emp a, (select deptno,avg(sal) avg_sal from emp group by deptno) b
where a.deptno=b.deptno and a.sal>b.avg_sal;
Mutating trigger error example: Note that mutating trigger error comes only with after DML triggers. Mutating error occurs when we are performing some DML(insert,delete,update) operations and we are trying to select (or any DML operation) the affected records from the same trigger. So basically we are trying to select the records in the trigger from the table that owns the trigger. This creates inconsistency issue and Oracle throws a mutating error
create table mutat_trigger(id number)
insert into mutat_trigger values (&id);
create or replace trigger mut_trigger
after insert on mutat_trigger for each row
declare
cnt number;
begin
select count(*)into cnt from mutat_trigger ;
dbms_output.put_line(cnt);
end;
/
