How to user perl DBI/DBD calling a stored procedure

metalink 62329.1

PURPOSE

This document provides an example of the method to call a PL/SQL-stored
procedure from a PERL script using the DBI/DBD interface.

SCOPE & APPLICATION

PERL cartridge developers, Web site administrators

RELATED DOCUMENTS

Note 62320.1 - Upgrading DBD/DBI
perldoc DBD::Oracle


This bulletin provides an example of calling a PL/SQL procedure from a PERL
script. It may be necessary to follow the steps in Note 62320.1 to upgrade
the versions of DBD/DBI if you intend to run this script through the PERL
cartridge.

This example is from the PERL DBD:Oracle documentation.

Create the following package in the schema you want to use:

CREATE OR REPLACE PACKAGE plsql_example
IS
PROCEDURE proc_np;

PROCEDURE proc_in (err_code IN NUMBER);

PROCEDURE proc_in_inout(test_num IN NUMBER,
is_odd IN OUT NUMBER);

FUNCTION func_np RETURN VARCHAR2;
END plsql_example;
/

CREATE OR REPLACE PACKAGE BODY plsql_example
IS
PROCEDURE proc_np IS
whoami VARCHAR2(20) := NULL;
BEGIN
SELECT USER INTO whoami FROM DUAL;
END;

PROCEDURE proc_in (err_code IN NUMBER) IS
BEGIN
RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
END;

PROCEDURE proc_in_inout (test_num IN NUMBER,
is_odd IN OUT NUMBER) IS
BEGIN
is_odd := MOD(test_num, 2);
END;

FUNCTION func_np RETURN VARCHAR2 IS
ret_val VARCHAR2(20);
BEGIN
SELECT USER INTO ret_val FROM DUAL;
RETURN ret_val;
END;

END plsql_example;


The PERL script:
----------------- CUT HERE ----------------------
#!/usr/local/bin/perl

use DBI;

my($db, $csr, $ret_val);

# Connect to an Oracle database.
# Modify the following with suitable values for the net alias and your
# db userid and password
$db = DBI->connect('dbi:Oracle:your_db_alias','your_user','your_password')
or die "Unable to connect: $DBI::errstr";

# Set Raise Error so you do not have to check every DBI call.
# See the DBI (perldoc DBI) documentss now if you are not familiar with
# RaiseError.
$db->{RaiseError} = 1;

# Example 1

# This example demonstrates how to call a PL/SQL procedure that takes no
# parameters. This shows you the fundamentals of what you need to execute a
# PL/SQL procedure. Wrap your procedure call in a BEGIN END; block as you
# do with the SQL*Plus execute command.

print "Example 1...";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_NP;
END;
});
$csr->execute;
print "PLSQL_EXAMPLE.PROC_NP called successfully\n\n";

# Example 2

# For this example, call a procedure that has one IN parameter. Here, use
# bind_param to bind out parameter to the prepared statement, as you can do for
# an INSERT, UPDATE, DELETE, or SELECT statement.

# You can use positional placeholders (for example, :1, :2, etc.) or Open
# Database Connectivity (ODBC) style placeholders (for example, ?). However,
# it is ideal to use Oracle's named placeholders if the DBI drivers support
# them (selected DBI drivers support them, so they are not portable).

my $err_code = -20001;

print "Example 2: Passing a parameter and catching an exception\n";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN(:err_code);
END;
});

$csr->bind_param(":err_code", $err_code);

# PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to fail.
# Because we set RaiseError, the DBI will die, so we catch that with eval.

eval {
$csr->execute;
};
print "Called PLSQL_EXAMPLE.PROC_IN($err_code): \n",
"\$\@='$@'\n", "errstr=$DBI::errstr \n", "ret_val=$ret_val\n\n";

# Example 3

# Building on the last example, add one IN OUT parameter. Use a placeholder in
# the call to prepare. The difference is that we now call bind_param_inout to
# bind the value to the placeholder.

# The third parameter to bind_param_inout is the maximum size of the variable.
# You normally make this slightly larger than necessary. However, the PERL
# variable will have that much memory assigned to it, even if the actual value
# returned is shorter.

my $test_num = 5;
my $is_odd;

print
"Example 3: Calling PLSQL_EXAMPLE.PROC_IN_INOUT($test_num, \$is_odd)\n";
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
END;
});

# The value of $test_num is _copied_ here
$csr->bind_param(":test_num", $test_num);

$csr->bind_param_inout(":is_odd", \$is_odd, 1);

# The execute automagically updateS the value of $is_odd.
$csr->execute;

print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n\n";

# Example 4

# Treat the return value of a PL/SQL function the same as you treat a call to
# a function from SQL*Plus. Add a placeholder for the return value and bind it
# with a call to bind_param_inout so we can access its value after execute.

my $whoami = "";

print "Example 4: Calling funtion PLSQL_EXAMPLE.FUNC_NP\n";
$csr = $db->prepare(q{
BEGIN
:whoami := PLSQL_EXAMPLE.FUNC_NP;
END;
});

$csr->bind_param_inout(":whoami", \$whoami, 20);
$csr->execute;
print "Your database user name is $whoami\n\n";

# Close the cursor.
$csr->finish;

# Disconnect from the database.
$db->disconnect;