Soumitri Pattnaik Soumitri Pattnaik - 4 years ago 148
SQL Question

How can I use `USING` clause with `LIKE` operator in PL/SQL?

I have a PL/SQL in which I want to perform a search.
The PL/SQL is

DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE table_name='GETS_TS_EGU_LOOKUP' and data_type LIKE '%CHAR%') LOOP

EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING 'NONE';

IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;

END LOOP;

END;
/


This PL/SQL can perform look up on the keyword NONE. I want to perform a
LIKE
search on NONE like %NONE%.

How can I do it?

Is it even possible?

Answer Source

That should be

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
  ' WHERE ' || t.column_name || ' like ''%'' || :1 || ''%'''
  INTO match_count
  USING 'NONE';

But with 'NONE' being a constant, why not simply:

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
  ' WHERE ' || t.column_name || ' like ''%NONE%'''
  INTO match_count;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download