Version: 11.2.0.4
I am a bit of a beginner when it comes to Dynamic SQL.
I had a requirement in which I had to execute the following command on all the datafiles in the DB.
-- Expected output
alter database datafile '<file_name>' resize 10g ;
The file_name, which is dynamically generated must be enclosed in single quotes.
-- Sample Data
create table x (file_name varchar2(90));
insert into x values ('/u04/oradata/ORCL/system.dbf');
insert into x values ('/u04/oradata/ORCL/sysaux.dbf');
insert into x values ('/u04/oradata/ORCL/users01.dbf');
I managed to do it using a code similair to below. But , I had to do several trial and error stuff to get this working because of getting the correct number of single quotes for the string generated for v_sql below. So, I want to get the basics right.
declare
v_sql varchar2(350);
begin
for rec in (select file_name from x)
loop
v_sql := 'alter database datafile '''||rec.file_name||''' resize 10g';
--- EXECUTE IMMEDIATE v_sql;
dbms_output.put_line ( v_sql);
end loop;
end;
/
alter database datafile '/u04/oradata/ORCL/system.dbf' resize 10g
alter database datafile '/u04/oradata/ORCL/sysaux.dbf' resize 10g
alter database datafile '/u04/oradata/ORCL/users01.dbf' resize 10g
PL/SQL procedure successfully completed.
I would like to know the role each single quote plays. I have used 8 single quotes in V_SQL variable above and I have numbered each single quote as shown below.
1 234 567 8
| ||| ||| |
v_sql := 'alter database datafile '''||rec.file_name||''' resize 10g';
Sample Output from the Dynamic String generation:
alter database datafile '/u04/oradata/ORCL/users01.dbf' resize 10g
1 and 4 ---> start and end of the string alter database datafile '
3 ---> string literal single quote shown in Red in Sample Output
2 ----> To escape 3 (string literal single quote)
5 and 8 ----> start and end of the string ' resize 10g
6 -----> To escape 7 (string literal single quote)
7 -----> string literal single quote shown in Green in Sample Output
Are my asssumptions correct ?