perl: executing stored procedure which uses select

Question

I am running into some errors when trying to run a stored procedure through Perl.

The following statements work fine in sqlDeveloper and return expected results

EXEC alpha.beta.do_something()

This does not generate any output, as expected

Select alpha.beta.get_something(alpha.STRING_ARRAY('xyz')) from tableName

This generates output which is given to us from the DBA guys. It has this format in DB terms: Output Type:Ref Cursor. The cursor has two columns, a and b

When running through Perl, I get errors when running the second statement.

This is the Perl code that I am using:

sub execute_procedure {
    my $dbh       = shift;
    my $procedure = shift;

    my $statement = "BEGIN $procedure; END;"
    my $sth;
    eval {$sth = $dbh->prepare($statement);};
    eval {$sth->execute();};

    if ($@) {
        print "failed";
    } else {
        print "passed";
    }
}

# Call 1
execute_procedure($dbh, "alpha.beta.do_something()")

# Call 2
execute_procedure($dbh, "Select alpha.beta.get_something(alpha.STRING_ARRAY('xyz')) from tableName")

Call 1 works as expected, does not generate any errors

Call 2 results in this error

"Select alpha.beta.get_something(alpha.STRING_ARRAY('xyz')) from tableName" results in :PLS-00428: an INTO clause is expected in this SELECT statement (DBD ERROR: error possibly near <> indicator at char 6 in 'BEGIN <>Select alpha.beta.get_result(alpha.STRING_ARRAY('xyz')) from tableName; END;')

if I remove BEGIN and END from the statement in the execute_procedure function like this:

# my $statement = "BEGIN $procedure; END;";
my $statement = "$procedure";

then it does not generate any error, but it returns a result which I don't know how to parse

my $result = $sth->fetchrow_hashref;
print Dumper($result)

results in:

$VAR1 = {
  'alpha.beta.get_result(alpha.STRING_ARRAY(\'xyz\'))' => bless( {}, 'DBI::st' )
};

so, my questions are

What is the right way to execute a stored procedure which uses the SELECT statement? Should it be called with BEGIN $procedure; END; or without the BEGIN/END?
How can I get the actual data from bless( {}, 'DBI::st' )? I have tried to use different fetch options: fetchrow, fetchrow_hashref etc. but I am not able to get the actual data from the object

This looks very familiar. Have you asked the same thing already during the last few days? — simbabque
– simbabque, Commented Feb 3, 2017 at 16:40
yes, I did ask this yesterday, but it was missing details and people asked me to provide code. I just updated the other question and will be removing that shortly — comatose
– comatose, Commented Feb 3, 2017 at 16:42
Note that the DBI module will not allow you to prepare and execute multiple statements at once. Also, it will depend on the database driver whether $dbh->prepare('BEGIN') succeeds, and the canonical way to create a transaction is to set $dbh->{AutoCommit} = 0 before executing the transaction, and then $dbh->commit or $dbh->rollback accordingly. AutoCommit is enabled by default, and if you set it to a false value then every database action that you request will be logged, waiting to be actioned or discarded. — Borodin
– Borodin, Commented Feb 3, 2017 at 17:12

simbabque · Accepted Answer · 2017-02-03 16:43:01Z

1

$VAR1 = { 'alpha.beta.get_result(alpha.STRING_ARRAY(\'xyz\'))' => bless( {}, 'DBI::st' ) };

The value here is a DBI statement handle. It's the same thing you get when you do my $sth = $dbh->prepare(...). It's probably been executed already, so you can just call any of the fetch methods on it.

my $result = $sth->fetchrow_hashref;
while (
    my $res = $result->{'alpha.beta.get_result(alpha.STRING_ARRAY(\'xyz\'))'}
        ->fetchrow_hashref
) {
    # ...
}

But that looks horrible, and you might not know what the keys are. So you might want to just iterate over all of them.

my $result = $sth->fetchrow_hashref;
foreach my $key ( keys %${ $result } ) {
    print "Processing results of $key\n";
    while ( my $res = $result->{$key}->fetchrow_hashref ) {
        # do things with the $res

    }
}

You just need to figure out a good way to deal with the results. The above loop also works if there is just one key in the hash ref, so you could just use it anyway and hope that there is always just one.

answered Feb 3, 2017 at 16:43

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

comatose Over a year ago

That works, Thanks! so just to get some clarity on question (1), for stored procedures which return something, running them like "BEGIN $procedure; END;"; is not a good idea ?

simbabque Over a year ago

@comatose I don't know. I've not used that syntax before.

Collectives™ on Stack Overflow

perl: executing stored procedure which uses select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related