CODEWITHSUNDEEP
oracle-databasesysdate
Ahmad El Batanouni
Ahmad El Batanouni

Reputation: 35

Oracle db - naming a variable (sysdate)

In PL/SQL... why is it possible to name a variable (sysdate) although it's a reserved word?

for example,

set serveroutput on;    
declare sysdate integer := 23;
begin
dbms_output.put_line(sysdate);
end;

which outputs: 23 and not the date of the current day.

Upvotes: 1

Views: 421

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

Simply because sysdate is a SQL reserved word not PL/SQL. SQL and PL/SQL have different set of reserved words. Not all SQL reserved words are also PL/SQL reserved words and vice versa. So you may, although it's not recommended, use some SQL reserved words as identifiers in PL/SQL without enclosing them in double quotation marks:

sysdate and (say) add are SQL reserved words, so we can use them as variables in PL/SQL without enclosing them in double quotation marks:

SQL> set serveroutput on;
SQL> clear screen;
SQL> set feedback off;

SQL> declare
  2    sysdate varchar2(123);
  3    add     number;
  4  begin
  5    sysdate := 'aaaaa';
  6    add     := 123;
  7    dbms_output.put_line(sysdate || ' - ' ||to_char(add));
  8  end;
  9  /

Result:

aaaaa - 123

But we cannot create a table that has column named sysdate or add:

SQL> create table t1(
  2    sysdate varchar2(123)
  3  );
  sysdate varchar2(123)
  *
ERROR at line 2:
ORA-00904: : invalid identifier

And vice versa. We cannot declare a PL/SQL variable (say) if without it being enclosed in double quotation marks, but we can easily create a table that has a column named if, because if is PL/SQL reserved word not SQL:

SQL> create table t1(
  2   if varchar2(111)
  3  );

table T1 created.

SQL> declare
  2    if number;
  3  begin
  4    if := 123;
  5  end;
  6  /
  if number;
  *
ERROR at line 2:
ORA-06550: line 2, column 3: 
PLS-00103: Encountered the symbol "IF" when expecting one of the following:

But when we put if in double quotation marks(try to avoid it) everything is going to be alright:

SQL> declare
  2    "if" number;
  3  begin
  4    "if" := 123;
  5  end;
  6  /

anonymous block completed

Upvotes: 1

Related Questions