Home » SQL & PL/SQL » SQL & PL/SQL » getting error while executing SQL script
getting error while executing SQL script [message #246468] Thu, 21 June 2007 01:13 Go to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
--the following table is created with list of Singapore Holidays
set define off
drop table exclude_date;

create table exclude_date as
select 2007 as year#, 1 month#, 1 day#, 'New Year Day' as name# from dual union all
select 2007 as year#, 1 month#, 2 day#, 'New Year Day' as name# from dual union all
select 2007 as year#, 2 month#, 18 day#, 'Chinese New Year' as name# from dual union all
select 2007 as year#, 2 month#, 19 day#, 'Chinese New Year' as name# from dual union all
select 2007 as year#, 2 month#, 20 day#, 'Chinese New Year' as name# from dual union all
select 2007 as year#, 4 month#, 6 day#, 'Good Friday' as name# from dual union all
select 2007 as year#, 5 month#, 1 day#, 'Labour Day' as name# from dual union all
select 2007 as year#, 5 month#, 31 day#, 'Vesak Day' as name# from dual union all
select 2007 as year#, 8 month#, 9 day#, 'National Day' as name# from dual union all
select 2007 as year#, 10 month#, 13 day#, 'Hari Raya Puasa' as name# from dual union all
select 2007 as year#, 11 month#, 8 day#, 'Deepavali' as name# from dual union all
select 2007 as year#, 12 month#, 20 day#, 'Hari Raya Hji' as name# from dual union all
select 2007 as year#, 12 month#, 25 day#, 'Christmas Day' as name# from dual ;
/

drop type DateList;

create or replace type DateList as table of date;
/

--the following function returns list of working dates between two dates excluding SAT , SUN and Singapore Holidays
drop function all_date_list;

create or replace function all_date_list(p_start date, p_end date)
RETURN DateList
PIPELINED
IS
curdate date:=p_start;
exclude number;
BEGIN
WHILE curdate <= p_end
LOOP
BEGIN
select 1
into exclude
from exclude_date
where to_date(year#||lpad(month#,2,0)||lpad(day#,2,0),'YYYYMMDD')=trunc(curdate) --excluding unworked days
or mod(to_char(curdate,'j'),7) in (5,6); --excluding week-end
EXCEPTION WHEN NO_DATA_FOUND THEN PIPE ROW( curdate );
WHEN OTHERS THEN NULL;
END;
curdate:=curdate+1;
END LOOP;
RETURN;
END;
/
show errors;


--the following function returns number of working days between two dates excluding SAT , SUN and Singapore Holidays

drop function WorkingDays;

CREATE OR REPLACE function WorkingDays(msgSentDate Date) return NUMBER is
noOfWokingDays number;
begin
select count(*) into noOfWokingDays from table(all_date_list(msgSentDate,sysdate));
return noOfWokingDays;
end;
/
show errors;
commit;


The above script is not working properly.Last two fuctions are not executing.
What might be the error?
Re: getting error while executing SQL script [message #246471 is a reply to message #246468] Thu, 21 June 2007 01:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What do you mean, not executing? You've just created the functions. Do they compile with errors?

MHE
Re: getting error while executing SQL script [message #246482 is a reply to message #246468] Thu, 21 June 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The whole thing you posted works for me in 10.2.0.3.

What is your Oracle version?
Copy and paste what is not working.

Regards
Michel
Re: getting error while executing SQL script [message #246484 is a reply to message #246468] Thu, 21 June 2007 01:30 Go to previous messageGo to next message
pritika
Messages: 9
Registered: November 2006
Junior Member
I tried executing this script, this works fine except the forward slash / in the end while creating the exclude_date table.

Just need to remove this /.

also the error will occur while execting the drop statements if and only if the ojects which you are creating donot exist.

Hope this helps.
Re: getting error while executing SQL script [message #246487 is a reply to message #246484] Thu, 21 June 2007 01:41 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pritika wrote on Thu, 21 June 2007 08:30
also the error will occur while execting the drop statements if and only if the ojects which you are creating donot exist.
What error are we talking about?
- ORA-00942: table or view does not exist?

MHE
Previous Topic: some interview questions
Next Topic: Custom field type in Oracle
Goto Forum:
  


Current Time: Tue Dec 06 13:59:43 CST 2016

Total time taken to generate the page: 0.27940 seconds