Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dynamic PL/SQL

RE: Dynamic PL/SQL

From: larry elkins <>
Date: Sat, 23 Dec 2000 14:27:36 -0600
Message-Id: <>


I am assuming that you are running into an ORA-01756 error, "quoted string not properly terminated". Is this correct?

It looks like you are trying to get around this by using the REPLACE function in your dynamically built SELECT statement to replace a single quote in the AUTHOR_LASTNAME with a space. But, if you inspect your SELECT statement that is built, you will see that the REPLACE portion of the SELECT statement ends up looking like:

REPLACE(MS.AUTHOR_LASTNAME,'',' ') So, when the SELECT is executed, it is *not* replacing the single quote with a space and you end up with the error on the INSERT. I think a lot of people myself included, who have used DBMS_SQL to dynamically insert data have been bitten by the ORA-01756 error. I think the *easiest* fix with regards to understanding what the code is doing is to use the replace function against the local variable holding the value. So, you would rip out the REPLACE part in your dynamically built SELECT and simply say MS.AUTHOR_LASTNAME. Then, after your DBMS_SQL.COLUMN_VALUE, you could use the replace against the variable:

   DBMS_SQL.COLUMN_VALUE(li_cursor_id, 9, v_author_lastname);    v_author_lastname := replace(v_author_lastname,'''',' ');

For me, that is the easiest to understand. I take it a step further and use a function:

  RETURN(REPLACE(p_string,'''',' '));

You could define this within a PL/SQL block, as an internal function within a package, or, as an exposed generic routine. Whatever floats your boat. Then, do something like:

   v_author_lastname := FIX_QUOTE(v_author_lastname);

That's the way I like to do it. Actually, I do it a little bit differently in that I like to preserve the quote. As can be seen from your code, you already know that 2 quotes gives 1 quote. So, my function actually says:


Now, when the insert statement is created, the value would look something like 'O''Dell'. And there are so many variations -- some people find it easier to understand if they use chr(39) instead of all the tickies, maybe even defining a variable to hold it and using the variable.

If you want to continue using the REPLACE directly within the dynamically built SELECT statement, you could do it something like REPLACE(MS.AUTHOR_LASTNAME,'''''''','' '') or REPLACE(MS.AUTHOR_LASTNAME,chr(39),'' '') to replace the quote with a space. To replace the quote with a double quote so that you preserve the quote upon insert, try:

REPLACE(MS.AUTHOR_LASTNAME,'''''''','''''''''''') or REPLACE(MS.AUTHOR_LASTNAME,chr(39),chr(39)||chr(39))

You get the idea -- there are a lot of different ways to approach this. I like function approach because I can easily document it, its purpose, and why it is used.


Larry G. Elkins

-----Original Message-----
From: []On Behalf Of Viktor Sent: Thursday, December 21, 2000 3:41 PM To: Multiple recipients of list ORACLE-L Subject: Dynamic PL/SQL

Hello all,

I have a dynamic PL/SQL procedure that selects author_name. For some reason author names that contain

"'" cause the procedure to fail.
Does anyone have experienced this before and has a Received on Sat Dec 23 2000 - 14:27:36 CST

Original text of this message