Accessing executing SQL with Perl DBI & Oracle?

LameNerd has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am trying to write a Perl script that will query the
V$SESSION table in a Oracle database. Right now all this
script does is print out the SQL_ADDRESS column of the
V$SESSION table. I was hoping that this address pointed to
the SQL being executed by the session, but I don't know how
to proceed from here.

Here's some psuedo code for what I am trying to do:

...
sub sqlAddress2sqlStatement {
    my $sql_address_string = shift;
    ...
    # do so magic the perlmonks told me about
    ...
    return $sql_statement;
}

my $sql = "select sql_address, osuser from v\$session where user = ?";
...
while( row = $sth->fetchrow_array ) {
      my $thesql = sub sqlAddress2sqlStatement ( $row[0] );
      print "$user is executing $thesql\n";
}
[download]

Maybe I am barking up the wrong tree?

Comment on Accessing executing SQL with Perl DBI & Oracle? Download Code

Replies are listed 'Best First'.
Re: Accessing executing SQL with Perl DBI & Oracle? by LameNerd (Hermit) on May 08, 2003 at 18:09 UTC
Doh!! It appears that my SQL is wrong. All I needed to do was change my SQL to this ... `select distinct o.sql_text "SQL text" ,s.username "User name" ,s.schemaname "Schema name" ,s.lockwait "Lock wait" ,s.status "Status" ,s.sid "Session ID" ,s.serial# "Serial N" ,s.server "Server" ,s.osuser "OS user" ,s.machine "Machine" ,s.terminal "Terminal" ,s.program "Program" from sys.v_$session s ,sys.v_$sql o where s.username is not null and s.sql_address=o.address(+)` [download] which I found in this link Ain't the internet great!	[reply] [d/l]
Re: Accessing executing SQL with Perl DBI & Oracle? by talexb (Chancellor) on May 08, 2003 at 18:08 UTC
Try looking at perldoc DBI and see if that answers your question. Then develop the query you want to run from a command line prompt into the database, and that should be enough to get you going. --t. alex Life is short: get busy!	[reply]