DBD::Oracle, tracing, and stored procedures...

Discussion in 'Perl Misc' started by RU, May 9, 2007.

  1. RU

    RU Guest

    Hi Perlers,

    I'm trying to turn on tracing for a DB session using the oracle event 10046
    by calling the stored procedure "sys.dbms_system.set_ev" (SCHEMA "sys",
    PACKAGE "dbms_system", PROCEDURE "set_ev") using the DBI so far without
    success.

    Using sqlplus it's trivial:
    > % sqlplus
    > SQL> execute sys.dbms_system.set_ev(46, 53017, 10046, 4, ' ');
    >
    > PL/SQL procedure successfully completed.


    now tracing is enabled.


    I have tried all possible permutations that I can think of:

    assuming:
    I have a db-handle in $DBH,
    $sess_id = 32
    $serial = 53017;

    this session exists, and my db-user had the necessary privs.


    #======================================
    # permutation 1:
    #======================================

    CODE>

    my $sql = qq{
    BEGIN
    sys.dbms_system.set_ev(46, 53017, 10046, 4, '');
    END;
    };
    $sql = $DBH->quote($sql);
    my $rv = $DBH->do($sql);

    RESULT>

    DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "'
    BEGIN
    sys.dbms_system.set_ev(46, 53017, 10046, 4, '''');
    END;
    '"] at ./trace.pl line 72.


    #======================================
    # permutation 2:
    #======================================

    CODE>

    my $sql = qq{
    BEGIN
    sys.dbms_system.set_ev:)sid, :ser, :event, :level, :blank);
    END;
    };
    my $sth = $DBH->prepare($sql);
    $sth->bind_param(':sid', $sess_id);
    $sth->bind_param(':ser', $serial);
    $sth->bind_param(':event', $event);
    $sth->bind_param(':level', $level);
    $sth->bind_param(':blank', $blank);

    my $rv = $sth->execute;

    RESULT>

    no error, but tracing is not enabled for the session corresponding to
    SID/SERIAL#.

    #======================================
    # permutation 3:
    #======================================

    CODE>

    my $sql = qq{
    BEGIN
    sys.dbms_system.set_ev(?, ?, ?, ?, ?);
    END;
    };
    my $sth = $DBH->prepare($sql);
    my $event = 10046;
    my $blank = '';
    $sth->bind_param(1, $sess_id);
    $sth->bind_param(2, $serial);
    $sth->bind_param(3, $event);
    $sth->bind_param(4, $level);
    $sth->bind_param(5, $blank);

    my $rv = $sth->execute;

    RESULT>

    no error, but tracing is not enabled for the session corresponding to
    SID/SERIAL#.

    =========================================================================

    anyone have an idea how to get this working???

    thanks,

    RU
     
    RU, May 9, 2007
    #1
    1. Advertising

  2. In article <>, says...
    >
    >#======================================
    ># permutation 1:
    >#======================================
    >
    >CODE>
    >
    >my $sql = qq{
    > BEGIN
    > sys.dbms_system.set_ev(46, 53017, 10046, 4, '');
    > END;
    >};
    >$sql = $DBH->quote($sql);


    This doesn't do any good. $DBH->quote returns one single string constant in
    SQL syntax. Look closely at the error. Your statement starts with 'BEGIN ... instead of plain BEGIN ...

    >my $rv = $DBH->do($sql);
    >
    >RESULT>
    >
    >DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD ERROR:
    >OCIStmtExecute) [for Statement "'
    > BEGIN
    > sys.dbms_system.set_ev(46, 53017, 10046, 4, '''');
    > END;
    > '"] at ./trace.pl line 72.


    Can't see any error in both the other permutations. I have called procedures
    this way and it worked.

    Cheers

    Heinrich

    --
    Heinrich Mislik
    Zentraler Informatikdienst der Universitaet Wien
    A-1010 Wien, Universitaetsstrasse 7
    Tel.: (+43 1) 4277-14056, Fax: (+43 1) 4277-9140
     
    Heinrich Mislik, May 11, 2007
    #2
    1. Advertising

  3. RU

    RU Guest

    On Fri, 11 May 2007 08:22:23 +0000, Heinrich Mislik wrote:

    > In article <>,
    > says...
    >>
    >>#====================================== # permutation 1:
    >>#======================================
    >>
    >>CODE>
    >>
    >>my $sql = qq{
    >> BEGIN
    >> sys.dbms_system.set_ev(46, 53017, 10046, 4, ''); END;
    >>};
    >>$sql = $DBH->quote($sql);

    >
    > This doesn't do any good. $DBH->quote returns one single string constant
    > in SQL syntax. Look closely at the error. Your statement starts with
    > 'BEGIN ... instead of plain BEGIN ...


    indeed, as I pointed out in an email, I was on drugs when I did this
    (well, really I just didn't read the DBI manpage very carefully...). If I
    stop quoting $sql it works...

    thanks Heinrich.

    RU
     
    RU, May 12, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ricardo Magalhaes
    Replies:
    4
    Views:
    13,566
    Ricardo Magalhaes
    Jun 21, 2005
  2. Curtis
    Replies:
    4
    Views:
    14,832
    steve
    Jun 26, 2007
  3. Feyruz
    Replies:
    4
    Views:
    2,243
    Sherm Pendley
    Oct 14, 2005
  4. Replies:
    0
    Views:
    119
  5. Jim Cochrane
    Replies:
    0
    Views:
    124
    Jim Cochrane
    Aug 25, 2007
Loading...

Share This Page