newbie: Pl/SQL - loop - hotbackup

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 Received on Thu Apr 04 2002 - 19:28:58 CST