Re: Impossible stored procedure problem?

erithtotl_at_yahoo.com (Ian Murphy) wrote in message news:<4e8be5d6.0305201419.2c9f2ee1_at_posting.google.com>...
> By my understanding, this should be impossible. Here's a snippet from
> a stored procedure:
>
> create or replace procedure TESTPROCEDURE
> (
> INRCGID_PERSON in varChar2:=null,
> INRCGID_PROJECT in varChar2:=null,
> INnDMSFOLDERID in number:=null,
> INnGETALLFOLDERS in number :=1,
> INnGETALLFOLDERS_NOACLCHECK in number :=0,
> INnGETSUBFOLDERS in number :=0,
> INnGETFOLDERFORTYPE in number:=0,
> INnGETPARENTFOLDER in number :=0,
> INnFOLDERTYPE in number:=0,
> INvcNODENAME in varchar2:=null,
> INRCGID_FOLDERID in varChar2:=null,
> INRCGID_PARENTFOLDERID in varChar2:=null,
> OUTnHASFOLDER out number, --size=10
> OUTcPARENTFOLDERID out varChar2, --size=14
> retCur in out globalPkg.RCT1
> )
> is
> getType number;
> projectId varchar2(16);
> persInGrp number:=0;
>
> begin
>
> if INNGETALLFOLDERS is null then
> insert into debug_dump values('NULL',sysdate);
> else
> insert into debug_dump values('NOT NULL',sysdate);
> end if;
>
> -----------------------------
>
> By my understanding, this if statement should ALWAYS insert 'NOT NULL'
> since the parameter INNGETALLFOLDERS has a default value of 1.
>
> But thats not what is happening. Somehow, we are getting 'NULL'
> inserted.
>
> This is blowing up the procedure. What do I do? Am I right in saying
> this can't happen? Or do I have a false assumption here?
>
> This is, as far as I know, running on the latest version of Oracle 9i.

This can happen. You have specified default value of 1. To Plsql engine, this means that when this parameter is not passed in procedure call,

execute TESTPROCEDURE;

Default value will be used i.e. 1 and IF statement will return NOT NULL. Only time default value will be overwritten is if you actually pass something to this parameter... even NULL,

execute TESTPROCEDURE(...,...,...,NULL,...);

This will return NULL in IF statement. To test this in another way, change INnGETALLFOLDERS in number :=1, to INnGETALLFOLDERS in number := NULL, and then try.

Regards
/Rauf Sarwar Received on Tue May 20 2003 - 22:04:45 CDT