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 Go to next message
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 #283585 is a reply to message #283583] Tue, 27 November 2007 09:43 Go to previous messageGo to next message
divacam99
Messages: 5
Registered: June 2007
Junior Member
You have to put an ; after the declaration of the startdate and the to_char function must be replaced by to_date

Grtz,

Dirk
Re: Declare date and BEGIN problem [message #283586 is a reply to message #283583] Tue, 27 November 2007 09:44 Go to previous messageGo to next message
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
Re: Declare date and BEGIN problem [message #283588 is a reply to message #283583] Tue, 27 November 2007 10:02 Go to previous message
Chibi
Messages: 2
Registered: November 2007
Junior Member
Thanks for your very swift responses Cool

It looks like the script now works, and all because of a missing ; - I really am a newbie Laughing
Previous Topic: Period to Period Comparison
Next Topic: UTL_MAIL Attachments
Goto Forum:
  


Current Time: Tue Dec 06 15:53:06 CST 2016

Total time taken to generate the page: 0.11799 seconds