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

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:
% 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
 
H

Heinrich Mislik

#======================================
# 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
 
R

RU

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,906
Latest member
SkinfixSkintag

Latest Threads

Top