Re: NULL or empty string argument in procedure

From: <rtproffitt_at_my-deja.com>
Date: Thu, 23 Mar 2000 18:23:11 GMT
Message-ID: <8bdnds$aqq$1@nnrp1.deja.com>

morang3nf,
Here are some observations on your problem...

1. Oracle makes no distinction between NULL and Empty strings. Strings either are NULL or have text.
2. I don't think your problem is with passing the parameters, or passing NULLs into the procedure, otherwise you would have a "numeric or data error".
3. The problem seems to be with your SQL of your Select Into phrase.
   > SELECT PERSON_ID INTO PERSON_KEY
   > FROM PERSON
   > WHERE (LAST_NAME = LAST)
   > AND (FIRST_NAME = FIRST) AND (MIDDLE_INITIAL =
   > MIDDLE) AND
   > (EMAIL = EMAILADD) AND
   > (SEX = GENDER) AND (PERSON_SUFFIX_CD = SUFFIX);
   

You stated,
> I want to be able to use the procedure to enter
> records, but I need to be able to pass either
> NULLs or empty string values for fields that are
> optional.

However, in your query you are referencing optional fields as absolutes.
Therefore,.... if emailadd is null or gender is null, then SEX=GENDER will return 'no data found'.

There are a number of ways to restructure the query...The simplest being, Only search using the Key fields which uniquely identify the row, in your case, perhaps Last_name and First_Name, etc. By definition, if a field is optional, then it wouldn't be needed to identify the row. Or, other examples:

   Where xxxxx...
    And ((field = optionalfield) or (optionalfield IS NULL))     And xxxx...

Or,

   Where xxxx....
   And Field = DECODE( optionalfield, NULL, Field, optionalfield)    (when the passed value is null, force a true condition     field=field else use the optionalfield: field = optionalfield)

Or, restructure the entire process:
a. Remove the ID assignment from the Person Trigger... b. Rewrite code so you get the ID first, then do the

   work.

   Person_Key = GetPersonID(Last,First,Middle); ...etc.
   Insert into Person .... (last, first, person_key...etc);
   Insert into Landlord ...(person_key...etc);

4. When do you COMMIT the work?
Procedures and functions will not automatically COMMIT; Either add COMMIT as last step, or, elsewhere in your processing you have a COMMIT step...

Good Luck,
Robert Proffitt
RTProffitt AT beckman DOT com
Beckman Coulter
Brea California

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 23 2000 - 12:23:11 CST