My tech blog

Thursday, 20 December 2018

Compute Group Salary

-- DATA SETUP --

create table emp_t(empno number,
ename varchar2(100),
mgr number,
sal number);

Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7839,'KING',null,1000);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7698,'BLAKE',7839,700);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7782,'CLARK',7839,500);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7566,'JONES',7839,800);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7788,'SCOTT',7566,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7902,'FORD',7566,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7369,'SMITH',7902,75);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7499,'ALLEN',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7521,'WARD',7698,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7654,'MARTIN',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7844,'TURNER',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7876,'ADAMS',7788,50);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7900,'JAMES',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7934,'MILLER',7782,300);

commit;

-- Query 1 --

select sum(sal)
from EMP_T
start with ENAME = 'FORD'
connect by PRIOR empno=mgr;

select EMPNO, ENAME, MGR, SAL,(select sum(sal)
from EMP_T
start with ENAME = a.ENAME
connect by PRIOR empno=mgr) group_sal
from emp_t a;

-- Query 2 --

CREATE OR REPLACE FUNCTION get_group_sal (
pin_ename VARCHAR
) RETURN NUMBER AS
lv_grp_sal NUMBER;
BEGIN
SELECT SUM(sal)
INTO lv_grp_sal
FROM emp_t
START WITH ename = pin_ename
CONNECT BY PRIOR empno = mgr;

RETURN lv_grp_sal;
END;
/
select EMPNO, ENAME, MGR, SAL,get_group_sal(a.ename) group_sal
from emp_t a;

-- Query 3 --

with FUNCTION fn_group_sal (
pin_ename VARCHAR
) RETURN NUMBER AS
lv_grp_sal NUMBER;
BEGIN
SELECT SUM(sal)
INTO lv_grp_sal
FROM emp_t
START WITH ename = pin_ename
CONNECT BY PRIOR empno = mgr;

RETURN lv_grp_sal;
END;
select EMPNO, ENAME, MGR, SAL,fn_group_sal(ename) group_sal
from emp_t a;

Friday, 14 December 2018

Query to Extract Names from mail id

create table tab1(mail_id varchar2(50));

insert into tab1 values('siva.k.academy@gmail.com');
insert into tab1 values('jeevan.anand@yahoo.com');
insert into tab1 values('ravi@outlook.com');

commit;

select * from tab1;

with d1 as (select mail_id m,
substr(mail_id,1, instr(mail_id,'@')-1) n,
substr(mail_id, instr(mail_id,'@')+1) d
from tab1),
d2 as (select m,d,n,
instr(n,'.',1,1) f_dot ,
instr(n,'.',1,2) s_dot
from d1),
d3 as (select m,d,n,f_dot,s_dot,
substr(n,1,decode(f_dot,0,length(n),f_dot-1)) f_name,
decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1)) m_name,
decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)+1)) l_name
from d2)
select m,f_name,m_name,l_name
from d3;

Saturday, 8 December 2018

Two dimensional array in PLSQL using VARRAY

Example to understand "Two dimensional array in PLSQL using VARRAY"

SET SERVEROUTPUT ON
DECLARE
TYPE matrix_elem_type IS VARRAY ( 3 ) OF NUMBER;
TYPE matrix_type IS VARRAY ( 3 ) OF matrix_elem_type;
lv_matrix1 matrix_type := matrix_type(NULL,NULL,NULL);
lv_matrix2 matrix_type := matrix_type(NULL,NULL,NULL);
lv_matrix3_total matrix_type := matrix_type(NULL,NULL,NULL);
lv_matrix_elem matrix_elem_type := matrix_elem_type(NULL,NULL,NULL);
-- Local Procedure to print the array
procedure print_array(pin_array matrix_type,pin_desc varchar2) as
begin
dbms_output.put_line('Printing the '||pin_desc||' ....');
for i in pin_array.first..pin_array.last loop
for j in pin_array(i).first..pin_array(i).last loop
dbms_output.put(pin_array(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
BEGIN
-- Assing First Array
lv_matrix_elem := matrix_elem_type(1,2,3);
lv_matrix1(1) := lv_matrix_elem;
lv_matrix_elem := matrix_elem_type(4,5,6);
lv_matrix1(2) := lv_matrix_elem;
lv_matrix_elem := matrix_elem_type(7,8,9);
lv_matrix1(3) := lv_matrix_elem;

-- Printing the first array
print_array(lv_matrix1,'First Array');

-- Assing Second Array
lv_matrix_elem := matrix_elem_type(11,12,13);
lv_matrix2(1) := lv_matrix_elem;
lv_matrix_elem := matrix_elem_type(14,15,16);
lv_matrix2(2) := lv_matrix_elem;
lv_matrix_elem := matrix_elem_type(17,18,19);
lv_matrix2(3) := lv_matrix_elem;

-- Printing the Second array
print_array(lv_matrix2,'Second Array');

-- Add Array1+Array2

for i in lv_matrix1.first..lv_matrix1.last loop
for j in lv_matrix1(i).first..lv_matrix1(i).last loop
lv_matrix_elem(j) := lv_matrix1(i)(j) + lv_matrix2(i)(j);
end loop;
lv_matrix3_total(i) := lv_matrix_elem;
end loop;

--Print Total Array
print_array(lv_matrix3_total,'Total Array');
END;

Saturday, 7 July 2018

How to get unique records without using “DISTINCT” in oracle

drop table Student_list;
create table Student_list(roll_no number, stu_name varchar2(30));

Insert into Student_list (ROLL_NO,stu_name) values (1,'KING');
Insert into Student_list (ROLL_NO,stu_name) values (2,'BLAKE');
Insert into Student_list (ROLL_NO,stu_name) values (3,'CLARK');
Insert into Student_list (ROLL_NO,stu_name) values (4,'JONES');
Insert into Student_list (ROLL_NO,stu_name) values (5,'SCOTT');
Insert into Student_list (ROLL_NO,stu_name) values (6,'RAGHU');
Insert into Student_list (ROLL_NO,stu_name) values (7,'FORD');
Insert into Student_list (ROLL_NO,stu_name) values (8,'SMITH');
Insert into Student_list (ROLL_NO,stu_name) values (9,'ALLEN');
Insert into Student_list (ROLL_NO,stu_name) values (10,'WARD');
Insert into Student_list (ROLL_NO,stu_name) values (1,'KING');
Insert into Student_list (ROLL_NO,stu_name) values (5,'SCOTT');
Insert into Student_list (ROLL_NO,stu_name) values (7,'FORD');

COMMIT;

select * from Student_list
order by roll_no,stu_name;

SELECT DISTINCT ROLL_NO,STU_NAME
FROM STUDENT_LIST
ORDER BY ROLL_NO,STU_NAME;

SELECT UNIQUE ROLL_NO,STU_NAME
FROM STUDENT_LIST
ORDER BY ROLL_NO,STU_NAME;

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
GROUP BY ROLL_NO,STU_NAME
ORDER BY ROLL_NO,STU_NAME;

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
UNION
SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST;

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
UNION
SELECT NULL,NULL
FROM DUAL WHERE 1=2;

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
INTERSECT
SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST;

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
MINUS
SELECT NULL,NULL
FROM DUAL;

SELECT ROLL_NO,STU_NAME FROM (
SELECT ROLL_NO,STU_NAME,
ROW_NUMBER() over(partition by ROLL_NO,STU_NAME
ORDER BY ROLL_NO,STU_NAME) R
FROM STUDENT_LIST)
where R=1;

SELECT ROLL_NO,STU_NAME FROM (
SELECT ROLL_NO,STU_NAME,
RANK() over(partition by ROLL_NO,STU_NAME
ORDER BY ROWNUM) R
FROM STUDENT_LIST)
where R=1;

SELECT * FROM STUDENT_LIST
WHERE ROWID IN (
SELECT MIN(ROWID)
FROM STUDENT_LIST
GROUP BY ROLL_NO,STU_NAME)
ORDER BY ROLL_NO,STU_NAME;

SELECt * FROM STUDENT_LIST A
WHERE 1 = ( SELECT COUNT(1)
FROM STUDENT_LIST B
WHERE A.ROLL_NO = B.ROLL_NO
AND A.STU_NAME = B.STU_NAME
AND A.ROWID >= B.ROWID);

Wednesday, 6 June 2018

"Accessible by" clause

"Accessible by" clause is used to declare a particular unit may be referenced only by given list of units.
Anonymous block cannot be listed under "Accessible by" Clause, hence the units declared as "Accessible by" cannot be called dynamically.
"Accessible by" declared units cannot be invoked from outside of database

CREATE OR replace PROCEDURE proc_for_sal_increment ( pin_empno NUMBER,
                                                    pin_pct    NUMBER )
accessible BY (update_sal_wrapper)
AS
BEGIN
  UPDATE emp
  SET    sal = ( sal ) + ( sal * pin_pct / 100 )
  WHERE  empno = pin_empno;

  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Unable to Update sal : '
  || SQLCODE);
END;

BEGIN
Proc_for_sal_increment(7698, 10);
END;
/

exec proc_for_sal_increment(7698, 10);

CREATE OR replace PROCEDURE Update_sal_wrapper
AS
BEGIN
Proc_for_sal_increment(7698, 10);
END;
/

exec update_sal_wrapper;

Tuesday, 5 June 2018

Pragma UDF (User Defined Function)

CREATE OR REPLACE FUNCTION fn1 (
p_input1 NUMBER
) RETURN NUMBER
AS
BEGIN
RETURN p_input1 * 2;
END;
/

SELECT
SUM(fn1(ROWNUM) )
FROM
dual
CONNECT BY
level <= 2000000;

-----------------------------------------------------

WITH
FUNCTION fn1 (
p_input1 NUMBER
) RETURN NUMBER
AS
BEGIN
RETURN p_input1 * 2;
END;

SELECT
SUM(fn1(ROWNUM) )
FROM
dual
CONNECT BY
level <= 2000000;

-----------------------------------------------------

CREATE OR REPLACE FUNCTION fn1 (
p_input1 NUMBER
) RETURN NUMBER
AS
PRAGMA UDF;
BEGIN
RETURN p_input1 * 2;
END;
/

SELECT
SUM(fn1(ROWNUM) )
FROM
dual
CONNECT BY
level <= 2000000;

-----------------------------------------------------

Wednesday, 30 May 2018

Sequence as default value for table columns

Oracle 12c allows to use sequence as default value for column value. This video shows the behavior in pre-12C & 12C version onwards.

-- Behaviour from 12c onwards ( USAGE OF SEQUENCE FOR DEFAULT VALUE )

drop table stu_details;
create sequence stu_id_seq;

CREATE TABLE stu_details (
stu_id NUMBER DEFAULT stu_id_seq.nextval,
stu_name VARCHAR2(30),
date_of_join DATE DEFAULT sysdate
);

insert into stu_details(stu_name,date_of_join) values('RAGU',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(201,'SCOTT',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(NULL,'SMITH',sysdate);

select *
from stu_details;

-- Behaviour from 12c onwards ( USAGE OF "DEFAULT ON NULL" )
drop table stu_details;
drop sequence stu_id_seq;
create sequence stu_id_seq;

CREATE TABLE stu_details (
stu_id NUMBER DEFAULT ON NULL stu_id_seq.nextval,
stu_name VARCHAR2(30),
date_of_join DATE DEFAULT sysdate
);

insert into stu_details(stu_name,date_of_join) values('RAGU',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(100,'SCOTT',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(NULL,'SMITH',sysdate);

select *
from stu_details;