Re: PL/SQL Question for FORMS5.0

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/24
Message-ID: <34cf4230.40270926_at_192.86.155.100>#1/1


On Fri, 23 Jan 1998 16:09:34 -0500, Frank Phillips <fwp_at_ufl.edu> wrote:

>In Forms5 I am attempting to write a PL/SQL procedure to dynamically
>populate pick lists. This is easy, but, I wish to include a where
>clause. The problem: When passing the variables to dynamically build the
>SQL code you must put single quotes around the variable. This does not
>work as PL/SQL uses the single quote as the delimiter for the string you
>must concatenate.
>
>Sql_code := ‘select ‘||list_field||’ from ‘||table_name||’where
>‘||where_field||’ = ‘||field_value;
>
>This code will not work as field_value must be quoted. I have played
>with several formats attempting to get the quotes in, all have failed.
>Any ideas??? Thanks for the help.
>

In sql and pl/sql, in order to include a quote in a quoted string, you put in the quote twice. so the end result would like like:

sqlstmt := 'select * from emp where ename = ''KING''';

                                            ^^    ^^  <- will be single quotes

The easiest way I've found to do this without going crazy building a string is to use a small function like:

create or replace function dquote( p_str in varchar2 ) return varchar2 is
begin

    return '''' || replace( p_str, '''', '''''' ) || ''''; end;

So then you could code something like:

sql_code := 'select ' || list_field || ' from ' || table_name ||

            ' where ' || where_field || ' = ' || dquote(field_value);

This will return field_value not only properly quoted, but it will make sure that if field_value itself contains an embedded quote, it will be double quoted. So, for example if field_value has the string "it's a wonderful thing", it will return

'it''s a wonderful thing'  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 24 1998 - 00:00:00 CET

Original text of this message