Invoking oracle storedprocedure through perl script

Question

Can some one please help me to let me know how to call a oracle subroutine in Perl script

I have a procedure already existing in oracle db. Say below

Case 1) return a ref cursor and accept a IN parameter.

CREATE OR REPLACE PROCEDURE procCursorExample(
  cursorParam OUT SYS_REFCURSOR, userNameParam IN VARCHAR2)
IS
BEGIN
  OPEN cursorParam FOR
    SELECT * FROM DBUSER WHERE USERNAME = userNameParam;
  END;

In SQL developer i can then execute it directly:

DECLARE
  dbUserCursor SYS_REFCURSOR;
  dbUserTable DBUSER%ROWTYPE;
BEGIN
  procCursorExample(dbUserCursor,'mkyong');
  LOOP
    FETCH dbUserCursor INTO dbUserTable;
    EXIT WHEN dbUserCursor%NOTFOUND;
    dbms_output.put_line(dbUserTable.user_id);
  END LOOP;
  CLOSE dbUserCursor;
END;

Can some tell me how to invoke subroutine with argument through Perl script

Anwser

#!/usr/bin/perl
use warnings ;
use strict ;
use DBI;
print "Connecting to DB..";
my $dbh = DBI->connect('dbi:Oracle:xe',  'scott', 'tiger') or
          die "Cannot connect to DB => " . DBI->errstr;

# prepare ????????

am not sure about prepare statement. Any help is highly appreciable.

Answer 1

Please read the documentation examples for calling stored procedures in Perl with DBD::Oracle, which is the driver you are using.

From this link specifically:

use DBI;
 
my($db, $csr, $ret_val);
 
$db = DBI->connect('dbi:Oracle:database','user','password')
      or die "Unable to connect: $DBI::errstr";
 
# So we don't have to check every DBI call we set RaiseError.
# See the DBI docs now if you're not familiar with RaiseError.
$db->{RaiseError} = 1;
 
# Example 1        Eric Bartley <[email protected]>
#
# Calling a PLSQL procedure that takes no parameters. This shows you the
# basic's of what you need to execute a PLSQL procedure. Just wrap your
# procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
#
# p.s. If you've used SQL*Plus's exec command all it does is wrap the
#      command in a BEGIN END; block for you.
 
$csr = $db->prepare(q{
  BEGIN
    PLSQL_EXAMPLE.PROC_NP;
  END;
});
$csr->execute;
 
 
# Example 2        Eric Bartley <[email protected]>
#
# Now we call a procedure that has 1 IN parameter. Here we use bind_param
# to bind out parameter to the prepared statement just like you might
# do for an INSERT, UPDATE, DELETE, or SELECT statement.
#
# I could have used positional placeholders (e.g. :1, :2, etc.) or
# ODBC style placeholders (e.g. ?), but I prefer Oracle's named
# placeholders (but few DBI drivers support them so they're not portable).
 
my $err_code = -20001;
 
$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 croak (die) so we catch that with eval.
eval {
  $csr->execute;
};
print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";
 
 
# Example 3        Eric Bartley <[email protected]>
#
# Building on the last example, I've added 1 IN OUT parameter. We still
# use a placeholders in the call to prepare, the difference is that
# we now call bind_param_inout to bind the value to the place holder.
#
# Note that the third parameter to bind_param_inout is the maximum size
# of the variable. You normally make this slightly larger than necessary.
# But note that 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;
 
$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 will automagically update the value of $is_odd
$csr->execute;
 
print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";
 
 
# Example 4        Eric Bartley <[email protected]>
#
# What about the return value of a PLSQL function? Well treat it the same
# as you would a call to a function from SQL*Plus. We 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 = "";
 
$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";
 
$db->disconnect;