PL/SQL script giving errors

Question

I've to work on some older EDW scripts, which I think are in PL/SQL and queries fech data from Oracle table as well. But there is some problem with them, the part which declares variables, as shown in the image gives error. I'm unable to understand why?

Below is some part of script,

VARIABLE begin_exp_date varchar2(8)
VARIABLE end_exp_date varchar2(8)
VARIABLE begin_cal_key number

Declare
begin
    :begin_exp_date := 'begin_exp_date';
    :end_exp_date := 'end_exp_date';
    :begin_cal_key := 'begin_cal_key';
end;

These lines produce error ORA-00900: Invalid SQL statement.

Any help?

Glyphfever · Accepted Answer · 2012-02-23 22:20:52Z

1

If you plug the script in SQL*Plus, it will be executed without the ORA-00900 error. I guess you received the error when it was run in Toad.

answered Feb 23, 2012 at 22:20

Glyphfever

1459 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Phil · Accepted Answer · 2011-12-06 06:56:39Z

0

If it is indeed PL/SQL, it should be more like this

DECLARE
    begin_exp_date varchar2(8);
    end_exp_date varchar2(8);
    begin_cal_key number;
BEGIN
    begin_exp_date := 'begin_exp_date';
    -- and so on
END;

You can set constant values to the variables in the DECLARE section if you want. Note that you've defined begin_cal_key as a NUMBER so cannot assign the string 'begin_cal_key'

answered Dec 6, 2011 at 6:56

Phil

166k25 gold badges265 silver badges269 bronze badges

1 Comment

Manish Over a year ago

Yes, I'm aware of this syntax, but the older scripts which I have are also working (no idea which enviornment) with the things mentioned in question. So thought are they ment for any other platform like DB2 or like that?

Collectives™ on Stack Overflow

PL/SQL script giving errors

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related