Single quoting singe quotes (Confusion?)

I am looking through a really large Oracle stored procedure that was written by an ex team member who left
several years ago. I noticed that in the stored procedure that many, many strings are surounded by four
single quotes. str := strName + '''' + strId; There are also other places that four single quotes are used.
(Question 1)Would it ever be necessary to add more than four single quotes?? Note that my stored procedure is not created
within an exec statement, so I don't see how  more than four single quotes would be necessary? I imagine that more
than four would be a syntax error?

Question 2 : Would it ever be necessary to single quote double quotes ? I am talking about the double quotes that come from the double
quote on the keyboard?

I see at the link below that you can escape single quotes, if the sql is within an exec statement. So in that case my string
above would be the following : str := strName + '''''' + strId;
where there are a total of six single quotes.

http://www.dba-oracle.com/t_oracle_execute_immediate.htm

Just trying to clear this up, as I am feeling overwhelmed by all of the single quotes and how many should be used, and when?
With all of the single quotes I see, it is difficult to determine if some of them are also double quotes?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Q1 - Yes, but it's not common.  You'd almost have to be doing it on purpose.

You need to double up single quotes when they are inside of another string.
The only reason you'd need more would be if you had a string with quotes, inside a string with quotes, inside of another string literal wrapped in quotes.

For example:  select 'This string contains a substring: ''This substring contains a sub-substring: ''This is the 3rd level''''' from dual

Replacing quotes with ( [ { } ] )  to indicate the layering it would look like this...

(This string contains a substring: [This substring contains a sub-substring: {This is the 3rd level}])

So yes, it's silly; but if you nest quoted strings within others you can get situations like that.

Q2 - Sure, if you want to put double-quotes around some other string variable.

For example:  v_string := '"' || v_string || '"'

This type of sql construction is actually fairly common when querying from the data dictionary to make sure identifiers are properly quoted to preserver case or special characters like this:

SELECT '"' || owner || '"."' || table_name || '"'
  FROM all_tables;


You can simplify one layer of quotes by using the Q syntax.

Note I don't have to double the quotes within the names below:

select q'[O'Malley and D'Artagnan]' from dual
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.