Oracle SQL Using Variables Not Running

Question

This is probably an elementary question, but I'm new to Oracle SQL. I'm trying to get the SQL below to execute in Oracle SQL Developer. The error information is below the code. I've modified the code since I pasted the error message. Line 28 is the last line in the code, "END;" Line 14 column 1 refers to the word "Select".

DECLARE 
    v_StartDate Date := &StartDate;
    v_EndDate Date := &EndDate;

SET v_StartDate := CASE &StartDate
WHEN TO_DATE('01/01/1900','MM/DD/YYYY')
THEN LAST_DAY(ADD_MONTHS(trunc(current_date),-2))
ELSE TO_DATE(&StartDate - 1) END;
SET v_EndDate := CASE &StartDate
WHEN TO_DATE('01/01/1900','MM/DD/YYYY')
THEN LAST_DAY(ADD_MONTHS(&EndDate,-1))
ELSE TO_DATE(&EndDate + 1) END;

BEGIN
Select *
From
Table
Where date_value > v_StartDate
and date_value < v_EndDate
END;

Error report - ORA-06550: line 28, column 24: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 14, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 28, column 27: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Apologies for that, I simplified the code after I pasted the error message. Line 28 is the last line in the code "END;" Line 14 column 1 refers to the word "Select". — AmishRobot
– AmishRobot, Commented Jun 14, 2021 at 15:09
. . You should edit the question. That information belongs in the question not a comment. — Gordon Linoff
– Gordon Linoff, Commented Jun 14, 2021 at 15:22

Koen Lostrie · Accepted Answer · 2021-06-14 15:23:55Z

1

There are several things wrong with your code. I suggest you start with a small piece of code that works and then add statement by statement. That will allow you to see what is wrong and fix it. Once you have an accumulated set of syntax errors it becomes difficult to debug.

There are comments where the code has errors:

DECLARE 
  v_StartDate Date := &StartDate;
  v_EndDate Date := &EndDate;
-- issues below:
-- 1. SET is not oracle syntax. To assign a variable, use the := operator 
--    without the SET word. 
-- 2. you cannot assign variables in the declaration section, unless you
--    declare and assign in the same statement. This part should go after
--    the BEGIN keyword 
-- 3. Why would you use the bind variable if you already assigned it  
--    above ??
SET v_StartDate := CASE &StartDate
WHEN TO_DATE('01/01/1900','MM/DD/YYYY')
THEN LAST_DAY(ADD_MONTHS(trunc(current_date),-2))
ELSE TO_DATE(&StartDate - 1) END;
SET v_EndDate := CASE &StartDate
WHEN TO_DATE('01/01/1900','MM/DD/YYYY')
THEN LAST_DAY(ADD_MONTHS(&EndDate,-1))
ELSE TO_DATE(&EndDate + 1) END;

BEGIN
-- issue below
-- you cannot just select in pl/sql, you need to SELECT INTO (if there is
-- a single row) or BULK COLLECT INTO (if you have multiple rows)
Select *
From
Table
Where date_value > v_StartDate
and date_value < v_EndDate
END;

answered Jun 14, 2021 at 15:23

Koen Lostrie

19.4k2 gold badges16 silver badges25 bronze badges

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

1 Comment

AmishRobot Over a year ago

Thanks for the help. I'm trying to translate this from SQL Server. Is there a way to do this without using PL/SQL with begin and end lines? I couldn't figure out the variables without it.

EdStevens · Accepted Answer · 2021-06-14 15:31:00Z

1

I'm sorry to have to say it, but your code "has more errors than an early Met's game".

The key word SET belongs as a clause of the UPDATE statement. You have to UPDATE statements. If you just want to set the value of a variable, the syntax is simply

v_StartDate := some_value

And you have already set the value of v_StartDate with a parameter in you DECLARE section, so what are you trying to do with it now?

The keyword WHEN requires a comparison, but your usage

WHEN TO_DATE('01/01/1900','MM/DD/YYYY')
THEN LAST_DAY(ADD_MONTHS(trunc(current_date),-2))

Is not comparing the first arugment (TO_DATE ....) with anything.

You are trying to trunc(current_date) but 'current_date' is not defined -- and it's not a key word or reserved word. Perhaps you meant 'sysdate'.

answered Jun 14, 2021 at 15:31

EdStevens

3,9203 gold badges12 silver badges19 bronze badges

3 Comments

AmishRobot Over a year ago

Thanks for the help. I'm trying to translate this from SQL Server. Is there a way to do this without using PL/SQL with begin and end lines? I couldn't figure out the variables without it. The subject of the case statement is what the when to_date is comparing to. Yes, I meant sysdate, The gist of it is, the user is presented with 2 parameter fields, start date and end date. They are already default populated with 01/01/1900. If they leave that alone and run the code, it will run for the previous month. If they change them, the code will run for the period they set.

Koen Lostrie Over a year ago

Please be more clear. Do you want to just run a query and see the results in sqldeveloper (in that case you don't need pl/sql) or do you want to do something with the data (in which case you would need pl/sql). If it is the first then I suggest logging a new question because it is entirely different from this one. Please be as clear as possible - you did not say in your question what you wanted to achieve, only what you were doing. So you got an answer to that question...

AmishRobot Over a year ago

You are correct. It's the former. I don't need PL/SQL at all. Thank you!

Collectives™ on Stack Overflow

Oracle SQL Using Variables Not Running

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related