RE: Dynamic PL/SQL

Larry,
Thanks a lot for taking the time to go over this. I really appreciate it.
I should've indicated the error:
I am getting ORA-00917: missing comma.
Do you think that it could be due to the same cause.

Thanks!

• larry elkins <elkinsl_at_flash.net> wrote:
  > Viktor,
  >
  > 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:
  >
  > Function FIX_QUOTE (p_string VARCHAR2) RETURN
  > VARCHAR2
  > IS
  > Begin
  > RETURN(REPLACE(p_string,'''',' '));
  > End;
  >
  > 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:
  >
  > RETURN(REPLACE(p_string,'''',''''''));
  >
  > 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.
  >
  > Regards,
  >
  > Larry G. Elkins
  > elkinsl_at_flash.net
  >
  > -----Original Message-----
  > Sent: Thursday, December 21, 2000 3:41 PM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > 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
  > solution?
  >
  > Attached is a section from the procedure.
  >
  > Thanks.
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: larry elkins
  > INET: elkinsl_at_flash.net
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).