RE: ORA-911 during DBD::Oracle prepare

From: Alan Gano <Alan.Gano_at_Banfield.net>
Date: Thu, 06 Nov 2003 14:14:24 -0800
Message-ID: <F001.005D5DCB.20031106141424@fatcity.com>

DBI is able to use named binds

e.g.,

   my $cursor=$$self{conn}->prepare(q{

      select column_name from
      dba_cons_columns
      where
         (owner,constraint_name) = (
            select owner, constraint_name
            from dba_constraints
            where
               owner = :table_owner AND
               table_name = :table_name AND
               constraint_type = 'P'
         )

   });
$cursor->bind_param(":table_owner",uc($owner));
$cursor->bind_param(":table_name",uc($table));
$cursor->execute();

$$self{pk_columns}=[];

   my @columns;
   while(my $row=$cursor->fetchrow_arrayref())    {

      push @columns,$$row[0];
   }
$$self{pk_columns}=\@columns;

Alan.

-----Original Message-----
Sent: Thursday, November 06, 2003 1:40 PM To: Multiple recipients of list ORACLE-L

Don't you want SELECT machine, program, SYS_CONTEXT(?, ?), SYS_CONTEXT(?, ?)... DBI uses positional binds as opposed to named binds.

On Thu, 2003-11-06 at 16:29, Jesse, Rich wrote:

> Hey all,
> 
> I'm trying to get a simple query running in Perl 5.6.1, DBI 1.30,
> DBD::Oracle 1.14, Oracle 8.1.7 on HPUX 11.0 talking to a 9.2.0.4 RAC DB on
> Linux (whew!).  Here's the pertinent part of the code:
> 
> #!/usr/bin/perl -w
> 
> use strict;
> use DBI;
> use DBD::Oracle qw(:ora_types);
> 
> my ($dbh, $sth);
> 
> $dbh = DBI->connect("dbi:Oracle:mysid","myuser","mypass");
> 
> $sth = $dbh->prepare(q{
> 	SELECT machine, program, SYS_CONTEXT(:userenv, :ipaddress),
> SYS_CONTEXT(:userenv, :osuser)
> 		FROM v$session VS;
> });
> 
> At this point, I get an "ORA-911: invalid character" on the prepare.  I
> thought perhaps the "$" was hosing me in "v$session", so I tried escaping

it

> to "v\$session" (along with the underscores and parens) and using "qq"
> instead of "q", but to no avail.
> 
> I knew I shouldn't have unsub'd from the DBI mailing list...
> 
> Anyone?
> TIA,
> Rich
> 
> Rich Jesse                           System/Database Administrator
> rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net

Richard Quintin, DBA
Information Systems & Computing, DBMS
Virginia Tech

-- 
"If you would stand well with a great mind, leave him with a favorable
impression of yourself; if with a little mind, leave him with a
favorable impression of himself." -- Samuel Taylor Coleridge
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: rquintin_at_vt.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Alan Gano
  INET: Alan.Gano_at_Banfield.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Nov 06 2003 - 16:14:24 CST