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
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
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

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@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

-----Original Message-----
From: Ravi Malghan [mailto:rmalghan@yahoo.com]
Sent: 27 November 2006 22:55
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
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
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

'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:rmalghan@yahoo.com]=20
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=20
statement. So I need to run the following

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=20
between $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
WHERE=20
ED.utime between $period order by ED.utime;
END; };
print "Running\n$sql\n";
my $sth =3D $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=20
following:

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


Could someone help.

Thanks
Ravi=20



This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
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.

Hi: could you please provide more detail on what you mean by bind variable?
Thanks
Ravi
""Andy Hassall"" wrote in message
news:051601c7127f$878fe710$6501a8c0@excession...
> 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@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
>
> -----Original Message-----
> From: Ravi Malghan [mailto:rmalghan@yahoo.com]
> Sent: 27 November 2006 22:55
> 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
> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall merge
> 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
>

I have successfully used

my $Osth = $Odbh->prepare("ALTER SESSION SET NLS_DATE_FORMAT =
'YYYYMMDDHH24MISS'");
$Osth->execute() or die "Failed to set Oracle date format: $DBI::errstr\n";

at the startof a script, which then remains in effect for the duration
of the script. So you should be able to do a similar thing, without
requiring to go into PL/SQL at all.

hth,

victor


On 28/11/06, Reidy, Ron wrote:
> '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:rmalghan@yahoo.com]
> 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
> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall merge
> 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.
>
>

It is concidered rather poor form it create you sql on the fly so while

my $sql='Select $field1, $field2 from test_table where $field1=$var1 and
$field2=$var2

is ok

you are opening yourself to a host of problems such as

1) "hacking" with SQL insertion
2) problems with using ' and " if you field are strings
3) and a number of other reaseons Style,maintainablity scaleabilit

buch better to do this
my $sql='Select name,year from test_table where name=:name and year=:year

$c = $db->prepare($sql);
$c->bind_param(":name",$var1 );
$c->bind_param(":year",$var2 );
$c->execute();

abd let DBI do the formating of ' for you

cheers

""Ravi Malghan"" wrote in message
news:20061128023443.9637.qmail@lists.develooper.com...
> Hi: could you please provide more detail on what you mean by bind
> variable?
> Thanks
> Ravi
> ""Andy Hassall"" wrote in message
> news:051601c7127f$878fe710$6501a8c0@excession...
>> 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@andyh.co.uk :: http://www.andyh.co.uk
>> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
>>
>> -----Original Message-----
>> From: Ravi Malghan [mailto:rmalghan@yahoo.com]
>> Sent: 27 November 2006 22:55
>> 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
>> <<
>> close current delete fetch lock insert open rollback
>> savepoint set sql execute commit forall merge
>> 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
>>
>
>