mardi 19 mai 2015

Check if a string value matches up with the content of an existing table in Oracle 11G

At the moment I am not working as efficient as I could be. For the problem I have I almost know certain that there is a smarter and better way to fix it.

What I am trying to do: I got a string like this:

'NL 4633 4809 KTU'

The NL is a country code from an existing table and KTU is an university code from an existing table. I need to put this string in my function and check if the string is validated.

In my function (to validate the string) this is what I am working on. I have managed to split up the string with this:

 countryCode := checkISIN; -- checkISIN is the full string ('NL 4633 4809 KTU') and I am giving the NL value to this variable. countryCode is the type varchar2(50)
 countryCode := regexp_substr(countryCode, '[^ ]+', 1, 1);

Now that I have the country code as shown below:

NL
Has valid country code

I want to validate/check the country code for it's existence from it's own table. I tried this:

if countryCode in ('NL', 'FR', 'DE', 'GB', 'BE', 'US', 'CA')
 then dbms_output.put_line('Has valid country code');
 else
 dbms_output.put_line('Has invald country code. Change the country code to a valid one');
 end if;

This works, but it's not dynamically. If someone adds a country code then I have to change the function again.

So is there a (smart/dynamically) way to check the country codes for their existing tables?

I hope my question is not too vague

Cheers

Aucun commentaire:

Enregistrer un commentaire