CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Soumitri Pattnaik
Soumitri Pattnaik

Reputation: 3556

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?

Upvotes: 0

Views: 2144

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

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;

Upvotes: 3

Narasimha Maiya
Narasimha Maiya

Reputation: 1029

You can declare a new variable to assign the string you want to search and then you cane use it in the query as shown below

    DECLARE
          match_count INTEGER;
         srch_str varchar2(20);
        BEGIN
          srch_str:= 'NONE'
          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||' like ''%'||srch_str||'%'''
              INTO match_count;

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

          END LOOP;

        END;

It will work perfectly fine.

Upvotes: 1

Related Questions