Home » SQL & PL/SQL » SQL & PL/SQL » Declare date and BEGIN problem (Oracle, 9.2.0.8.0, SUN)
Declare date and BEGIN problem [message #283583] |
Tue, 27 November 2007 09:32  |
Chibi
Messages: 2 Registered: November 2007
|
Junior Member |
|
|
Hi
I'm new to Oracle and PL/SQL and am having issues running the following script. I need to update the table with a new set of dates, from a specific date (hence the declaration of startdate). The SQL from the BEGIN statement I've taken from another member of my team (now left), although he was using sysdate, which I've substituted for the startdate variable. We are unable to use sysdate in this instance as there are already entries in the table for future dates.
When I try to run the SQL I get an ORA-06550 (PLS-00103) error against the line with the BEGIN statement. I've no idea why this is as the initial script (from BEGIN) works perfectly fine without the declare statement. Can anyone help?
Thanks
The SQL is as follows:
-- Insert 1 years worth of days
--
-- Declare new start date
--
DECLARE startdate DATE:= to_char('08/08/2008','dd/mm/yyyy')
--
BEGIN
-- Get the universe of exchanges
--
FOR rec IN (SELECT distinct EXC FROM EXCIME)
LOOP
FOR i IN 0..31
LOOP
IF TO_CHAR(startdate + i, 'DY') NOT IN ('SAT', 'SUN')
THEN -- Day of the week - Active
BEGIN
INSERT INTO EXCHGTIMETABLECOPY(exchgid, tradedate, opentime, closetime, buysettledate,sellsettledate,createuser,createtime,updusr,updtime,status)
VALUES (rec.exchgid, trunc(startdate+i),trunc(startdate+i), trunc(startdate+i), trunc(startdate+i+2), trunc(startdate+i+2), 'omsdba', sysdate, 'omsdba', sysdate, 'A');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
ELSE -- Weekend - Suspend
BEGIN
INSERT INTO EXCHGTIMETABLECOPY( exchgid, tradedate, opentime, closetime, buysettledate,sellsettledate,createuser,createtime,updusr,updtime,status)
VALUES (rec.exchgid, trunc(sysdate+i), trunc(sysdate+i), trunc(sysdate+i), trunc(sysdate+i+2), trunc(sysdate+i+2), 'omsdba', sysdate, 'omsdba', sysdate, 'S');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
END LOOP;
END LOOP;
|
|
|
|
Re: Declare date and BEGIN problem [message #283586 is a reply to message #283583] |
Tue, 27 November 2007 09:44   |
Ronald Beck
Messages: 121 Registered: February 2003
|
Senior Member |
|
|
The following statement...
DECLARE startdate DATE:= to_char('08/08/2008','dd/mm/yyyy')
is not correct. The variable "startdate" is of type "DATE", so you need to change "to_char" to "to_date". This will take the string '08/08/2008' and change it to a DATE format, which you can then compare to other DATE type fields.
Others more knowledgable with date commands will have to help with the rest of the statement.
HTH,
Ron
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 20:42:26 CST 2025
|