I'm trying to check if a postal code is valid. The postal code in the sample code is NOT valid. So it should not return any values.
postcode := '1059NJ';
SELECT P.POSTCODE INTO test3
FROM POSTCODE P
WHERE P.POSTCODE = UPPER(postcode)
AND P.CITY = INITCAP(LOWER('Amsterdam'))
AND P.STREET = INITCAP(LOWER('Aalsmeerweg'))
AND 14 BETWEEN P.MINNUMBER AND P.MAXNUMBER
AND( NUMBERTYPE = 'even' OR NUMBERTYPE = 'mixed');
DBMS_OUTPUT.PUT_LINE(test3);
However, instead of returning nothing, it returns this
1059AJ
That is the valid postal code. It shouldn't have done that.
Here comes the funny bit, if I were to put the postal code inside the '' signs it actually doesn't return anything.
SELECT P.POSTCODE INTO test4
FROM POSTCODE P
WHERE P.POSTCODE = UPPER('1059NJ')
AND P.CITY = INITCAP(LOWER('Amsterdam'))
AND P.STREET = INITCAP(LOWER('Aalsmeerweg'))
AND 14 BETWEEN P.MINNUMBER AND P.MAXNUMBER
AND( NUMBERTYPE = 'even' OR NUMBERTYPE = 'mixed');
DBMS_OUTPUT.PUT_LINE(test4);
Is this an Oracle bug?
As Mr Llama hinted, this is a scope issue. Within your SQL statement the plain postcode is interpreted first as a column name, and since there is such a column that is used. Lack of an alias qualifier (p.) does not preclude the value being taken from that table, or promote the local variable to be used above the column name. So you are really saying:
WHERE P.POSTCODE = UPPER(p.postcode)
... which isn't what you want, and means that clause will always evaluate to true for any uppercase value.
If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.
The simplest answer is to rename your local variable, and it's common to have a naming convention that enforces this; for example prefixing formal parameters with p_, local variables with l_ etc.
If this is in a named block you can also prefix the variable with that name; so if your function is called IsPostcodeValid then you can do:
WHERE P.POSTCODE = UPPER(IsPostCodeValid.postcode)
A naming convention is simpler, shorter and clearer (IMO), but you can always combine both if you want, and refer to IsValidPostcode.p_postcode for extra explicitness...
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments