Can't declare a variable in Oracle SQL(SQL Fiddle)

0
votes

When I run declare query, it always gives me errors.

DECLARE
   the_variable date;
BEGIN
   SELECT MIN("Start Date") INTO the_variable FROM "Employee_Master";
END;

ORA-06550: line 2, column 20: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( @ % ; not null range with default character.

I am not sure where is the problem.

2
oracleplsqlsqlfiddle
looks like SQLFiddle doesn't support PL/SQL. It fails even on simple begin null; end; - Sayan Malakshinov
huh, but it works if I add / in the next line - Sayan Malakshinov
and it works if I put all pl/sql block in one block, ie: declare dt date; begin dt:=date'2020-01-01'; end; with / in next line - Sayan Malakshinov

2 Answers

2
votes

Works OK for me:

SQL> create table "Employee_Master" ("Start Date" date);

Table created.

SQL> insert into "Employee_Master" values (sysdate);

1 row created.

SQL> declare
  2    the_variable date;
  3  begin
  4    select min("Start Date") into the_variable
  5      from "Employee_Master";
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>

Though, why, oh, why are you making your life miserable? Avoid double quotes while working with Oracle. If you do that, you always have to reference tables (and columns) using double quotes and matching letter case exactly as during creation process.

By default, Oracle stores everything as uppercase, but you can reference those objects any way you want (upper, lower, mixed case - doesn't matter). But, with double quotes, as I've said - exact matching is required.

By the way, screenshot you attached shows a lot of nothing and just a little bit of something. Couldn't you have taken a better screenshot?

1
votes

On the SQL Fiddle website, you need to tell it that you are going to be using PL/SQL and a statement will be ended with a / terminator (and not by a ; semi-colon). To do this you need to click on the "Query Terminator" button (right of the "Run SQL" and "Edit Fullscreen" buttons) and change it from [;] to [/].

Then it will work:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Employee_Master ( Start_Date DATE );
    
INSERT INTO Employee_Master ( Start_Date ) VALUES ( SYSDATE );

Query 1:

DECLARE
   the_variable date;
BEGIN
   SELECT MIN(Start_Date) INTO the_variable FROM Employee_Master;
END;
/

Results: