CODEWITHSUNDEEP
oracle-databaseperl
David Faizulaev
David Faizulaev

Reputation: 5721

Perl - how to call Oracle DB stored procedure

I'm attempting to execute a stored procedure and (for now) get the number of rows returned. But I keep getting the following error:

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCI
StmtExecute) [for Statement "exec VSU22.DB_API.GetUsersInfo(?);" with ParamValue
s: :p1=1] at C:\NNInstall\auit.pl line 19.

Here is the script:

#!/usr/bin/perl
use strict;
use dbi;

my ($connection_string, $nn_dbh, $extract_sth);
my $dbIPAddress = "192.168.147.55";
my @row;
my $query_result=0;
my $var;
$connection_string = "dbi:Oracle:host=$dbIPAddress;sid=NNVSDB";
$nn_dbh->{RaiseError} = 1;
$nn_dbh->{RowCacheSize} = 256;
$nn_dbh = DBI->connect($connection_string, 'DBSUER', 'DBUSER')
    or die "Connection failed: $DBI::errstr";

$extract_sth = $nn_dbh->prepare('exec DBUSER.API.UsersInfo(?);');
$extract_sth ->bind_param(1, 1);
my $num_ofOrows= $extract_sth->execute();

What Am I doing wrong?

Upvotes: 0

Views: 3404

Answers (2)

Clarius
Clarius

Reputation: 1419

I'm running Oracle XE in a Docker container and using their sample database.

Here's a stored procedure that takes a parameter and returns a set of records.

CREATE OR REPLACE PROCEDURE customer_orders(cust_id IN NUMBER, cur OUT SYS_REFCURSOR) IS 
BEGIN 
    OPEN cur FOR 
        SELECT * 
        FROM orders 
        WHERE customer_id = cust_id; 
end;
/

Here's the Perl that executes the procedure, passing the customer id as a parameter, and processing the returned results set.

use DBI;
use DBD::Oracle qw(:ora_types);
 
# get the database connection
$dbh = DBI->connect("dbi:Oracle:localhost/xepdb1", "ot", "Orcl1234") || die(DBI->errstr());

$sth = $dbh->prepare("BEGIN customer_orders(?, ?); END;");

$sth->bind_param(1, 49);

$sth->bind_param_inout(2, \$rsh, 0, {ora_type => ORA_RSET});

$sth->execute();

while ( @row = $rsh->fetchrow_array ){
    foreach ( @row ){
        print "$_\t";
    };
    print "\n";
}

$dbh->disconnect;

I hope this saves all who pass this way the time it took me to figure it out.

Luck.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146450

EXEC[CUTE] is a SQL*Plus command. It's normally not implemented in other SQL clients. You probably need to use an anonymous PL/SQL block;

BEGIN
    DBUSER.API.UsersInfo(?);
END;

Upvotes: 1

Related Questions