Dynamic SQL using quotation marks

Hi,
I'm trying to formulate a dynamic sql string to search for values in a VARCHAR2 column if the column contains an empty string. In SQL*PLUS I can do the following:
SQL> SELECT COUNT(*) FROM PART WHERE PRODUCT_NAME LIKE ' ';

COUNT(*)
----------
1

But what's the right syntax for this dynamic SQL version...
I'm getting weird compile errors with the following:
v_sql_stmt := 'SELECT count(*)
FROM ' || p_table_name ||
' WHERE '|| p_col_name || ' LIKE '' '';
EXECUTE IMMEDIATE v_sql_stmt INTO v_cnt_empty;

Thanks,

Quote:

---

Originally posted by welchdor
' WHERE '|| p_col_name || ' LIKE '' '';

---

This is where your mistake is. Doing '' '' is the wrong format for quoting a single-quoted string.

To place single quotes around a character string, enclose the character string within 3 single quotes: ''' At the start of a character string: the first single quote defines the start of the character string; it is one of the two single quotes that surround the string. The second and third single quotes define the literal single quote. At the end of the character string: the first and second single quotes define the literal single quote. The third single quote closes the character string; it is the other single quote that surrounds the string.

Example 4
---------
SQL> SELECT '''character string in quotes''' result 2> FROM dual;
RESULT
----------------------------
'character string in quotes'

See Note 1005607.6 on MetaLink if you have access there.

[Edited by stecal on 09-23-2002 at 05:55 PM]

try to use -- CHR(39) instead of Single quote

v_sql_stmt := 'SELECT count(*)
FROM ' || p_table_name ||
' WHERE '|| p_col_name || ' LIKE '' '';
EXECUTE IMMEDIATE v_sql_stmt INTO v_cnt_empty;

Thanks....I appreciate your input!