PERL Beginners - Perl DBI Oracle: Multiple statements

Author

Perl DBI Oracle: Multiple statements

Ravi Malghan

2006-11-27, 6:57 pm

Hi: Not sure if this is the right place to post this question.=0A=0AMy dba =
has asked to execute a alter statement before the select statement in a per=
l script. So I need to run the following. Supposedly the alter statement, h=
elps run the select sql run faster.=0A=0Aalter session set db_file_multiblo=
ck_read_count =3D 128;=0ASELECT ED.utime, ED.info, ED.agent_id FROM EVENT_D=
ATA ED WHERE ED.utime between $period order by ED.utime;=0A=0Awhere $period=
is a string such as "190 and 300".=0A=0AI tried the following=0A my $sql=
=3D qq{=0A BEGIN=0A alter session set db_file_multibloc=
k_read_count =3D 128;=0A SELECT ED.utime, ED.info, ED.agent_id F=
ROM EVENT_DATA ED WHERE ED.utime between $period order by ED.utime;=0A =
END; };=0A my $sth =3D $dbh->prepare($sql);=0A $sth->execute();=0A=
=0AI get the following error=0A=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=0ADBD::Oracle::st execute failed: ORA-06550: line 3, col=
umn 5:=0APLS-00103: Encountered the symbol "ALTER" when expecting one of th=
e following:=0A begin case declare exit for goto if loop mod null pragma=
=0A raise return select update while with <an identifier>=0A <a double-=
quoted delimited-identifier> <a bind variable> <<=0A close current delete=
fetch lock insert open rollback=0A savepoint set sql execute commit fora=
ll merge=0A <a single-quoted SQL string> pipe=0AThe symbol "update was in=
serted before "ALTER" to continue. (DBD ERROR: error possibly near <*> indi=
cator at char 14 in '=0A BEGIN=0A =
<*>alter session set db_file_multiblock_read_count =3D 128;=0A=
SELECT ED.utime, ED.info, ED.agent_id FROM =
EVENT_DATA ED WHERE ED.utime between 1164650400 and 1164653940 order by ED.=
utime;=0A END; ') [for Statement "=0A =
BEGIN=0A alter session set db_file_=
multiblock_read_count =3D 128;=0A SELECT ED.=
utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHERE ED.utime between 11646=
50400 and 1164653940 order by ED.utime;=0A END; "] a=
t /actuate/AcServer/reports_scripts/report_functions.pl line 48.=0A=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0ACou=
ld someone help.=0AThanks=0ARavi=0A=0A=0A =0A______________________________=
________________________________________
______________=0ADo you Yahoo!?=0AE=
veryone is raving about the all-new Yahoo! Mail beta.=0Ahttp://new.mail.yah=
oo.com

Paul Lalli

2006-11-28, 7:58 am

Ravi Malghan wrote:
> My dba has asked to execute a alter statement before the select statement=
in a perl script. So I need to run the following. Supposedly the alter sta=
tement, helps run the select sql run faster.
>
> alter session set db_file_multiblock_read_count =3D 128;
> SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHERE ED.utime b=
etween $period order by ED.utime;
>
> where $period is a string such as "190 and 300".
>
> I tried the following
> my $sql =3D qq{
> BEGIN
> alter session set db_file_multiblock_read_count =3D 128;
> SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHER=
E ED.utime between $period order by ED.utime;
> END; };
> my $sth =3D $dbh->prepare($sql);
> $sth->execute();
>

> Could someone help.

Maybe this is na=EFve, but why aren't you just doing two queries?

$dbh->do(qq{alter session set db_file_multiblock_read_count =3D 128;});
my $sql =3D qq{SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED
WHERE ED.utime between $period order by ED.utime;});
my $sth =3D $dbh->prepare($sql);
$sth->execute();

Paul Lalli

Sponsored Links