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

Use two statements. You can't combine them into one like that.
(without amounts of pain far exceeding that of just executing two separate
statements)

Shouldn't you be using a bind variable instead of interpolating $period into
the SQL as well?

--
Andy Hassall :: andy@xxxxxxxxxxx :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

-----Original Message-----
From: Ravi Malghan [mailto:rmalghan@xxxxxxxxx]
Sent: 27 November 2006 22:55
To: dbi-users@xxxxxxxx
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

.

Relevant Pages

  • Re: DBD Oracle: multiple statements in a single execute?
    ... at the startof a script, which then remains in effect for the duration ... ED.utime between $period order by ED.utime; ... savepoint set sql execute commit forall merge ... <*>alter session set ...
    (perl.dbi.users)
  • Re: DBD Oracle: multiple statements in a single execute?
    ... the SQL as well? ... Subject: DBD Oracle: multiple statements in a single execute? ... ED.utime between $period order by ED.utime; ... <*>alter session set ...
    (perl.dbi.users)
  • Re: DBD Oracle: multiple statements in a single execute?
    ... It is concidered rather poor form it create you sql on the fly so while ... Subject: DBD Oracle: multiple statements in a single execute? ... ED.utime between $period order by ED.utime; ... <*>alter session set ...
    (perl.dbi.users)
  • DBD Oracle: multiple statements in a single execute?
    ... my dba has asked to execute a alter statement before the select ... ED.utime between $period order by ED.utime; ... <*>alter session set ...
    (perl.dbi.users)
  • Perl DBI Oracle: Multiple statements
    ... My dba has asked to execute a alter statement before the select statement in a perl script. ... helps run the select sql run faster. ... SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHERE ED.utime between $period order by ED.utime; ...
    (perl.beginners)