Solved

when executing Exceute Imidiate in Oracle - how many (') you need to define an inner string in the statmnent ( exp. Pls.)

Posted on 2016-07-27
3
108 Views
Last Modified: 2016-07-30
Hello,
when executing Exceute Imidiate in Oracle - how many (') you need to define an inner string in the statmnent ( exp. Pls.) .
note : There is 1 (') in the beginng and end of the statement
it is getting harder when inside the inner sring there  is (")
THX
Daniel
0
Comment
Question by:Member_2_5866372
3 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 250 total points
ID: 41730770
The rule is simple: Each inner single quote needs to be doubled.
' select * from tbl where id = ''one'' and description = '''' '

Open in new window

This also shows how to state an empty string.
1
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 41731203
The number of single quotes to use can sometimes be rather confusing. To avoid the confusion, I find it a little easier to visualize the statement using ascii values. For example, the statement " select * from stores where store_name = 'ABC' " in an execute immediate statement could be written as -
execute immediate 'select * from stores where store_name = '||chr(39)||'ABC'||chr(39);
Another step I often take is to declare a variable to hold the sql statement then print it out before using the execute immediate.
declare
v_sql varchar2(255);
begin
v_sql := 'select * from stores where store_name = '||chr(39)||'ABC'||chr(39);
dbms_output.put_line(v_sql); ==> view the sql
--execute immediate v_sql ==> comment out until the sql is what you need
end;

Then after the sql has been verified, comment out the dbms_output statement and un-comment the execute immediate statement
1
 

Author Comment

by:Member_2_5866372
ID: 41736036
thx
awking00 and Qlemo you have explained the issue completly .
All the best
Daniel
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question