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

Member_2_5866372
Member_2_5866372 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
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.
awking00Information Technology Specialist
Commented:
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

Author

Commented:
thx
awking00 and Qlemo you have explained the issue completly .
All the best
Daniel

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial