Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Mr. Muskrat (4572)

Mr. Muskrat
  reversethis-{moc ... ta} {tarksum.rm}
I'm married with 2 girls. I work as a full time Perl programmer for a Land Mobile Radio company in the Dallas/Fort Worth area.

I am enrolled at the Art Institute of Pittsburgh - Online working towards a Bachelor of Science in photography.

My other blog [blogspot.com]

Journal of Mr. Muskrat (4572)

Friday August 24, 2007
03:57 PM

Oracle Quotes

[ #34210 ]
Oracle throws some odd errors if you mistakenly use the wrong kind of quotes.

SQL> CREATE OR REPLACE FUNCTION test (p_in IN VARCHAR2)
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5    IF p_in = "" THEN
  6      RETURN 0;
  7    END IF;
  8  END test;
  9  /
CREATE OR REPLACE FUNCTION test (p_in IN VARCHAR2)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")

Say what? That error message does anything but scream "you used the wrong quoting character".

SQL> CREATE OR REPLACE FUNCTION test (p_in IN VARCHAR2)
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5    IF p_in = '' THEN
  6      RETURN 0;
  7    END IF;
  8  END test;
  9  /

Function created.


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Oracle Quotes 7 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • ... to add an appropriate error message?

    ERROR at line 5:
    ORA-99999: wrong quoting character used
    or even

    ERROR at line 5:
    ORA-ID10T: Use single quotes instead dummy!
    • Or to allow EITHER single or double quoting (like some databases *cough* Informix *cough* allow)? I was spoiled by learning SQL on Informix, so when I started on Oracle and MS-SQL, I cursed and then learned to change my habits. I suppose there's some sort of ANSI standard though...
  • There's no such thing as a zero length string in Oracle. '' is identical to NULL. So

    p_in = ''
    will never return true.

    Use

    p_in is null
    instead.
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.