Re: set_session_longops in perl

From: Tim X <>
Date: Tue, 28 Oct 2008 19:14:02 +1100
Message-ID: <>

Ben <> writes:

> On Oct 25, 12:59 am, Tim X <> wrote:
>> Ben <> writes:
>> > I've posted this in the perl group also, but not really sure which
>> > group would be better to get an answer from. Is it possible to use
>> > dbms_application_info.set_session_longops for in a code loop
>> > of a perl script? I've only seen it used in pl/sql loops and I'm
>> > thinking the values that you use to determine the row in v
>> > $session_longops would pose a problem.
>> Cant't see there would be any problem. You wold need to put the call
>> between BEGIN and END blocks as you do when calling any plsql via perl
>> DBD::Oracle.
>> However, its been a while since I've done any Perl DBI to oracle (in
>> fact, I'm about to do it again to test some interfaces designed for JDBC
>> connections that pass ref cursors back and fort _ I can't stand Java, so
>> I'm going to use perl to write the tests), but the last time I did it, I
>> seem to remember there were optional settings that could be passed to
>> the connect method to set application_info details. Check the
>> DBD::Oracle man page.
>> Tim
>> --
>> tcross (at) rapttech dot com dot au
> I haven't been able to find any references to optional settings for
> application_info.

From the DBD::Oracle POD

           After connecting to the database the value of this attribute is
           passed to the SET_MODULE() function in the "DBMS_APPLICATION_INFO"
           PL/SQL package. This can be used to identify the application to the
           DBA for monitoring and performance tuning purposes. For example:

             DBI->connect($dsn, $user, $passwd, { ora_module_name =>

then you can just call the long_ops procedure. Note that you will need to use the bind_params_inout call rather than the bind_params call as you want to get back the rindex value after the first call in order to use it in subsequent calls. Likewise with the slno arg, but you must not modify this as its used for internal use by dbms_app_info.

rough untested pseudo code would liook like

my $dbh = DBI->connect(......);
my $stmt = qq/BEGIN dbms_application_info(:r, :s, .....); END;/;
my $sth = $dbh->prepare($stmt);
my $rindex =  # set this to the same as  value for
              # set_session_longops_nohint for the first entry/call
my $slno;
my $op_name = ....;
$sth->bind_param_inout(":r", \$rindex, ....); # for IN/OUT params
$sth->bind_param-inout(":s", \$slno, .....);
$sth->bind_param(....) # for IN params



something like that should work. Essentially, you are just sending a plsql block - the only trick is getting the initial value for rindex and ensuring you pass the value that slno is set to after the first call to all other calls during that 'long op'.


tcross (at) rapttech dot com dot au
Received on Tue Oct 28 2008 - 03:14:02 CDT

Original text of this message