Answer Posted / lokesh

-- Table which maintains the Holidays list.
CREATE TABLE HOLIDAYS_TABLE
(
HOLIDAY_NAME VARCHAR2(20),
HOLIDAY_DATE DATE
);


-- procedure to Calculate the Number of Working days in a
period
CREATE OR REPLACE FUNCTION CALC_WORKING_DAYS(START_DATE IN
DATE, END_DATE IN DATE)

RETURN NUMBER

IS

v_stdate DATE := TO_DATE(START_DATE);
v_eddate DATE := TO_DATE(END_DATE);

v_k NUMBER := 0;
v_cnt NUMBER := 0;
v_bk_hldys NUMBER := 0;


BEGIN


-- To Find the total number of days in the period
v_k := v_eddate - v_stdate ;

-- To Exclude Weekends from the total number of days in
the period
FOR i IN 1..v_k LOOP

IF to_char(v_stdate,'Dy') NOT IN ('Sat','Sun') THEN
v_cnt := v_cnt +1;
END IF;
v_stdate := v_stdate + 1;

END LOOP;

-- To find the Public Holidays which are not on Weekends in
the given Period
SELECT COUNT(DISTINCT(HOLIDAY_DATE)) INTO v_bk_hldys FROM
HOLIDAYS_TABLE where HOLIDAY_DATE BETWEEN TO_DATE
(START_DATE) AND TO_DATE(END_DATE) AND to_char
(HOLIDAY_DATE,'Dy') NOT IN ('Sat','Sun');

-- To Subtract the public holidays which are not on
Weekends
v_cnt := v_cnt - v_bk_hldys;

RETURN v_cnt;
END;
/

What are sql functions? Describe in brief different types of sql functions?

1074

---

How are functions and procedures called in PL/SQL?

1194

---

What is a sql*loader control file?

1227

---

Can we use views in stored procedure?

1014

---

What is synonyms?

1035

---

What are different clauses used in sql?

1103

---

What is the difference between left and left outer join?

937

---

Is it possible to remove child records without removing master table records...the two having pk,fk relationship?

1245

---

- Types of triggers - View - Dcl - Procedures, packages, functions - Metasolve - Can use Dcl in triggers - package case study - Cursor and its types - triggers schedule - Wrap - Why we are using fetch and for in cursor. difference?

1817

---

Can we use ddl commands in pl sql?

1135

---

Explain dml and ddl?

960

---

List the different type of joins?

1054

---

what is user defined functions? : Sql dba

1017

---

Why do we need cursors in pl sql?

1057

---

What is count * in sql?

978

---