Is it possible to use the content of a variable in an regexp_like as the source column in Oracle

I want to compare the contents of table with regex pattern and column_name which is saved as variable in a procedure. Regexp_like with variables v_column_name, v_column_regexp are then in the if statement.


CREATE OR REPLACE PROCEDURE TBL_COLUMN_REGEX (
    p_data       IN VARCHAR2,
    p_table_name   IN VARCHAR2)
AS
    v_data       VARCHAR2 (50);
    v_table_name   VARCHAR2 (200);
    v_column_name     VARCHAR2 (100);
    v_column_regexp   VARCHAR2 (100);
    v_row_rownum      NUMBER;
BEGIN
    v_data := p_data;
    v_table_name := p_table_name;


    FOR i
        IN (SELECT column_name, t1.column_type, column_regexp
              FROM T_TBL_COLUMN  t1
                   INNER JOIN t_column_regexp t2
                       ON t1.column_type = t2.column_type
             WHERE data = v_data AND table_name = v_table_name)
    LOOP
        v_column_name := i.column_name;
        v_column_regexp := i.column_regexp;

        INSERT INTO T_LOG_TBL_COLUMN (DATA, TABLE_NAME, LOG_TEXT)
                 VALUES (  v_data,  v_table_name,  v_column_name  || ' REGEXP Check starts with '  || v_column_regexp);


        FOR r_row IN (SELECT v_column_name, ROWNUM FROM T_TEST)
        LOOP
            v_row_rownum := r_row.ROWNUM;

            IF NOT REGEXP_LIKE (v_column_name,  v_column_regexp)--v_column_name:='DATE' with content as '202311', v_column_regexp:='^\d{4}(0[1-9]|1[012])$'
            THEN
                INSERT INTO T_LOG_TBL_COLUMN (DATA,
                                                 TABLE_NAME,
                                                 LOG_TEXT)
                         VALUES ( v_data, v_table_name,   'In '|| v_row_rownum || ': ' || v_column_name);
            
                COMMIT;
            END IF;

            COMMIT;
        END LOOP;
    END LOOP;
END;
/

It doesn't match in the if statement, even though I get a match if I use regexp_like in a select statement.

SELECT DISTINCT rownum, DATE
  FROM T_TEST
 WHERE  REGEXP_LIKE (DATE, '^\d{4}(0[1-9]|1[012])$')

Solution

Your query SELECT v_column_name, ROWNUM FROM T_TEST_4 will get the literal value of the variable (i.e. the column name) for every row in your table, not the column value.

You can see that in this simplified demo:

declare
  v_column_name all_tab_columns.column_name%type := 'DUMMY';
begin
  for r_row in (select v_column_name as col from dual)
  loop
    dbms_output.put_line(r_row.col);
  end loop;
end;
/

dbms_output:
DUMMY

It's showing the column name, DUMMY, not the value, X.

If you use a dynamic SQL cursor loop then you get the value instead:

declare
  v_column_name all_tab_columns.column_name%type := 'DUMMY';
  v_cursor sys_refcursor;
  v_value varchar2(4000);
begin
  open v_cursor for 'select "' || v_column_name || '" from dual';
  loop
    fetch v_cursor into v_value;
    exit when v_cursor%notfound;
    dbms_output.put_line(v_value);
  end loop;
end;
/

dbms_output:
X

fiddle

You can adapt your code to do the same thing; add variable declarations for the cursor and results:

    v_cursor sys_refcursor;
    v_column_value varchar2(4000);
    v_rownum number;

and change your loop from:

        FOR r_row IN (SELECT v_column_name, ROWNUM FROM T_TEST_4)
        LOOP
            v_row_rownum := r_row.ROWNUM;

            IF NOT REGEXP_LIKE (v_column_name,  v_column_regexp)
...

        OPEN v_cursor FOR 'SELECT "' || v_column_name || '", ROWNUM FROM T_TEST_4';
        LOOP
            FETCH v_cursor INTO v_column_value, v_row_rownum;
            EXIT WHEN v_cursor%NOTFOUND;

            IF NOT REGEXP_LIKE (v_column_value, v_column_regexp)
...

and presumably change v_column_name to v_column_value when you insert into your log table too. (You probably don't want to be committing after every insert...)