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
110 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 70

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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