Matthew Morris PL/SQL 1Z0-144 exam study guide errors - 1

Ojasvi Sirohi

I am going through the Study Guide for 1Z0-144 Oracle DB 11g Program with PL/SQL Oracle Certification Prep book by MATTHEW MORRIS. This has a published year of 2012.

I have found these errors in this book.

In Page 19: MM says that "quoted user-defined identifiers" cannot have double-quotation marks which is true. But he also says that new line and null characters are not allowed.

But I just ran a block where these 2 are also accepted

My DB is a Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

DECLARE
   " asfsf "  number; -- null characters 
   " " NUMBER; -- null variable
   " bbb chr(10) yyy " number; -- New line is chr(10);
BEGIN
   " asfsf " := 333;
   " " := 444;
   " bbb chr(10) yyy " := 555;
   dbms_output.put_line(" asfsf ");
   dbms_output.put_line(" ");
   dbms_output.put_line(" bbb chr(10) yyy "); 
END;

333
444
555

Also, in the same page in the last paragraph MM says

"A quoted user-defined identifier is case-sensitive, with one exception. If a quoted user-defined identifier is a valid ordinary user-defined identifier, then the double-quotation marks are optional. If the quotes are left off, then the identifier is case-sensitive"

There are 2 problems here. Because this works:

DECLARE
   "abc" number := 5;
BEGIN
  dbms_output.put_line("abc");
END;
5

While this does NOT.

DECLARE
   "abc" number := 5;
BEGIN
  dbms_output.put_line(abc);
END;

Error starting at line 1 in command:
DECLARE
   "abc" number := 5;
BEGIN
  dbms_output.put_line(abc);
END;
Error report:
ORA-06550: line 4, column 24:
PLS-00201: identifier 'ABC' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Elapsed: 00:00:01.348

Oracle should treat abc as "abc" but it giving an error.

Also there is a typo in the above quote by MM. Shouldn't it be?

If the quotes are left on, then the identifier is case-sensitive

I hope MM and other forum members can help sort this out.

Matthew Morris

First of all -- congratulations on testing things out.  Too many people simply read books on programming and simply memorize the information rather than trying to apply the information in the real world. It's working with the code that will allow you to gain more personal familiarity with it.

That said... your test cases have problems.  What you are calling "NULL's" are spaces.  Spaces and NULLs are not the same thing.

" asfsf "  number; -- this is "[SPACE]asfsf[SPACE]"  

" " NUMBER; -- this is "[SPACE]"

To perform the NULL test you are attempting, the NULL would have to be two double-quotes immediately next to each other (i.e. no space between them)

"" NUMBER; -- null variable 

If you try this, Oracle will return the error:  "PLS-00182: Identifier cannot be an empty string"

Also, what you are calling a new line character is the character-literal text 'chr(10)'. Because it's enclosed within the quotes, those characters are not being converted into ASCII character 10.

" bbb chr(10) yyy " number; -- This is simply a strangely named identifier.

Ordinarily to put a new line into a string, the concatenation operators would be used (" bbb "" || chr(10) || " yyy "). Those can't be used with double-quotes. The only way I can think of to put a carriage return into a double-quoted identifier string is to physically put one in, in which case the identifier will span two lines as per the below:

" bbb

yyy " number; -- New line

If you try this -- Oracle returns the error: "PLS-00112: end-of-line in quoted identifier"

Oracle should treat abc as "abc" but it giving an error.

Also there is a typo in the above quote by MM. Shouldn't it be?
If the quotes are left on, then the identifier is case-sensitive

I don't know what release of the book you have.  The current version -- 1.3 has case-insensitive in that paragraph.

"A quoted user-defined identifier is case-sensitive, with one exception. If a quoted user-defined identifier is a valid ordinary user-defined identifier, then the double quotation marks are optional.  If the quotes are left off, then the identifier is case-insensitive."

What that means is I if I want to create a variable with the name Fred, I can use either of the lines below:

Fred    VARCHAR2(10);

"Fred" VARCHAR2(10);

If the variable is declared without the quotes, references to it in the PL/SQL block will work no matter what case is used.  If declared with double-quotes, then the quoted (and case-sensitive) identifier must be used throughout the block.

DECLARE 

   Fred    VARCHAR2(10);

BEGIN 

   Fred := 'a';

   FRED := 'b';

   fred := 'c';

   freD := 'd';

END;

If you really wanted to get right down to it, there is another exception.  If you quoted a legal identifier, and the quoted text was in upper-case, then you could use unquoted and case-insensitive references in the block:

DECLARE 

   "FRED"    VARCHAR2(10);

BEGIN 

   Fred := 'a';

   FRED := 'b';

   fred := 'c';

   freD := 'd';

END;

That said -- it's difficult to think of any legitimate reason to be creating identifiers that are so strange that they need to be placed in double-quotes. If the identifier that a developer decides to use cannot be used without double-quotes, then they are not picking good identifiers.

Matthew Morris

First of all -- congratulations on testing things out.  Too many people simply read books on programming and simply memorize the information rather than trying to apply the information in the real world. It's working with the code that will allow you to gain more personal familiarity with it.

That said... your test cases have problems.  What you are calling "NULL's" are spaces.  Spaces and NULLs are not the same thing.

" asfsf "  number; -- this is "[SPACE]asfsf[SPACE]"  

" " NUMBER; -- this is "[SPACE]"

To perform the NULL test you are attempting, the NULL would have to be two double-quotes immediately next to each other (i.e. no space between them)

"" NUMBER; -- null variable 

If you try this, Oracle will return the error:  "PLS-00182: Identifier cannot be an empty string"

Also, what you are calling a new line character is the character-literal text 'chr(10)'. Because it's enclosed within the quotes, those characters are not being converted into ASCII character 10.

" bbb chr(10) yyy " number; -- This is simply a strangely named identifier.

Ordinarily to put a new line into a string, the concatenation operators would be used (" bbb "" || chr(10) || " yyy "). Those can't be used with double-quotes. The only way I can think of to put a carriage return into a double-quoted identifier string is to physically put one in, in which case the identifier will span two lines as per the below:

" bbb

yyy " number; -- New line

If you try this -- Oracle returns the error: "PLS-00112: end-of-line in quoted identifier"

Oracle should treat abc as "abc" but it giving an error.

Also there is a typo in the above quote by MM. Shouldn't it be?
If the quotes are left on, then the identifier is case-sensitive

I don't know what release of the book you have.  The current version -- 1.3 has case-insensitive in that paragraph.

"A quoted user-defined identifier is case-sensitive, with one exception. If a quoted user-defined identifier is a valid ordinary user-defined identifier, then the double quotation marks are optional.  If the quotes are left off, then the identifier is case-insensitive."

What that means is I if I want to create a variable with the name Fred, I can use either of the lines below:

Fred    VARCHAR2(10);

"Fred" VARCHAR2(10);

If the variable is declared without the quotes, references to it in the PL/SQL block will work no matter what case is used.  If declared with double-quotes, then the quoted (and case-sensitive) identifier must be used throughout the block.

DECLARE 

   Fred    VARCHAR2(10);

BEGIN 

   Fred := 'a';

   FRED := 'b';

   fred := 'c';

   freD := 'd';

END;

If you really wanted to get right down to it, there is another exception.  If you quoted a legal identifier, and the quoted text was in upper-case, then you could use unquoted and case-insensitive references in the block:

DECLARE 

   "FRED"    VARCHAR2(10);

BEGIN 

   Fred := 'a';

   FRED := 'b';

   fred := 'c';

   freD := 'd';

END;

That said -- it's difficult to think of any legitimate reason to be creating identifiers that are so strange that they need to be placed in double-quotes. If the identifier that a developer decides to use cannot be used without double-quotes, then they are not picking good identifiers.

Ojasvi Sirohi

Thanks Matthew.

I have not seen the insensitive part. I read it as sensitive. My Bad.