Re: Impossible stored procedure problem?

Just my 2 cents,

Why not write:

INSERT INTO debug_dump VALUES(DECODE(INNGETALLFOLDERS, NULL, 'NULL', 'NOT NULL'), sysdate);

so that there exist just 1 single SQL instead of 2 and get rid of all those pl/sql logics?

> if INNGETALLFOLDERS is null then
> insert into debug_dump values('NULL',sysdate);
> else
> insert into debug_dump values('NOT NULL',sysdate);
> end if;

"Ian Murphy" <erithtotl_at_yahoo.com> 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.
Received on Wed May 21 2003 - 11:22:47 CDT