RE: DBD Oracle: multiple statements in a single execute?

[Reidy, Ron]

'ALTER is not a valid PL/SQL command.  You need to use 'EXECUTE
IMMEDIATE' for this command.  The Oracle PL/SQL docs describe this
command.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Ravi Malghan [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 27, 2006 3:55 PM
To: dbi-users@perl.org
Subject: DBD Oracle: multiple statements in a single execute?

Hi: my dba has asked to execute a alter statement before the select 
statement. So I need to run the following

alter session set db_file_multiblock_read_count = 128;
SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHERE ED.utime 
between $period order by ED.utime;

where $period is a string such as "190 and 300".

I tried the following
   my $sql = qq{
         BEGIN
            alter session set db_file_multiblock_read_count = 128;
            SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED
WHERE 
ED.utime between $period order by ED.utime;
         END; };
   print "Running\n$sql\n";
   my $sth = $dbh->prepare($sql);
   $sth->execute();


I get the following error
====================
DBD::Oracle::st execute failed: ORA-06550: line 3, column 5:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the 
following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge
   <a single-quoted SQL string> pipe
The symbol "update was inserted before "ALTER" to continue. (DBD ERROR: 
error possibly near <*> indicator at char 14 in '
                        BEGIN
                                <*>alter session set 
db_file_multiblock_read_count = 128;
                                SELECT ED.utime, ED.info, ED.agent_id
FROM 
EVENT_DATA ED WHERE ED.utime between 1164650400 and 1164653940 order by 
ED.utime;
                        END; ') [for Statement "
                        BEGIN
                                alter session set 
db_file_multiblock_read_count = 128;
                                SELECT ED.utime, ED.info, ED.agent_id
FROM 
EVENT_DATA ED WHERE ED.utime between 1164650400 and 1164653940 order by 
ED.utime;
                        END; "] at 
/actuate/AcServer/reports_scripts/report_functions.pl line 48.
=========================

Could someone help.

Thanks
Ravi 



This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is 
intended 
to be for the use of the individual or entity named above. If you are not the 
intended recipient, please be aware that any disclosure, copying, distribution 
or use of the contents of this information is prohibited. Please notify the
sender  of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.