Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dealing with single quotes in Dynamic SQL

james_pJul 23 2014 — edited Jul 24 2014

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 ?

This post has been answered by Karthick2003 on Jul 23 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 21 2014
Added on Jul 23 2014
8 comments
6,616 views