Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > DBD::Oracle, tracing, and stored procedures...

Reply
Thread Tools

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

 
 
RU
Guest
Posts: n/a
 
      05-09-2007
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
 
Reply With Quote
 
 
 
 
Heinrich Mislik
Guest
Posts: n/a
 
      05-11-2007
In article <(E-Mail Removed)>, http://www.velocityreviews.com/forums/(E-Mail Removed) 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

 
Reply With Quote
 
 
 
 
RU
Guest
Posts: n/a
 
      05-12-2007
On Fri, 11 May 2007 08:22:23 +0000, Heinrich Mislik wrote:

> In article <(E-Mail Removed)>, (E-Mail Removed)
> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hi I have one web application and i want to get the number of users who are currently accessing the application. Also I want to get the user details of each user, which is stored in a database. How can I do this? Pls help. Getting No: and anu Java 11 05-12-2005 03:25 PM
Switching from Mozilla to FireFox without loosing my stored cookies and passwords Daniel Prince Firefox 1 12-11-2004 11:03 PM
DDL values with DataReader and stored procedures Rob Wire ASP .Net 5 08-14-2003 09:50 AM
How to use parameters(record selection) and stored procedure in CR.NET web-based application? TaeHo Yoo ASP .Net 0 08-13-2003 11:13 PM
Stored Procedure has both return value and data set (SqlDataReader) Neo Chou ASP .Net 1 08-03-2003 06:23 AM



Advertisments