Path: news.easynews.com!easynews!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail
From: viper@gmx.fr (Vi.)
Newsgroups: comp.databases.oracle.server
Subject: newbie: Pl/SQL - loop - hotbackup
Date: 4 Apr 2002 17:28:58 -0800
Organization: http://groups.google.com/
Lines: 46
Message-ID: <2ea00f5e.0204041728.60feb12f@posting.google.com>
NNTP-Posting-Host: 216.13.209.130
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1017970138 19762 127.0.0.1 (5 Apr 2002 01:28:58 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 5 Apr 2002 01:28:58 GMT
Xref: easynews comp.databases.oracle.server:142104
X-Received-Date: Thu, 04 Apr 2002 18:26:42 MST (news.easynews.com)

Hi,

I am trying to write a code to turn all the tablespaces on a db box in
backup mode. I have the code using sqlplus but this time, I need to
trigger that from a JDBC connection.

Here is the code I am trying to send through JDBC:

BEGIN
FOR tablespacename IN (SELECT tablespace_name FROM dba_tablespaces)
LOOP
execute immediate 'ALTER TABLESPACE '|tablespacename||' BEGIN BACKUP';
END LOOP;
END;
/

and I am getting the following exception (because the pl/sql is
wrong):

Exception occured in SQL statement [ BEGIN FOR tablespacename IN
(SELECT tablespace_name FROM dba_tablespaces) LOOP execute immediate
'ALTER TABLESPACE '|tablespacename||' BEGIN BACKUP'; END LOOP; END; 
]:
ORA-06550: line 1, column 118:
PLS-00103: Encountered the symbol "|" when expecting one of the
following:

   . ( * @ % & = - + ; < / > at in is mod not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like between into using || bulk
The symbol ". was inserted before "|" to continue.

Exception occured: ORA-06550: line 1, column 118:
PLS-00103: Encountered the symbol "|" when expecting one of the
following:

   . ( * @ % & = - + ; < / > at in is mod not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like between into using || bulk
The symbol ". was inserted before "|" to continue.


What wrong in this simple pl/sql code ?
Thank you VERY MUCH for your help.

Jean-Noel