Fetching data of a dynamic column from a dynamic view using cursor

Question

The requirement is to check data consistency of a view. It's a bit complicated, so let's move step by step.

1. A table check_data_column has basically 5 imp. columns: ViewName, ColumnName, Mandatory, MaxLength, DataType.

It will contain Information about each field of a particular view. For example:

ViewName: Employee_V
ColumnName: EmployeeNo
Mandatory: 1 (True)
MaxLength: 10
DataType: Number

ViewName: Employee_V
ColumnName: EmployeeName
Mandatory: 1 (True)
MaxLength: 20
DataType: String

2. Now I have to write a function which takes all the entries from this check_data_column and check the data in the appropriate view for each mentioned column.

From the above example, it will check the data in the Employee_V. Each entry in EmployeNo column should not be null, max length should be 10 and it should be a numeric value. Similarly, each entry in EmployeeName column should not be null, max length should be 20 and it should be a string.

2. Number of views is unknown and no. of columns in each view is unknown.

To solve the above problem, I wrote the following code:

FUNCTION CheckData(viewname VARCHAR2)
RETURN VARCHAR2
Is

  return_v      VARCHAR2(1000);
  query_v       VARCHAR2(200);
  column_c      SYS_REFCURSOR;
  column_v      column_c%ROWTYPE;

  CURSOR ddc_c IS
  SELECT *
  FROM check_data_column;

BEGIN
  return_v := null;

  FOR ddc_v IN ddc_c LOOP

    query_v := 'SELECT' || ddc_v.column_name || 'FROM anc_sap.' || ddc_v.viewname;

    OPEN column_c FOR query_v;
    LOOP
       FETCH column_c INTO column_v;
       EXIT WHEN column_c%NOTFOUND;

       IF LENGTH(column_v) > ddc_v.max_length THEN
           return_v := 'Max. length exceeded';
       END IF;

       ----Other validations (on mandatory and data type)

    END LOOP; 
    CLOSE column_c;

  END LOOP;

  RETURN return_v;

END CheckData;

Problem:

The problem I am facing here is in declaring column_v variable for sys_refcursor column_c. Since at this point of time I am not able to think replacement for sys_refcursor, is there anything else I can do?

Answer 1

This function worked in simple tests. It checks nulls and length, you have to add rest of validations.

create or replace function CheckData(i_viewname VARCHAR2)
RETURN VARCHAR2
Is
  query_v       VARCHAR2(2000);
  v_cnt number := 0;

  CURSOR ddc_c IS 
    SELECT * FROM check_data_column where viewname = i_viewname;

BEGIN
  FOR ddc_v IN ddc_c LOOP

    -- check nulls
    if ddc_v.mandatory = 1 then
      query_v := 'select count(1) from '|| ddc_v.viewname 
        ||' where '||ddc_v.columnname||' is null';
      execute immediate query_v into v_cnt;
      if v_cnt > 0 then
        return 'null values for mandatory column '
          ||ddc_v.viewname ||'.'||ddc_v.columnname||' exists';
      end if;
    end if;

    -- check column length
    query_v := 'select count(1) from '|| ddc_v.viewname 
      ||' where length('||ddc_v.columnname||') > '||ddc_v.maxlength;
    execute immediate query_v into v_cnt;
    if v_cnt > 0 then
      return 'max length in column '||ddc_v.viewname
        ||'.'||ddc_v.columnname||' exceeded';
    end if;

    -- other validations

  END LOOP;

  RETURN 'OK';

END CheckData;