Here is a simple (aka crude) example :
#!/opt/freestuff/bin/perl
use DBI ;
#
# Connect to Oracle
#
my $dbh = DBI -> connect ("dbi:Oracle:", $ENV {ORAPWD}) ;
$csr_open = $dbh -> prepare (q (
BEGIN
perl_global.fd := utl_file.fopen ('/tmp', 'a.a', 'r') ;
END ;
)) ;
$csr_open -> execute ;
$csr_read = $dbh -> prepare (q (
BEGIN
utl_file.get_line (perl_global.fd, :line) ;
:more := 'Y' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:more := 'N' ;
END ;
)) ;
#
$Line = "" ;
$More = "Y" ;
$csr_read -> bind_param_inout (":line", \$Line, 256) ;
$csr_read -> bind_param_inout (":more", \$More, 5) ;
while ($More eq "Y") {
$csr_read -> execute ;
last if $More eq "N" ;
print $Line . "\n" ;
}
There are probably lots of better ways of doing this, but Oracle is my
forte, not Perl. Note you will need to create a small PL/SQL package to
create a persistent PL/SQL variable (the file handle). Here is the one I
used with the above example :
create or replace package perl_global as
fd utl_file.file_type ;
end perl_global ;
/
If Perl handles PL/SQL cursor variables (perhaps someone could confirm or
deny this) and you are using Oracle 8.1 or greater, there is a much tidier
way of doing it - so if someone shows me how to use cursor vars, I'll show
you a better solution :). You will need to get your DBA to allow access to
the directory you want to read from. This is done by adding an entry into
the initxxx.ora file. Without this you will not be able to open any files
in the directory.
The above example should of course be made a lot more robust, and tidy up
after itself. I'm sure you know how to do all that.
Having said all this, it will work, but for large files, it'll probably be
pretty slow.
Hope this helps,
Steve
-----Original Message-----
From: Mark Wagner [mailto:[EMAIL PROTECTED]]
Sent: Friday, 23 March 2001 8:47 AM
To: Steven Baldwin
Cc: [EMAIL PROTECTED]
Subject: Re: Reading a trace file through Oracle? POSSIBLE??
Can that be done through dbi?
if so, would you mind giving me an example??
Thanks
Mark
On Fri, Mar 23, 2001 at 07:58:45AM +1100, Steven Baldwin wrote:
> Mark,
>
> If you use the PL/SQL utl_file package, this runs on the server,
regardless
> of where it is executed from, so can read the trace file. Furthermore, it
> is executed as an 'oracle' owned process, so has the appropriate
> permissions.
>
> Let me know if you need any more help.
>
> Cheers,
>
> Steve
>
> -----Original Message-----
> From: Mark Wagner [mailto:[EMAIL PROTECTED]]
> Sent: Friday, 23 March 2001 4:21 AM
> To: [EMAIL PROTECTED]
> Subject: Reading a trace file through Oracle? POSSIBLE??
>
>
> I am working on making a tool with perl DBI/DBD Oracle to document Oracle
> Databases... One of the things I want to do is back the control file
> up to the trace file then read the trace file.
>
> But heres the *hard* part:
> I am not running this locally so using sql*net to connect to Oracle with
> perl DBI/DBD, I need to make Oracle read the trace file in a way that
> I can have it accessible by perl to print out.
>
>
> Any help would be greatly appreciated.
>
> Thanks
>
> Mark
> --
> --------------------------------------------------------------------
> Mark Wagner Phone - (510) 683-9681
> Database Administrator [EMAIL PROTECTED]
> * www.oralinux.org www.orasolaris.org www.orant.org *
> * Dark Oracle of the Sysadmin Sith Darth SYSDBA *
> --------------------------------------------------------------------
> the shit ain't over till the last record spins.......
--
--------------------------------------------------------------------
Mark Wagner Phone - (510) 683-9681
Database Administrator [EMAIL PROTECTED]
* www.oralinux.org www.orasolaris.org www.orant.org *
* Dark Oracle of the Sysadmin Sith Darth SYSDBA *
--------------------------------------------------------------------
the shit ain't over till the last record spins.......
