-
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,
-
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;
Cheers!
OraKid.
-
Thanks....I appreciate your input!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|