It appears that you get this error (and, I agree it's not a particularly useful error) if you try to connect to a DBM file that doesn't exist.
In order for this code to work, you need to create a DBM file called temp_table.pag which contains the data that you want to use.
As always, it is a good idea to read the documentation of the module that you are trying to use. The DBD::DBM documentation includes a sample program that I had to adapt lightly in order to get it working:
#!/usr/bin/perl
use warnings;
use strict;
use feature 'say';
use DBI;
my $dbh = DBI->connect('dbi:DBM:');
$dbh->{RaiseError} = 1;
for my $sql( split /;\n+/,"
CREATE TABLE user ( user_name TEXT, phone TEXT );
INSERT INTO user VALUES ('Fred Bloggs','233-7777');
INSERT INTO user VALUES ('Sanjay Patel','777-3333');
INSERT INTO user VALUES ('Junk','xxx-xxxx');
DELETE FROM user WHERE user_name = 'Junk';
UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel';
SELECT * FROM user
"){
say $sql;
my $sth = $dbh->prepare($sql);
$sth->execute;
# It was the "if" clause that I had to change.
# Previously it was: if $sth->{NUM_OF_FIELDS}.
# I wonder if the behaviour of NUM_OF_FIELDS has changed.
$sth->dump_results if $sql =~ /\bselect\b/i;
}
$dbh->disconnect;
Having run this, I have files called user.pag and user.dir. I can then run a program that is very much like yours successfully.
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
my $dbh = DBI->connect('dbi:DBM:');
my $sth = $dbh->prepare('SELECT user_name FROM user');
$sth->execute;
while (my $row = $sth->fetch) {
print "$row->[0]\n";
}
$sth->execute();
Update: I also meant to add that reading the documentation will tell you which options are valid for DBD::DBM. It looks to me like sid and host aren't doing anything useful here. Also, username and password are unlikely to be necessary for file-based DBDs.
Update 2: Are you using the right database driver here? DBD::DBM is for accessing DBM files that sit on your local system. A DBM file is a really simple, single-table, file-based data file. It's not really a "database" as we would understand the term these days. It's very old technology and is rarely used these days.
Your comments below make it look like you're trying to connect to a real relational database system. In that case, DBD::DBM is completely the wrong database driver to use. You need the database driver that matches the system that you are using. Your use of the term "SID" makes me think that you're using Oracle - so you would need DBD::Oracle.
