R
RU
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:
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_evsid, :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
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_evsid, :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