Perl's DBI interface is excellent; however, it is intended for SQL not PL/SQL. If you do need procedures run, the traditional method is to use stored procedures invoked via standard queries (SELECT, INSERT...). If I was going to implement this, I would do the logic and looping in Perl, and the database interaction via DBI. Something like (untested):
use strict;
use warnings;
use DBI;
my $db = DBI->connect( $dbid,
$user,
$password,
{
PrintError => 0,
RaiseError => 1,
AutoCommit => 0,
},
);
my $query = $db->prepare(<<EOSQL);
INSERT INTO temp (field1, field2, field3)
VALUES(?, ?, ?)
EOSQL
my $x = 100;
for my $i (1 .. 10) {
$query->execute($i, $x, $i % 2 ? 'i is odd' : 'i is even');
$x += 100;
}
$db->commit;
Note that I've used a single query with placeholders rather than interpolation of values. This is both more efficient and protects you from SQL-injection and escaping issues. I've also taken advantage of the fact that 1 is true in Perl, thus bypassing an equality check. Finally, I explicitly included my target fields in the INSERT, since this makes you resilient to schema changes and makes maintenance more obvious.
#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.
| [reply]
[d/l] |
Howdy chai6125, welcome to the Monastery! If you'll allow me to chime in:
hi kennethk is it possible to call a sql as you have mentioned directly using sqlplus ?
Yes, it is. Indeed, as kennethk pointed out, there's a variety of different ways you can do this, depending on your specific needs.
I have heard some people saying to use dbi i really didnt understand that can you please explain me
DBI is Perl's major database interface (for SQL databases, anyway). You could probably use it instead of calling an external command, but depending on the context in which you want to accomplish this, I'm not really seeing an upside to this: there's nothing wrong with invoking external tools, so long as they're available.
Things might be different if you need to distribute your Perl script to machines that don't have sqlplus available, but until then I'd say don't overengineer things.
HTH!
| [reply] |