RE: Null Values Returned from a Stored Procedure

[Ron Reidy]

How are you checking for NULL?

I normally use:

if (!defined $var)

http://www.orafaq.com/faqperl.htm#DBDNULL

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Denesa K Shaw [mailto:denes...@CenterPointEnergy.com]
Sent: Thursday, June 09, 2005 7:24 AM
To: dbi-...@perl.org
Subject: Null Values Returned from a Stored Procedure

How does a script handle when null values are returned from a stored
procedure?
I checked for Null values in a individual string, but it seemed not to
make it to my if statement.
Thanks in advance.

my $sql = qq(
BEGIN
package.procedure(:p1,:p2,TO_DATE(:p3,:fmt),:out_cursor);
END;
);

my $func = $dbh->prepare($sql);
$func -> bind_param(":fmt","mm/dd/yyyy" );
$func->bind_param(":p1",$TP_ID);
$func->bind_param(":p2",$INB_OUTB_IN);
$func->bind_param(":p3",$Formatted_RELOAD_FILE_TS);
$func->bind_param_inout(":out_cursor", \$out_cursor, 0,{ ora_type=>ORA_RSET
} );
$func->execute;

if( $@) ###Check for failure of executing the Stored Procedure
{
### warn "Execution of stored procedure failed: $DBI::errstr\n";
warn TMH::writeConsole(*LOGFILE,"Execution of stored procedure failed:
$DBI::errstr\n");
$dbh->rollback;
}

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

[Denesa K Shaw]

[Michael A Chase]

Quoting Denesa K Shaw <denes...@CenterPointEnergy.com>:

> How does a script handle when null values are returned from a stored
> procedure?

The same as any other NULL values.

http://search.cpan.org/~timb/DBI/DBI.pm#Notation_and_Conventions

But you are asking the wrong question. You want to know when the stored
procedure call failed. Even successful calls could pass back NULL, depending
on the procedure.

> I checked for Null values in a individual string, but it seemed not to
> make it to my if statement.

Where? I don't see any checks for NULL (or undef) in any of the code you
provided.

> my $sql = qq(
> BEGIN
> package.procedure(:p1,:p2,TO_DATE(:p3,:fmt),:out_cursor);
> END;
> );

You didn't mention what RDBMS you are using, but this looks like Oracle PL/SQL.

> my $func = $dbh->prepare($sql);

Unless you've set RaiseError to 1, the only hint you have if prepare() failed is
that the handle ($func) is undef and that doesn't tell you what went wrong.

http://search.cpan.org/~timb/DBI/DBI.pm#Outline_Usage
http://search.cpan.org/~timb/DBI/DBI.pm#ATTRIBUTES_COMMON_TO_ALL_HANDLES

You can also check the individual calls for error conditions. Look at most fo
the method call examples in the manual
(http://search.cpan.org/~timb/DBI/DBI.pm).

> $func -> bind_param(":fmt","mm/dd/yyyy" );
> $func->bind_param(":p1",$TP_ID);
> $func->bind_param(":p2",$INB_OUTB_IN);
> $func->bind_param(":p3",$Formatted_RELOAD_FILE_TS);
> $func->bind_param_inout(":out_cursor", \$out_cursor, 0,{ ora_type=>ORA_RSET
> } );
> $func->execute;
>
> if( $@) ###Check for failure of executing the Stored Procedure

I don't see an eval block and you haven't shown that you set RaiseError, so
there's no indication that $@ would be set for an error.

> {
> ### warn "Execution of stored procedure failed: $DBI::errstr\n";
> warn TMH::writeConsole(*LOGFILE,"Execution of stored procedure failed:
> $DBI::errstr\n");
> $dbh->rollback;
> }

--
Mac :})