perl dbi oracle - ghdrako/doc_snipets GitHub Wiki
Connect
$dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);
$dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);
Connecting without environment variables or tnsname.ora file If you use the host=$host;sid=$sid style syntax, for example:
$dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user, $passwd);
then DBD::Oracle will construct a full connection descriptor string for you and Oracle will not need to consult the tnsname.ora file.
If a port number is not specified then the descriptor will try both 1526 and 1521 in that order (e.g., new then old). You can check which port(s) are in use by typing ``$ORACLE_HOME/bin/lsnrctl stat'' on the server.
$dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
(CONNECT_DATA=(SID=ORCL)))}, "")
(DESCRIPTION = (ADDRESS_LI$dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);
$dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);
Connecting without environment variables or tnsname.ora file
If you use the host=$host;sid=$sid style syntax, for example:
$dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user, $passwd);
then DBD::Oracle will construct a full connection descriptor string for you and Oracle will not need to consult the tnsname.ora file.
If a port number is not specified then the descriptor will try both 1526 and 1521 in that order (e.g., new then old). You can check which port(s) are in use by typing $ORACLE_HOME/bin/lsnrctl stat on the server.
$dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
(CONNECT_DATA=(SID=ORCL)))}, "")
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = s01wolc1.pkobp.pl)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = SLOG) (SERVER = DEDICATED)))
DBI trace
| Level | Description |
|---|---|
| 0 | Trace disabled. |
| 1 | Trace top-level DBI method calls returning with results or errors. |
| 2 | As above, adding tracing of top-level method entry with parameters. |
| 3 | As above, adding some high-level information from the driver and some internal information from the DBI. |
| 4 | As above, adding more detailed information from the driver. This is the first level to trace all the rows being fetched. |
| 5 to 15 | As above but with more and more internal information |
#DBI_TRACE environment handle before you run your Perl program
DBI_TRACE=1=dbitrace.log
export DBI_TRACE
#trace everything that DBI does
use DBI;
# to standard output
DBI->trace(1);
# or save it to a file
DBI->trace( 1, 'my_program.trace' );
#tracing on particular connections or handles
use DBI;
my $dbh = DBI->connect( ... );
$dbh->trace( 1, 'long_dbi_trace.txt' );
my $sth = $dbh->prepare( ... );
$sth->trace( 3, 'short_dbi_handle_trace.txt' );
https://www.easysoft.com/developer/languages/perl/dbi-debugging.html
Output
$sth->fetch(), $sth->fetchrow_*(), and friends all fetch records from a result set.
In Oracle PL/SQL blocks don't normally return result sets.
So calling $sth->fetchrow_array() after running a PL/SQL block won't return any results.
If your using DBMS_OUTPUT.PUT_LINE to output results you will need to use the dbms_output_* functions provided by DBD::Oracle.
my $dbms_output_byte_limit = 1000000;
$dbh->func( $dbms_output_byte_limit, 'dbms_output_enable' );
my $sth = $dbh->prepare($query);
$sth->execute();
my @text = $dbh->func( 'dbms_output_get' );
Example 1
# Enable DBMS_OUTPUT and set the buffer size
$dbh->{RaiseError} = 1;
$dbh->func( 1000000, 'dbms_output_enable' );
# Put text in the buffer . . .
$dbh->func( @text, 'dbms_output_put' );
# . . . and retrieve it later
@text = $dbh->func( 'dbms_output_get' );
Example2
dbh->{RaiseError} = 1;
$sth = $dbh->prepare(q{
DECLARE tmp VARCHAR2(50);
BEGIN
SELECT SYSDATE INTO tmp FROM DUAL;
dbms_output.put_line('The date is '||tmp);
END;
});
$sth->execute;
# retrieve the string
$date_string = $dbh->func( 'dbms_output_get' );
selectrow_array
my @res = $dbh -> selectrow_array('select count(*), min(col), max(col) from tab');
Using bind variables:
#
@res = $dbh -> selectrow_array('select count(*), min(col), max(col) from tab where col like ?', {}, 'b%');
printf "%d %s %s\n", @res;
$sth = $dbh -> prepare('select count(*), min(col) from tab where col > ?');
my @res = $dbh -> selectrow_array($sth, {}, 'f%');
selectall_arrayref
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
This means that the selectall_arrayref method takes either one, two, or any number of arguments. The first two have to be scalars, a string (SQL query) and a reference to a hash (attributes). After that, you can supply a list of values for the placeholders in your query, if any.
my $query2 = qq{ select id, name, salary from employees where employer = ? };
my $employees_loh = $dbh->selectall_arrayref($query2, {Slice => {}}, $employer);
PL/SQL
CREATE OR REPLACE PROCEDURE my_proc
(id IN NUMBER,
uname IN VARCHAR2,
card IN VARCHAR2)
AS BEGIN
INSERT INTO TABLE
VALUES (id, uname, card);
END;
my $sth = $dbh->prepare('call owner.my_proc(?, ?, ?)');
for (1..$iter) {
print "$_\n";
$sth->execute($id, $name, $card);
$id++;
}
$dbh->commit;
my $script = qq(
declare
x number := 1;
begin
insert into xxx values (x);
commit;
end;
);
my $sth = $DBH->prepare($script) or die;
$sth->execute() or die;
$DBH->disconnect();
$dbh->do("begin procedure_call; end;");
Actually, you can access procedure parameters (even in/out ones) and read dbms_output. It is doented in the ```DBD::Oracle`` POD.
$sth->fetch(), $sth->fetchrow_*(), and friends all fetch records from a result set.
In Oracle PL/SQL blocks don't normally return result sets.
So calling $sth->fetchrow_array() after running a PL/SQL block won't return any results.
If your using DBMS_OUTPUT.PUT_LINE to output results you will need to use the dbms_output_* functions provided by DBD::Oracle.
my $dbms_output_byte_limit = 1000000;
$dbh->func( $dbms_output_byte_limit, 'dbms_output_enable' );
my $sth = $dbh->prepare($query);
$sth->execute();
my @text = $dbh->func( 'dbms_output_get' );
Example 1
# Enable DBMS_OUTPUT and set the buffer size
$dbh->{RaiseError} = 1;
$dbh->func( 1000000, 'dbms_output_enable' );
# Put text in the buffer . . .
$dbh->func( @text, 'dbms_output_put' );
# . . . and retrieve it later
@text = $dbh->func( 'dbms_output_get' );
Example2
dbh->{RaiseError} = 1;
$sth = $dbh->prepare(q{
DECLARE tmp VARCHAR2(50);
BEGIN
SELECT SYSDATE INTO tmp FROM DUAL;
dbms_output.put_line('The date is '||tmp);
END;
});
$sth->execute;
# retrieve the string
$date_string = $dbh->func( 'dbms_output_get' );