CODEWITHSUNDEEP
sqloracle-databaseplsqlsqlplus
AmishRobot
AmishRobot

Reputation: 1

Oracle SQL Using Variables Not Running

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:

Upvotes: 0

Views: 634

Answers (2)

EdStevens
EdStevens

Reputation: 3872

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'.

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18565

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;

Upvotes: 1

Related Questions