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?
 
sdstuberConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.