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
97 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 68

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 31

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

10 Experts available now in Live!

Get 1:1 Help Now