create table mapping (field_name varchar2(10), column_name varchar2(10) );

insert into mapping VALUES( 'FIELD_5' , 'CF_WATCH_LIST') ;

COMMIT;


CREATE TABLE STAGING (SID VARCHAR2(10) , FIELD_5 VARCHAR2(10);

INSERT INTO STAGING VALUES ('000558','LEGAL')
INSERT INTO STAGING VALUES ('001080','LEGAL')
INSERT INTO STAGING VALUES ('X10419','LEGAL')
INSERT INTO STAGING VALUES ('000631','HRCF')
INSERT INTO STAGING VALUES ('569237','HRCF')
INSERT INTO STAGING VALUES ('00656L','HRCF')

COMMIT;

COUNT(1)  FIELD_5
3         LEGAL
3         HRCF  

select COUNT(1), UPPER(FIELD.FIELD_NAME) FIELD,FIELD_5
              from STAGING XDS , (select 
                                         max( DECODE(COLUMN_NAME, 'CF_WATCH_LIST', FIELD_NAME)) AS FIELD_NAME
                                         FROM
                                           MAPPING
                                    WHERe COLUMN_NAME = 'CF_WATCH_LIST'  
                                     group by FIELD_NAME
                                      order by FIELD_NAME
                                       ) field
GROUP BY field.FIELD_NAME,FIELD_5;  

select count(*), field_5
from staging
group by field_5

SQL> create or replace function f_map
  2    return sys_refcursor
  3  is
  4    c            sys_refcursor;
  5    l_field_name mapping.field_name%type;
  6    l_str        varchar2(500);
  7  begin
  8    select field_name
  9      into l_field_name
 10      from mapping;
 11
 12    l_str := ' select count(*) cnt, ' || l_field_name ||
 13             ' from staging '         ||
 14             ' group by '             || l_field_name;
 15
 16    open c for l_str;
 17    return c;
 18  end;
 19  /

Function created.

SQL> select f_map from dual;

F_MAP
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

       CNT FIELD_5
---------- ----------
         3 LEGAL
         3 HRCF


SQL>

SQL> column column_name format A15;
SQL> SELECT *
  2  FROM   mapping;

FIELD_NAME COLUMN_NAME
---------- ---------------
FIELD_5    CF_WATCH_LIST

SQL>
SQL> SELECT *
  2  FROM   staging;

SID        FIELD_5
---------- ----------
000558     LEGAL
001080     LEGAL
X10419     LEGAL
000631     HRCF
569237     HRCF
00656L     HRCF

6 rows selected.

SQL>
SQL> SELECT cnt,
  2         field_5
  3  FROM   (SELECT Count(s.sid) cnt,
  4                 s.field_5    field_5,
  5                 m.field_name
  6          FROM   staging s,
  7                 mapping m
  8          GROUP  BY s.field_5,
  9                    m.field_name);

       CNT FIELD_5
---------- ----------
         3 LEGAL
         3 HRCF

SQL> column field_name noprint;
SQL> SELECT Count(s.sid) cnt,
  2                 s.field_5    field_5,
  3                 m.field_name field_name
  4          FROM   staging s,
  5                 mapping m
  6          GROUP  BY s.field_5,
  7                    m.field_name;

       CNT FIELD_5
---------- ----------
         3 LEGAL
         3 HRCF

s.field_5

SQL> create table mapping (field_name varchar2(10), column_name varchar2(10) );

Table created.

SQL> insert into mapping VALUES( 'FIELD_5' , 'CF_WATCH_LIST') ;
insert into mapping VALUES( 'FIELD_5' , 'CF_WATCH_LIST')
                                        *
ERROR at line 1:
ORA-12899: value too large for column "MICHEL"."MAPPING"."COLUMN_NAME" (actual: 13, maximum: 10)


SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE STAGING (SID VARCHAR2(10) , FIELD_5 VARCHAR2(10);
CREATE TABLE STAGING (SID VARCHAR2(10) , FIELD_5 VARCHAR2(10)
                                                            *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> INSERT INTO STAGING VALUES ('000558','LEGAL')
  2  INSERT INTO STAGING VALUES ('001080','LEGAL')
  3  INSERT INTO STAGING VALUES ('X10419','LEGAL')
  4  INSERT INTO STAGING VALUES ('000631','HRCF')
  5  INSERT INTO STAGING VALUES ('569237','HRCF')
  6  INSERT INTO STAGING VALUES ('00656L','HRCF')
  7  COMMIT;
INSERT INTO STAGING VALUES ('001080','LEGAL')
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> col field_name new_value field_name
SQL> select field_name from mapping where column_name='CF_WATCH_LIST';
FIELD_NAME
----------
FIELD_5

1 row selected.

SQL> select &field_name, count(*) 
  2  from staging
  3  group by &field_name
  4  /
FIELD_5      COUNT(*)
---------- ----------
LEGAL               3
HRCF                3

2 rows selected.

PROCEDURE validateMaxRecUpload (seqnum_v      GBX.XV_DATA_UPLOAD_FILES.seq#%TYPE, 
                                  field_name_v  MAPPING.FIELD_NAME%TYPE,
                                  column_name_v MAPPING.COLUMN_LABEL%TYPE)    

IS
      lv_cf_watchlist       VARCHAR2(30);
      lv_cf_regcf_cnt       NUMBER;
      lv_cf_regcfwatch      VARCHAR2(30);
      lv_cf_stgcf_cnt       NUMBER;
      lv_cf_stgcfwatch      VARCHAR2(30);
      lv_cnt                NUMBER;
      lv_cnt1               NUMBER;
      L_STR                 varchar2(500);
      
      CURSOR cur_regcf
      IS 
       SELECT COUNT(1), CF_WATCH_LIST
         FROM  CONTROL_FORUMS RCF
         WHERE CF_WATCH_LIST IN ('CF','HR','ER','RM') 
        GROUP BY CF_WATCH_LIST;

     CURSOR cur_stagingcf 
      IS 
       SELECT COUNT(1),lv_cf_watchlist
         FROM STAGING 
        WHERE lv_cf_watchlist IN ('CF','HR','ER','RM') 
          AND SEQ# = seqnum_v
        GROUP BY lv_cf_watchlist;   
    
    BEGIN
   
    IF COLUMN_NAME_V = 'CF_WATCH_LIST' THEN 
     
       BEGIN
        SELECT XDUM.FIELD_NAME
          INTO lv_cf_watchlist
          FROM MAPPING XDUM 
         WHERE XDUM.COLUMN_NAME = 'CF_WATCH_LIST'
           AND XDUM.SEQ# =seqnum_v
           AND MAPPED_FLAG='Y';
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
             lv_cf_watchlist := NULL;
      END;
    
       OPEN cur_regcf;
       LOOP 
         FETCH cur_regcf INTO lv_cf_regcf_cnt,lv_cf_regcfwatch;
         EXIT when CUR_REGCF%NOTFOUND;
         
         OPEN CUR_STAGINGCF ;
           
             FETCH cur_stagingcf into lv_cf_stgcf_cnt,lv_cf_stgcfwatch;
              EXIT when cur_stagingcf%NOTFOUND;
               LOOP     
                IF  LV_CF_REGCFWATCH = LV_CF_STGCFWATCH THEN                    
                    IF LV_CF_REGCFWATCH IS NOT NULL AND LV_CF_REGCFWATCH IN ('CF','HR','ER','RM') THEN 
                    
                    LV_CNT := 5 - LV_CF_REGCF_CNT ;
                    LV_CNT1:= LV_CF_STGCF_CNT - LV_CNT;
                       DBMS_OUTPUT.PUT_LINE('COUNT=' || LV_CNT1 || LV_CF_STGCFWATCH);
                     .............                      
                    END IF;   
                END IF;  
                                  
            end LOOP;
              CLOSE cur_stagingcf;
       END LOOP;
      CLOSE cur_regcf;
    end if;   
END validateMaxRecUpload