Re: Impossible stored procedure problem?

Ian Murphy wrote:
> 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.

Not true. A parameter gets its default value only if you don't pass *any* value to the parameter. But if you explicitly pass a NULL to the parameter, then the parameter will be set to NULL regardless of what default value it has.

Cheers,
Dave

>
> 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.
Received on Tue May 20 2003 - 20:07:14 CDT