I have a little Perl problem. When I call function dbi_select_test like SELECT * from dbi_select_test() I get the expected result.
However when I call SELECT * from dbi_select I get an error message saying "ERROR: error from Perl function: setof-composite-returning Perl function must call return_next with reference to hash at line 9." My perl knowledge seems to be too limited to find the solution myself. Can anyone help me out, please?
CREATE OR REPLACE FUNCTION "public"."dbi_select_test" () RETURNS SETOF text AS
$body$
use DBI;
my $dbh = DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM address')
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
while (my $row = $sth->fetchrow_hashref) {
return_next($$row{'ADDRTABLEID'});
}
$sth->finish;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION "public"."dbi_select" () RETURNS SETOF "bmssa"."ADDRESS" AS
$body$
use DBI;
my $dbh = DBI->connect('dbi:Oracle:bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '********')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM address')
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
while (my %row = $sth->fetchrow_hashref) {
return_next($row);
}
$sth->finish;
return;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TABLE "bmssa"."ADDRESS" (
"ADDRTABLEID" TEXT,
"ADDRRECID" TEXT,
"LINENUM" TEXT,
"TYPE" TEXT,
"NAME" TEXT,
"ADDRESS" TEXT,
"PHONE" TEXT,
"TELEFAX" TEXT,
"COUNTRY" TEXT,
"DEL_SWIFTNUMBER" TEXT,
"ZIPCODE" TEXT,
"STATE" TEXT,
"COUNTY" TEXT,
"TELEX" TEXT,
"URL" TEXT,
"PHONELOCAL" TEXT,
"CELLULARPHONE" TEXT,
"EMAIL" TEXT,
"TAXGROUP" TEXT,
"CITY" TEXT,
"STREET" TEXT,
"PAGER" TEXT,
"SMS" TEXT,
"REFZIPCODE" TEXT,
"DATAAREAID" TEXT,
"RECID" TEXT
) WITHOUT OIDS;
