Thanks for the question, Greg.

Asked: July 19, 2004 - 12:06 pm UTC

Last updated: September 09, 2004 - 4:12 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I'm having a very annoying problem trying to get what should be some simple insert statements working in a PL/SQL script. I don't know that PL/SQL is necessarily the best way to accomplish what I'm trying to do but unfortunately it's the corporate standard and I have to work with it. Here's what's happening.
We create a new table. The table is used to store some SQL statements that we use to drive an application against specified tables.

BEGIN
EXECUTE IMMEDIATE ('CREATE TABLE X
(
A NUMBER (15,0),
B VARCHAR2 (10),
TABLE VARCHAR2 (50),
SQL VARCHAR2 (2000)
)');

EXECUTE IMMEDIATE ('INSERT INTO X
(A,B,TABLE,SQL) VALUES
(1,''X'',''Y'',''FROM Y WHERE ID='''XX'''')');
END;

The issue I'm having is how to properly quote the insert statement so that PL/SQL will process it properly. The issue I'm having is in the value for the SQL column with the quotes around the 'XX'.

If I was doing this insert outside of the EXECUTE IMMEDIATE it looks like this and works just fine.

INSERT INTO X(A,B,TABLE,SQL) VALUES
(1,'X','Y','FROM Y WHERE ID=''XX''');

I have tried every possible combination of quotes (and some pipes too) I can imagine to properly close the string in the EXECUTE IMMEDIATE and it doesn't work. The errors I get are usually either
"Quoted string not properly terminated" or "missing comma" depending on what exactly I'm doing. Unfortunately I can't seem to get this working outside of the EXECUTE IMMEDIATE either. If I do that I get the error "X Note declared." It doesn't seem to care that I create the table first, so it sees that as an undeclared variable.
Any suggestions on this are appreciated. I have looked all over.

Thanks

and Tom said...

nasty nasty nasty. not very "bind friendly", this app must not have to scale very large.

Anyway, when confronted with quotes in quotes in quotes, i generally drop down to replace or translate to make it more readable. Here i used translate:

ops$tkyte@ORA9IR2> BEGIN
2 EXECUTE IMMEDIATE ('CREATE TABLE X
3 (
4 A NUMBER (15,0),
5 B VARCHAR2 (10),
6 "TABLE" VARCHAR2 (50),
7 SQL VARCHAR2 (2000)
8 )');
9
10 EXECUTE IMMEDIATE translate('INSERT INTO X
11 (A,B,"TABLE",SQL) VALUES
12 (1, [X], [Y], [ FROM Y WHERE ID=[[XX]] ])', '[]', '''''' );
13 END;
14 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from x;

A B TABLE
---------- ---------- --------------------------------------------------
SQL
-------------------------------------------------------------------------------
1 X Y
FROM Y WHERE ID='XX'

I used [] as my "quotes". note that you need to doubleup the quotes on the quotes inside of the string that is inside of a string -- hence [[xx]] -- not just [x].

While this makes it easier to read, it won't help your runction performance very much..

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

RE: "Problems with escape sequence in EXECUTE IMMEDIATE",

Greg Gould, July 19, 2004 - 1:37 pm UTC

Thank you!
That worked perfectly.
I agree this is ugly and probably not the best way to do this, but for now it gets the job done, and no this really doesn't have to scale at all.
Thanks again.

And new in 10g....

Gary, July 19, 2004 - 7:33 pm UTC

They've got a new ability in PL/SQL in 10g to use an alternative to 'old' quoting.

From the PL/SQL User's Guide and Reference (Chapter 2 Fundamentals...) :

"
You can also use the following notation to define your own delimiter characters for the literal. You choose a character that is not present in the string, and then do not need to escape other single quotation marks inside the literal:

-- q'!...!' notation to use single quotes inside the literal
string_var := q'!I'm a string, you're a string.!';
"

Escape Characters

syed, September 08, 2004 - 5:31 pm UTC

Thanks Sir, this is excellent

Escape sequences

Syed, September 09, 2004 - 1:09 pm UTC

Tom,
Sorry to bother, but yesterday I was thinking I am successful, but still getting the error;

Can you please help what is the problem, the code is very dynamic.

Just want to update the information at the governing table, but don't know what exactly it is, problem are the 'quotes'.

Your help will be highly appreciated, thanks in advance.
Syed

EXACT CODE:

-- Updating information in the PM_TABLE_DROP_PART table, the governing table --

v_cmd := 'update PM_TABLE_DROP_PART set EXPORT_FILE_NAME = ['||v_stage||'.dmp'||'], EXPORT_DT = ['||SYSDATE||'] where table_name = ['||''||pm_rec.table_name||'] and partition_name = ['||pm_rec.partition_name||']';
dbms_output.put_line(v_cmd);
EXECUTE IMMEDIATE translate('v_cmd','[]','''''''');

Thanks

September 09, 2004 - 1:43 pm UTC

why the heck are you using dynamic sql?

there are so many things wrong there -- first and foremost is the unnecessary attempted use of dynamic sql.

second -- but equally as bad -- is the LACK OF BIND VARIABLES!!!!!!!!!!!! man oh man.

so, i won't even comment on the code (translate of the character string constant 'v_cmd' -- probably meant the variable v_cmd, not the string 'v_cmd'... putting sysdate in there as a string! ack, what happens to the time component -- or what if innocently "alter session set nls_date_format=....." something that does/does not including the time/day, whatever component). Ok, I guess I did comment on the code.

there is no way that should be anything else other than:

....
update pm_table_drop_part
set export_file_name = v_stage || '.dmp',
export_dt = sysdate
where table_name= pm_rec.table_name
and partition_name = pm_rec.partition_name;
.......

anything else is *wrong*

Escape Characters

Syed, September 09, 2004 - 4:12 pm UTC

Sorry Sir,

I was doing so many dynamic sqls, that I just forget this simple thing and was again unnecessarily using execute immediate, where as not required.

Accept my apology for disturbing you and thanks a lot for your help.....
YOU ARE REALLY REMARKABLE

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

RE: "Problems with escape sequence in EXECUTE IMMEDIATE",

And new in 10g....

Escape Characters

Escape sequences

Escape Characters

More to Explore

PL/SQL demos

PL/SQL docs