Resize datafile oracle using execute immediate

Question

I need resize this datafile '+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919', why this simple procedure is wrong?.

Other options, plis?

SQL> begin
      2  execute immediate 'alter database datafile '+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919' resize 732M';
      3  exit;
      4  /
    execute immediate 'alter database datafile '+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919' resize 732M';


                                                                                *

ERROR at line 2: ORA-06550: line 2, column 87: PLS-00103: Encountered the symbol ".269" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem return returning <> or != or ~= >= <= <> and or like like2 like4 likec between into using || bulk member submultiset

possible duplicate of [How to anticipate and escape ' in oracle](http://stackoverflow.com/questions/27373158/how-to-anticipate-and-escape-in-oracle) — Lalit Kumar B, May 14 '15 at 11:12

score 2 · Answer 1 · answered May 14 '15 at 11:01

2

You need to escape single quote (') with double quote ('')

begin
      execute immediate 'alter database datafile ''+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919'' resize 732M';
end;
/

But why are you using dynamic sql? You can just write

alter database datafile '+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919' resize 732M';

in sqlplus?

answered May 14 '15 at 11:01

Stefan Yordanov

666
3
10

You don't escape a single quote (also known as an apostrophe) with double-quotes (quotation marks) - you use two apostrophes, one after the other. The code is correct, but the explanation is confusing. – Bob Jarvis - Слава Україні May 14 '15 at 11:05
Yes, in sqlplus, I'm doing a procedure to maintenance of tablespaces, will be automatic. – Sandy Ramirez May 14 '15 at 11:37
But, this statement is a problem. – Sandy Ramirez May 14 '15 at 11:38
SQL> begin 2 execute immediate 'alter database datafile ''+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919'' resize 8500M'; 3 exit; 4 / exit; * ERROR at line 3: ORA-06550: line 3, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purg – Sandy Ramirez May 14 '15 at 11:39
@SandyRamirez - your line 3 is `exit;` instead of `end;` which isn't right for a PL/SQL block? – Alex Poole May 14 '15 at 12:06
Thanks for your answers!!! .....This statement too running : execute immediate 'alter database datafile ||''''||+GMPDATDESA01/prod/datafile/apps_ts_tx_idx.269.878649919'||''''|| ' resize 732M'; – Sandy Ramirez May 14 '15 at 12:39

Resize datafile oracle using execute immediate

1 Answers1