Re: set_session_longops in perl

From: Ben <benalvey_at_yahoo.com>
Date: Thu, 30 Oct 2008 07:54:27 -0700 (PDT)
Message-ID: <e63f1dbd-d36b-4840-8648-7ff8606867cb@y79g2000hsa.googlegroups.com>


On Oct 28, 4:14 am, Tim X <t..._at_nospam.dev.null> wrote:
> Ben <benal..._at_yahoo.com> writes:
> > On Oct 25, 12:59 am, Tim X <t..._at_nospam.dev.null> wrote:
> >> Ben <benal..._at_yahoo.com> 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 10.2.0.2 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
>
>        ora_module_name
>            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 =>
>              $0});
>
> 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
> ....
>
> $sth->execute;
>
> 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'.
>
> HTH
>
> Tim
>
> --
> tcross (at) rapttech dot com dot au

Very Helpful! I must just suck at searching for this stuff. I looked on CPAN, google, the server docs, etc and couldn't find this. Thanks. Received on Thu Oct 30 2008 - 09:54:27 CDT

Original text of this message