SQL-Statement problem
Dear all i am having a problem with SQL-Statement-1.005, whereas SQL-Statement-0.1020 is working fine. SQL ERROR: Mismatched parentheses in WHERE clause! SV = RV(0x810cad0) at 0x829105c REFCNT = 1 FLAGS = (PADBUSY,PADMY,ROK) RV = 0x83844e4 DBD::CSV::st execute failed: dbih_getcom handle 'DBD::CSV::Statement=HASH(0x83844e4)' is not a DBI handle (has no magic) at /usr/local/lib/perl5/site_perl/5.6.0/SQL/Statement.pm line 164. I think i tracked this down to Parser.pm the cleansql routine which skips closing parens, but not the corresponding openings: warn Before $sql; $sql =~ s~'(([^'$e]|$e.|'')+)'~push(@$fields,$1);$i++;?$i?~ge; warn After $sql; Before SELECT KOLOM1,KOLOM8,KOLOM5,KOLOM6,KOLOM7 FROM MYTABLE WHERE ( ( ( ( KOLOM4 = '123456' OR KOLOM4 = '' ) AND KOLOM3 = '127' ) OR KOLOM8 = 'TRUE' ) AND KOLOM1 = 'bla' ) at /usr/local/lib/perl5/site_perl/5.6.0/SQL/Parser.pm line 1679. After SELECT KOLOM1,KOLOM8,KOLOM5,KOLOM6,KOLOM7 FROM MYTABLE WHERE ( ( ( ( KOLOM4 = ?0? OR KOLOM4 = '?1?127?2?TRUE?3?bla' ) at /usr/local/lib/perl5/site_perl/5.6.0/SQL/Parser.pm line 1681. The (not ideal)query is generated from another application, which is not easy to change, so that is not an option Switching to SQL-Statement-0.1020 solves this, but introduces another problem, (there i cannot use the SELECT KOLOM1, NULL, KOLOM3 statement) Any suggestions ? Thanks Walter Takens
selectrow_hashref in DBI 1.32 doesn't always return a hashref
this seems to have changed since i installed DBI 1.32 (i upgraded from 1.30),
and is breaking some scripts which assume that it will always return a hash.
sometimes (when the query returns no rows), it returns the scalar 1 -
attempting to use that as a hashref causes the script to die (with strict
refs set - would cause strange errors without it)
the problem seems to be in _do_selectrow() in DBI.pm, which is also used
by selectrow_arrayref.
as a temporary workaround fix, i hacked _do_selectrow() so that it
always returns the appropriate reference.
like so:
sub _do_selectrow {
my ($method, $dbh, $stmt, $attr, @bind) = @_;
my $sth = ((ref $stmt) ? $stmt : $dbh-prepare($stmt, $attr))
or return;
$sth-execute(@bind)
or return;
my $row = $sth-$method()
and $sth-finish;
# temporary workaround fix. CAS 2003-02-14
if ($method =~ /ref/ ! ref($row)) {
warn * _do_selectrow workaround;
$row = [] if ($method =~ /array/);
$row = {} if ($method =~ /hash/);
} ;
return $row;
}
i don't know the DBI code well enough to know whether this will have
other undesirable consequences, but it stops my scripts from dying until
a real fix is available.
craig
--
craig sanders [EMAIL PROTECTED]
Fabricati Diem, PVNC.
-- motto of the Ankh-Morpork City Watch
Memo: RE: Problem installing DBI 1.32 on AIX 4.3.3.0 with Perl 5.005_03 - Any other suggestions??
Mark, The solution you suggested worked fine for my install of DBI, however I then encountered the same error when building the DBD::DB2 module. Unfotunately, the DBAs are not happy about putting the DB2 Application Development Client (a pre-req to the install) on the box I used last time to generate an uncorrupted version of the DBI.so file. I tried the install on a separate 'standard build' box that they suggested, but this also failed with the same DynaLoader error. So I am now stuck having to attempt to get to the bottom of this problem as to why the DBI.so file gets corrupted, yet I've really not got a lot of time to devote to this at the moment. Just wondering if anyone else can help or if you have any more details that may help point me in the right direction. Thanks, Kathryn ** HSBC's website is at www.hsbc.com ** This E-mail is confidential. It may also be legally privileged. If you are not the addressee you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return E-mail. Internet communications cannot be guaranteed to be timely, secure, error or virus-free. The sender does not accept liability for any errors or omissions.
Re: selectrow_hashref in DBI 1.32 doesn't always return a hashref
Can you post an example script that calls selectrow_hashref and
demonstrates the problem?
Tim.
On Fri, Feb 14, 2003 at 01:18:56PM +1100, Craig Sanders wrote:
this seems to have changed since i installed DBI 1.32 (i upgraded from 1.30),
and is breaking some scripts which assume that it will always return a hash.
sometimes (when the query returns no rows), it returns the scalar 1 -
attempting to use that as a hashref causes the script to die (with strict
refs set - would cause strange errors without it)
the problem seems to be in _do_selectrow() in DBI.pm, which is also used
by selectrow_arrayref.
as a temporary workaround fix, i hacked _do_selectrow() so that it
always returns the appropriate reference.
like so:
sub _do_selectrow {
my ($method, $dbh, $stmt, $attr, @bind) = @_;
my $sth = ((ref $stmt) ? $stmt : $dbh-prepare($stmt, $attr))
or return;
$sth-execute(@bind)
or return;
my $row = $sth-$method()
and $sth-finish;
# temporary workaround fix. CAS 2003-02-14
if ($method =~ /ref/ ! ref($row)) {
warn * _do_selectrow workaround;
$row = [] if ($method =~ /array/);
$row = {} if ($method =~ /hash/);
} ;
return $row;
}
i don't know the DBI code well enough to know whether this will have
other undesirable consequences, but it stops my scripts from dying until
a real fix is available.
craig
--
craig sanders [EMAIL PROTECTED]
Fabricati Diem, PVNC.
-- motto of the Ankh-Morpork City Watch
UDB/DB2
Does anybody know where IBM publish sample Perl code for their UDB/DB2 (dbh) structures. I am using Solaris; Linus and AIX. Thanks _ Get Free Web email Account from the MetroCentre --- http://www.metro-centre.co.uk _ Select your own custom email address for FREE! Get [EMAIL PROTECTED] w/No Ads, 6MB, POP more! http://www.everyone.net/selectmail?campaign=tag
Réf. : UDB/DB2
I did not see a lot of sample script regarding perl and DB2 but if you want tell me what kind of example you are looking for and I will sent you a couple of script I've made. I use DB2 and Perl on a regulary basis. Stéphane Legault, ing. Services Mondiaux IBM IBM Canada Ltée Architecte, SEDQ/Technologies 1 Place Alexis Nihon 3400 Boul. de Maisonneuve ouest 10e étage Montréal, Qc H3Z 3K9 Tel. : (514) 205-6724 Fax. : (514) 205-6550 Email : [EMAIL PROTECTED] Caroline Inchcombe - Pour :[EMAIL PROTECTED] Harvard cc : cinch@metro-centObjet : UDB/DB2 re.co.uk 14/02/2003 08:40 AM Veuillez répondre à cinch Does anybody know where IBM publish sample Perl code for their UDB/DB2 (dbh) structures. I am using Solaris; Linus and AIX. Thanks _ Get Free Web email Account from the MetroCentre --- http://www.metro-centre.co.uk _ Select your own custom email address for FREE! Get [EMAIL PROTECTED] w/No Ads, 6MB, POP more! http://www.everyone.net/selectmail?campaign=tag
Why doesn't Postgres connections for the PerlCGI users close down?
Hi,
I still have the default max number of database connections (32), but
instead of increasing it I want the connections to be closed down
after being used. I get an increasing number of these:
postgres 22906 695 0 13:40 ? 00:00:00 postgres: apache testdb [local]
postgres 22913 695 0 13:41 ? 00:00:00 postgres: apache testdb [local]
postgres 22926 695 0 13:47 ? 00:00:00 postgres: apache testdb [local]
postgres 22933 695 0 13:59 ? 00:00:00 postgres: apache testdb [local]
And I'm using Perl dbi:Pg (as well as DBIx::Recordset) in a CGI
script, and in the end I try to close the connection:
$db = DBIx::Database-new({'!DataSource' = 'dbi:Pg:dbname=testdb',
'!Username' = 'apache',
'!KeepOpen' = 1}) or die Couldn't connect to
database;
...
*set = DBIx::Recordset-Search({%fdat,('!DataSource' = $db,
'!Table' = $table,
'!Fields' = $joined_col,
'$where' = $query)}) or die
Couldn't connect to table :$DBI::errstr ;
...
$set - Disconnect ();
Although, this doesn't close any connection and they accumulate in a
way I don't want them to.
If I set '!KeepOpen' to 0 I can't run the script since $db isn't
recognized later on.
Really gratefull for any help!
Regards,
Marcus
Re: DBD:Oracle holding on to cursors ?
Holding cursors open is a feature. Really. :) Keeping cursors open for reuse can save a lot of time. Just because you close the cursor in the your program does not mean Oracle will close it also. Have your DBA's session_cached_cursors. This is the number of cursors that will be cached in the PGA of that session. Easy to test. In one sqlplus session, run a PL/SQL block that opens, fetches from and closes a couple of cursors. First: alter session set session_cached_cursors=0; run the PL/SQL Then: alter session set session_cached_cursors=5; run the PL/SQL all the while monitoring v$open_cursor from another session. For further discusstion, take it to an Oracle list. HTH. Jared On Tuesday 11 February 2003 00:29, [EMAIL PROTECTED] wrote: I explicitly finish() and undef all statement handlers, yes. Those handlers are in used in small subroutines and as such should be destroy when returning from those anyway. AutoCommit is on. DBA-ers over here can't find much wrong either, it does appear that the $dbh-do() command uses one cursor which it leaves open (v$open_cursor). We'll rewrite such commands to use our own function which will finish/undef the handler it uses. --- Frans Postma, (050-58) 81 852 ATOS Origin, Unix Support If at first you don't succeed, skydiving isn't for you -Oorspronkelijk bericht- Van: Ian Harisay [mailto:[EMAIL PROTECTED]] Verzonden: maandag 10 februari 2003 18:35 Aan: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Onderwerp: Re: DBD:Oracle holding on to cursors ? Are you explicitly closing your statement handles and explicitly committing? If you are running endlessly and not committing it will lock your tables until that commit happens. There is more to it than just this. The other things to cover are on the DBA side. [EMAIL PROTECTED] wrote: Hi, I have a script which runs in non-stop in the background on a HPUX system. It checks various table in my database and updates some rows when needed. It connects to the database (Oracle 8.1.7.2.0) once at startup and then execute a never ending loop with the update statements, sleep(15) and loop.. So far so good. However, we've noticed that somehow tables remain locked due to this script running non-stop. We did NOT have this problem when the script was run every minute from a crontab. Thats not desirable due to slow connects. Does anyone have experience with using DBD:Oracle in a never-exiting process/script ? It appears to be a cleanup problem of some sort but I'm at a loss as to WHERE the problem is located. I finish and undef every statement-handler I use, most of which are my into a sub-routine and thus destroy on returns anyway. Locks and problems in the database DO dissappear when we stop and re-start the script so it's definitly related to it. Suggestions ??? --- Frans Postma, (050-58) 81 852 ATOS Origin, Unix Support If at first you don't succeed, skydiving isn't for you
Re: Oracle connection problems
Try an Oracle list. There's instruction for joining one at: http://www.cybcon.com/~jkstill/util/maillist/maillist.html Jared On Thursday 13 February 2003 06:36, Morrison Davis wrote: This is probably more of an oracle problem than DBI problem but I'm getting desperate. I can connect from any machine thats in the same domain as my server but not from any machine outside that domain. I get the ora-12545 error, target host does not exist. Listener is running and I can see entries in the listener log file from these other machines but they never connect succesfully. I'm suspicious of one thing, when I see the host names trying to access in the log file it does n't include their domain name I do see their IP address so I'm not sure. I have read the perldoc on DBD::Oracle and tried all different connection methods but I get the same error tryin to use sqlplus too. Any advice would be much appreciated, my project is topped until I can figure this out.
Re: Given a dbh, is it possible to find out if there are uncommited transactions?
In DBD::Informix, there is an attribute for $dbh for ix_InTransaction which does this for you. It is not standardized. ---Original Message--- From: Roger Perttu [EMAIL PROTECTED] When I destroy my objects I would like to know if there are uncommitted transactions. There shouldn't be any but I would like to know. Given only a DBI database handle, is it possible to find out if there are uncommitted transactions? If not, is there anything inside DBD-ODBC that I could use? I didn't find anything in the docs.
Re: Given a dbh, is it possible to find out if there are uncommited transactions?
Roger Perttu wrote:
Hi!
When I destroy my objects I would like to know if there are
uncommitted transactions. There shouldn't be any but I would like to
know. Given only a DBI database handle, is it possible to find out if
there are uncommitted transactions? If not, is there anything inside
DBD-ODBC that I could use? I didn't find anything in the docs.
Thanks!
Roger P
I've been looking at the source but didn't find much.
From DBI.pm:
sub begin_work {
my $dbh = shift;
return $dbh-DBI::set_err(1, Already in a transaction)
unless $dbh-FETCH('AutoCommit');
$dbh-STORE('AutoCommit', 0); # will croak if driver doesn't support it
$dbh-STORE('BegunWork', 1); # trigger post commit/rollback action
}
From the implementation of DBD-ODBC:
/* maybe bad way to add hint about invalid transaction
* state upon disconnect...
*/
if (what !strcmp(sqlstate, 25000) !strcmp(what,
db_disconnect/SQLDisconnect)) {
sv_catpv(errstr, You need to commit before disconnecting! );
}
I assume the functionality I'm looking for doesn't exist.
/Roger P
ActiveState Perl 5.8.0 Build 805 PPM and DBD-DB2 module
Has anyone installed ActiveState Perl 5.8.0 Build 805? I did on a test machine, and installed the DBI package with the PPM3 interface. It could not locate the DBD-DB2 package. When I added the 'repository' for DBD-DB2, PPM refused to install it for binary incompatibility. I've been using these modules under 5.6.1 Build 633 (635 has similar install issues with DBD-DB2). I can't find any notes on why it changed. Anyone know why/how? How do I install DBD-DB2 in this new version of perl? Thanks, Ed The information in this e-mail, and any attachment therein, is confidential and for use by the addressee only. If you are not the intended recipient, please return the e-mail to the sender and delete it from your computer. Although The Bank of New York attempts to sweep e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses.
warnings reletive to my $sql content
perl 5.6.1
With use warnings, dependant upon the query I execute, I will recive the
following: Use of uninitialized value in join or string. I see this when
I am selecting against v$ views and not when I use a table/view
without a $ in the name. I'm assuming at this point that perl is parsing $
references as variables within the my $sql=q{ }; block that I create. Whats
the a method of working around this as opposed to turning off warnings?
RE: warnings reletive to my $sql content
Try changing all of your $ that are not variables to \$
perl 5.6.1
With use warnings, dependant upon the query I execute, I will
recive the
following: Use of uninitialized value in join or string.
I see this when I am selecting against v$ views and not when
I use a table/view
without a $ in the name. I'm assuming at this point that perl
is parsing $ references as variables within the my $sql=q{ };
block that I create. Whats the a method of working around
this as opposed to turning off warnings?
Re: warnings reletive to my $sql content
On Fri, Feb 14, 2003 at 01:31:00PM -0500, Markham, Richard wrote:
perl 5.6.1
With use warnings, dependant upon the query I execute, I will recive the
following: Use of uninitialized value in join or string. I see this when
I am selecting against v$ views and not when I use a table/view
without a $ in the name. I'm assuming at this point that perl is parsing $
references as variables within the my $sql=q{ }; block that I create. Whats
the a method of working around this as opposed to turning off warnings?
If you are really using a single-quoted string (e.g. q{ }) then perl will
not interpolate variables. Please show a sample of code that produces this
warning.
Ronald
RE: warnings reletive to my $sql content
Thanks ronald for your assistance
~~snip~~
$dbh-{RowCacheSize} = 100;
my $sql=q{ select * from v$instance };
print Preparing SQL\n;
my $sth = $dbh-prepare($sql);
print Executing SQL\n;
$sth-execute;
while (my $ary = $sth-fetchrow_arrayref) {
print @{$ary}\n;
}
$sth-finish;
$dbh-disconnect;
~~snip~~
~output~
Preparing SQL
Executing SQL
Use of uninitialized value in join or string at C:\DOCUME~1\edp18\un.pl line
70.
1 DEV erpd3 8.1.7.2.0 09-FEB-03 OPEN NO 1 STOPPED ALLOWED NO ACTIVE
PRIMARY_INSTANCE
line 70 in notepad is print @{$ary}\n;
-Original Message-
From: Ronald J Kimball [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 1:41 PM
To: Markham, Richard
Cc: '[EMAIL PROTECTED]'
Subject: Re: warnings reletive to my $sql content
On Fri, Feb 14, 2003 at 01:31:00PM -0500, Markham, Richard wrote:
perl 5.6.1
With use warnings, dependant upon the query I execute, I will recive
the
following: Use of uninitialized value in join or string. I see this
when
I am selecting against v$ views and not when I use a table/view
without a $ in the name. I'm assuming at this point that perl is parsing $
references as variables within the my $sql=q{ }; block that I create.
Whats
the a method of working around this as opposed to turning off warnings?
If you are really using a single-quoted string (e.g. q{ }) then perl will
not interpolate variables. Please show a sample of code that produces this
warning.
Ronald
Re: warnings reletive to my $sql content
On Fri, Feb 14, 2003 at 01:50:05PM -0500, Markham, Richard wrote:
while (my $ary = $sth-fetchrow_arrayref) {
print @{$ary}\n;
}
Use of uninitialized value in join or string at C:\DOCUME~1\edp18\un.pl line
70.
1 DEV erpd3 8.1.7.2.0 09-FEB-03 OPEN NO 1 STOPPED ALLOWED NO ACTIVE
PRIMARY_INSTANCE
line 70 in notepad is print @{$ary}\n;
Okay, you are getting a warning because the row returned from the database
includes a NULL value. You could just add no warnings 'uninitialized';
inside the while loop to turn off uninitialized warnings for that small
part of the script.
Another approach, if you want the NULL values to be obvious in the output,
is to use map to change the undefined values to something else. For
example:
print join(' ', map { defined $_ ? $_ : 'empty' } @{$ary}), \n;
HTH,
Ronald
Re: ActiveState Perl 5.8.0 Build 805 PPM and DBD-DB2 module
Hi Ed, I had a similar problem with the Sybase module, and I went back to perl 5.6.1. The Sybase module says it needs perl 5.6 libraries (somehow) so it fails. Until someone compiles a 5.8 specific version (not me, don't have the time!) you'll have to use ODBC, or use 5.6. (This is in XP BTW) Regards Phil Has anyone installed ActiveState Perl 5.8.0 Build 805? I did on a test machine, and installed the DBI package with the PPM3 interface. It could not locate the DBD-DB2 package. When I added the 'repository' for DBD-DB2, PPM refused to install it for binary incompatibility. I've been using these modules under 5.6.1 Build 633 (635 has similar install issues with DBD-DB2). I can't find any notes on why it changed. Anyone know why/how? How do I install DBD-DB2 in this new version of perl? Thanks, Ed --- Direct: +44 (0) 20 7325 1653GDP:325-1653 Mobile: +44 (0) 77 4876 0299Home: +44 (0) 1444 891365 RTM:800 625 1275 or + 1(1) 334 420 2916 Code: 435994
Re: SQL-Statement problem
Walter B. Takens wrote: Dear all i am having a problem with SQL-Statement-1.005, whereas SQL-Statement-0.1020 is working fine. SELECT KOLOM1,KOLOM8,KOLOM5,KOLOM6,KOLOM7 FROM MYTABLE WHERE ( ( ( ( KOLOM4 = '123456' OR KOLOM4 = '' ) AND KOLOM3 = '127' ) OR KOLOM8 = 'TRUE' ) AND KOLOM1 = 'bla' ) Yes, sorry, there are two bugs in SQL::Statement that are biting you there, I am working on both. The first is the = '' construction -- the parser gets confused by that. The second is the unecessary (but quite valid) enclosing of the WHERE clause in outer parens. Sorry, there is no quick fix, I'll try for a new release as soon as I can. -- Jeff
Re: SQL-Statement problem
Dan Muey wrote:
Before
SELECT KOLOM1,KOLOM8,KOLOM5,KOLOM6,KOLOM7 FROM MYTABLE
WHERE ( ( ( ( KOLOM4 = '123456' OR KOLOM4 = '' ) AND KOLOM3
= '127' )
OR KOLOM8 = 'TRUE' ) AND KOLOM1 = 'bla' )
Does this query work from the command line app?
It seems to me it wouldn't becaue you have 4 ('s and 3 )'s
I count four and four. The problem is on my end, not on Walter's.
--
Jeff
DBI/DBD Oracle dumps core on Solaris 8 when reference cursor returned untouched
Hi,
I need a bit of a help with DBI/DBD combo. I have tried to narrow it
down and came up with the following.
Here is the problem summary:
Versions:
Perl: 5.6.1
DBI: 1.32
DBD-Oracle: 1.12
Operating System: Solaris 5.8
Oracle version: 8.1.7
Description:
When DBI/DBD executes a PL/SQL stored procedure which
returns a reference cursor, and that cursor has been touched
by the procedure, the whole works fine. However, if the
cursor was untouched, for example if there is a piece of code
that raises an exception before the cursor was used in the
procedure, the program dumps core. The error message is:
OCIErrorGet after OCIAttrGet OCI_ATTR_PARAM_COUNT (er1:ok): -1, 24338:
ORA-24338: statement handle not executed
!! ERROR: 24338 'ORA-24338: statement handle not executed (DBD ERROR:
OCIAttrGet OCI_ATTR_PARAM_COUNT)'
DBD::Oracle::st execute failed: ORA-24338: statement handle not executed
(DBD ERROR: OCIAttrGet OCI_ATTR_PARAM_COUNT) at ./bug.pl line 40.
Note that the execution is successful also if the cursor
statement did not have any rows returned. The program fails
ONLY if the cursor is untouched.
Attachments:
bug.sql - creates the example tables and packages (run 'sqlplus
user/password @bug.sql' to install).
bug.pl - the sample perl script (use './bug.pl MR' for success
or './bug.pl GG' to get it to dump core).
DBImake.log(*) - verbose log of the DBI-1.32 make and
installation
DBDmake.log(*) - verbose log of the DBD-1.12-Oracle make and
installation
success.log - verbose log of a successful execution of bug.pl
failure.log - verbose log of a failed execution (with core dump)
Unfortunately I was unable to recompile everything to
convince gdb backtrace to produce any sane output. All I have
is number of hex pointers... Sorry.
(*) Due to list post size limit requirements I can't attach
these, but will be happy to e-mail if needed.
The complicating factor is that I am not allowed to modify
the stored procedure code to ensure the cursor gets touched
every time. That would be the most obvious solution. The
reason for this is that the perl code we are executing is
used to performance test some 15 complex functions in a
package, so any modification would cause lengthy regression testing.
I have seen only one reference to this issue on the Net (and
that appears to have been unanswered). I would appreciate any
feedback from developers.
Thanks and regards,
Srdjan Nikolic
Senior Performance Analyst
Telstra Corporation
== bug.sql
==
DROP TABLE DBI_BUG;
CREATE TABLE DBI_BUG (
COL1 VARCHAR2 (10),
COL2 VARCHAR2 (10),
COL3 VARCHAR2 (10));
INSERT INTO DBI_BUG VALUES ('MR','WILLIAM','RIKER');
INSERT INTO DBI_BUG VALUES ('MR','JEAN-LUC','PICARD');
INSERT INTO DBI_BUG VALUES ('MS','DEANNA','TROI');
INSERT INTO DBI_BUG VALUES ('MISS','BEVERLY','CRUSHER');
CREATE OR REPLACE PACKAGE BUG_PACKAGE AS
TYPE T_CURSOR IS REF CURSOR;
FUNCTION BUG_FUNCTION (
IN_COL1 IN DBI_BUG.COL1%TYPE,
F_CURSOROUT BUG_PACKAGE.T_CURSOR
) RETURN NUMBER;
END BUG_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY BUG_PACKAGE IS
FUNCTION BUG_FUNCTION (
IN_COL1 IN DBI_BUG.COL1%TYPE,
F_CURSOROUT BUG_PACKAGE.T_CURSOR
) RETURN NUMBER IS
INVALID_QUERY_DATA EXCEPTION;
ROWCOUNT NUMBER;
BEGIN
IF IN_COL1 = '' THEN
RAISE INVALID_QUERY_DATA;
END IF;
SELECT COUNT(*)
INTO ROWCOUNT
FROM DBI_BUG
WHERE COL1 = IN_COL1;
IF ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;
OPEN F_CURSOR FOR
SELECT COL2, COL3
FROM DBI_BUG
WHERE COL1 = IN_COL1;
RETURN 0;
EXCEPTION
WHEN INVALID_QUERY_DATA
THEN
RETURN -2;
WHEN NO_DATA_FOUND
THEN
RETURN -1;
END BUG_FUNCTION;
END BUG_PACKAGE;
/
EXIT
== bug.pl
==
#!/usr/local/bin/perl -w
use strict;
use DBI qw(:sql_types); # Perl DBI Database
Access module
use DBD::Oracle qw(:ora_types); # Perl Oracle access module
# Database access variables
my $dbh;# Database handle
my $sth;# Statement handle
my $csr;
