Concatenate 2 CLOB in oracle

SQL
====
Select  CONCAT(clob1, clob2) from table

PL/SQL
====
declare
  clob1 clob;
  clob2 clob;
begin
  clob1 := 'Clob1 ';
  clob2 := '+ Clob2';
  dbms_output.put_line(CONCAT(clob1, clob2));
end;

How to split a phone no to country code,std code,Land Line

with ph as
(select '091-044-234678' phone from dual),
 ph1 as
(select phone,substr(phone,instr(phone,'-',1)+1) as phone2 from ph),
 ph2 as
(select phone,phone2,substr(phone2,instr(phone2,'-',1)+1) as phone3 from ph1)
select * from ph2
select substr(phone,0,instr(phone,'-',1)-1) phone,
substr(phone2,0,instr(phone2,'-',1)-1)phone2,phone3
 from ph2

Find List of Referenced dependencies for a package or procedure or view

select referenced_owner,referenced_name from all_dependencies where  lower(name)='package Name'

Get Financial Year Week no for given date

Financial Week start from 01-Apr each year. So for 01-Apr of that year i should get week number as 1.

select
to_char(SYSDATE) the_date,
to_char(add_months(SYSDATE,-3),'WW') fiscal_week
from dual

Connect By Root Example

I have a Table person_map which will have 2 columns,
Person_id,Mapped_person_id

For Example
Person_ID Mapped_Person_id
1 2
2 3
3 4
5 6
6 7

I require a query which will give output like below
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

Scripts
Create table Person (person_id Number, Mapped_person_id Number);

INSERT INTO PERSON VALUES ( 1,2);
INSERT INTO PERSON VALUES ( 2,3);
INSERT INTO PERSON VALUES ( 3,4);
INSERT INTO PERSON VALUES ( 5,6);
INSERT INTO PERSON VALUES ( 6,7);

Solution:

SELECT level, person_id AS original_person_id, 

CONNECT_BY_ROOT person_id AS root_person_id, mapped_person_id
      FROM person
 CONNECT BY person_id = prior mapped_person_id;

How to find Table Last Altered Date/Time in Oracle

select * from dba_objects
where owner='<Table Schema Owner>'
and object_name ='<Table Name>'

How to get first day and last date of week, month, quarter, year in Oracle

--First day of current week(sunday)
select TRUNC(SYSDATE, 'Day') from dual;
--First day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day') from dual;
--First day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day') from dual;
--First day of current month
select TRUNC(SYSDATE , 'Month') from dual;
--First day of previous month
select TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month') from dual;
--First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , 'Month') from dual;
--First day of current year
select TRUNC(SYSDATE , 'Year') from dual;
--First day of previous year
select TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year') from dual;
--First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , 'Year'),12) from dual;
-- First Day of Current quater
select TRUNC(SYSDATE , 'Q') from dual;
--  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),-3) from dual;
--  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3) from dual;

--Last day of current week(sunday)
select TRUNC(SYSDATE, 'Day')+6 from dual;
--Last day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day')+6 from dual;
--Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day')+6 from dual;
--Last day of current month
select LAST_DAY(TRUNC(SYSDATE , 'Month')) from dual;
--Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month')) from dual;
--Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , 'Month')) from dual;
--Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)) from dual;
--Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) from dual;
--Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),-13)) from dual;
-- Last Day of Current quater
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),2)) from dual;
--  Last Day of Previous Quarter
select TRUNC(SYSDATE , 'Q')-1 from dual;
--  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),5)) from dual;

Query to calculate week number from a given date

SELECT CEIL ( ( 7
+ ( TRUNC (TO_DATE (p_booking_date), 'd')
- TRUNC (TO_DATE (p_booking_date), 'Y')
)
)
/ 7
)
INTO l_qtr
FROM DUAL;