Spatial data is available in the table st_country_postal_code with index. In order to get the distance between postal codes, we used the below query:
SELECT sdo_nn_distance (1) distance, z.*
FROM st_country_postal_code z
WHERE z.postal_code = UPPER ('V3G1M2')
AND z.country_id = '124'
AND 'TRUE' = sdo_nn (z.spatial_data, (SELECT spatial_data FROM st_country_postal_code WHERE country_id = '124' AND postal_code = UPPER ('V2R0N3')), 'sdo_num_res=999 unit=' || 'KM', 1 );
For some Canadian zipcodes like V2R0N3, V3G1M2, J8H2A7, for the above query I am getting no rows returned. Is there a constraint for the sdo_nn function to not let me calculate the distance?
If all you want is to compute the distance between two postal codes, then you are doing it in a very complex way. The simple and proper way is this:
select sdo_geom.sdo_distance (a.spatial_data, b.spatial_data, 0.05, 'unit=km')
from st_country_postal_code a, st_country_postal_code b
where a.country_id = '124'
and a.postal_code = UPPER ('V3G1M2')
and b.country_id = '124'
and b.postal_code = UPPER ('V2R0N3');
i.e. just fetch the two shapes and compute the distance between them.
The SDO_NN_DISTANCE() construct is exclusively for use in conjunction with the SDO_NN operator, whose goal is to return the N closest objects (here the 999 closest postal codes) to an object (a postal code). Not really what you want.
As a matter of fact, the probable reason why you do not see all postal codes is precisely because you effectively perform a search for the 999 closest postal codes to one specific postal code. I assume the ones you do not see are just not in that set.