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 or refer me to a better suited forum? Thanks
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;
