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
105 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RDBMS and No sql database 4 56
multiple application databases same MSSQL instance 5 52
SSRS troubles 4 46
T-SQL: New to using transactions 9 18
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now