Re: Impossible stored procedure problem?

"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.

Oracle 9.2.0.3

  1 create or replace procedure crap (in_val in number := 1)   2 as
  3 begin
  4 if (in_val is null) then
  5 dbms_output.put_line('NULL');
  6 else
  7 dbms_output.put_line('NOT NULL');   8 end if;
  9* end;
SQL> / Procedure created.

SQL> set serveroutput on size 10000
SQL> exec crap(2)
NOT NULL PL/SQL procedure successfully completed.

SQL> exec crap
NOT NULL PL/SQL procedure successfully completed.

SQL> exec crap(null)
NULL PL/SQL procedure successfully completed.

Does this say it all?

Anurag Received on Tue May 20 2003 - 20:01:14 CDT