0

What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration? I realize bind and substitution may be acceptable alternatives for an actual query, but what about Oracle PL/SQL? For example I would like to declare a numeric variable as "num", structured below within the Oracle PL query:

SET SERVEROUTPUT ON
declare
  num number;
begin
   num := 10;
   DBMS_OUTPUT.put_line('The value of num ' || num); 
end;

1 Answer 1

5

NUM is not a reserved word.

The best way is not to use reserved words.

If you insist on using them, you can put them between quotation marks. This does not work:

SET SERVEROUTPUT ON
declare
  begin number;
begin
   begin := 10;
   DBMS_OUTPUT.put_line('The value of begin ' || begin); 
end;
/

This does:

SET SERVEROUTPUT ON
declare
  "begin" number;
begin
   "begin" := 10;
   DBMS_OUTPUT.put_line('The value of begin ' || "begin"); 
end;
/

Quotation marks also make the variable name case-sensitive. "begin", "Begin" and "BEGIN" are different variables.

4
  • that's strange because my Oracle SQL Developer output window was treating it as an error; I re-ran my script in my question and now it passes. In any case, I was looking for the appropriate bypass with quotes as you suggested-thank you! Commented May 4, 2016 at 17:03
  • 1
    Would upvote 20 times if I could - there should really be no reason to ever use a reserved word as a variable/column name. If you are using a reserved word, chances are your variable/column name isn't descriptive enough. With a variable it's easy to have a prepend on it (my_timestamp as an example). Commented May 4, 2016 at 21:05
  • 1
    begin number; will make your app non-portable and your debugging difficult. Anyone who uses reserved words in PL/SQL (or anywhere) should be committed :-) You have IIRC 30 whole characters per identifier - the manual says you can quote reserved words, but this is not recommended.
    – Vérace
    Commented May 5, 2016 at 0:47
  • 1
    To add to that - DON'T DO IT. Its just not worth it. I've turned up at client sites to find that users insisted on certain names for table columns that included reserved words as well as 'non-standard' characters, e.g. '-'. I see it as a lack of consideration for anyone running or debugging code later. Commented May 5, 2016 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.