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

'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@xxxxxxxxx]
Sent: Monday, November 27, 2006 3:55 PM
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



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.

.

Relevant Pages

  • 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)
  • [Full-Disclosure] Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit ... Connection closed by foreign host. ... think what we want to execute. ...
    (Full-Disclosure)
  • Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit we are ... Connection closed by foreign host. ... think what we want to execute. ...
    (Bugtraq)
  • Re: Wait for background processes to complete
    ... To be able to execute commands in the background and wait for their ... The documentation I am referring to is http://perldoc.perl.org/. ... You can run a command in the background with: ... There is a general problem with perl documentation: ...
    (comp.lang.perl.misc)
  • Execute Process Task not failing, but not executing the batch comm
    ... I can execute the following command from the windows "Run" prompt and it ... Might I have something set weird in SQL Server? ... server being by default configured to run as localsystem account, ...
    (microsoft.public.sqlserver.dts)