--CONNECT system/manager@whatever
set serveroutput on size 100000
set linesize 2000
create or replace package etl_util AUTHID CURRENT_USER
IS
   FUNCTION get_col_names(p_schema IN VARCHAR2, p_table_name IN VARCHAR2, p_skip_columns IN VARCHAR2, p_pref IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
   FUNCTION get_cols_definition(p_schema IN VARCHAR2, p_table_name IN VARCHAR2, p_skip_columns IN VARCHAR2, p_pref IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
   FUNCTION get_modified_col_names(schema IN VARCHAR2, table_name IN VARCHAR2, source_colmap$ IN RAW)  RETURN VARCHAR2;
END etl_util;
/
show errors
create or replace package body etl_util
IS
-- ******************************************************************************
-- PUBLIC FUNCTION get_col_names
-- ******************************************************************************
  FUNCTION get_col_names(p_schema IN VARCHAR2, p_table_name IN VARCHAR2, p_skip_columns IN VARCHAR2, p_pref IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2	
	IS
  BEGIN
	DECLARE 
	   v_col_nm VARCHAR2(100);
	   v_ret VARCHAR2(32767);
	   v_work_str VARCHAR2(32767);

	   CURSOR c_tab_col(p_tbl_nm VARCHAR2, p_schm VARCHAR2) IS        
			SELECT column_name FROM all_tab_columns
			WHERE owner = UPPER(TRIM(p_schm))
			AND table_name = UPPER(TRIM(p_tbl_nm))
			ORDER BY column_name;

  BEGIN
   OPEN c_tab_col(p_table_name, p_schema);
   v_ret := '';
   v_work_str := ','||LOWER(REPLACE(p_skip_columns,' ','')) || ',';

   LOOP
      FETCH c_tab_col INTO v_col_nm;
      EXIT WHEN c_tab_col%NOTFOUND;
	IF INSTR(v_work_str,','||LOWER(v_col_nm)) = 0 THEN
		v_ret := v_ret || ',' || p_pref ||LOWER(v_col_nm);
	END IF;
   END LOOP;
   CLOSE c_tab_col;
   IF (v_ret IS NOT NULL) THEN v_ret := SUBSTR(v_ret, 2);
   END IF;
   IF (v_ret IS NULL) THEN
	   raise_application_error(-20201,'Table ' || p_schema||'.'||p_table_name 
		|| ' Does not exist or has no columns');
   END IF;

   RETURN v_ret;
  END;
  END;
-- ******************************************************************************
-- END OF PUBLIC FUNCTION get_col_names
-- ******************************************************************************

-- ******************************************************************************
-- PUBLIC FUNCTION get_cols_definition
-- ******************************************************************************
  FUNCTION get_cols_definition(p_schema IN VARCHAR2, p_table_name IN VARCHAR2, p_skip_columns IN VARCHAR2, p_pref IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2	
	IS
  BEGIN
	DECLARE 
	   v_col_nm VARCHAR2(100);
	   v_col_def VARCHAR2(300);
	   v_ret VARCHAR2(32767);
	   v_work_str VARCHAR2(32767);
	   CURSOR c_tab_col(p_tbl_nm VARCHAR2, p_schm VARCHAR2) IS        
		SELECT column_name,
			RPAD(column_name,53) ||
		 decode(data_type
			,'NUMBER','NUMBER('||TO_CHAR(data_precision)||
				decode(data_scale,0,'',','||data_scale)||')'
			,'CHAR','CHAR('||TO_CHAR(DATA_LENGTH)||')'
			,'VARCHAR2','VARCHAR2('||TO_CHAR(DATA_LENGTH)||')'
			,'DATE','DATE'
			,data_type) def
		from all_tab_columns 
		where table_name = upper(trim(p_tbl_nm))
			AND owner = upper(trim(p_schm))
		order by owner, table_name, column_name
		;
  BEGIN
   OPEN c_tab_col(p_table_name, p_schema);
   v_ret := '';
   v_work_str := ','||LOWER(REPLACE(p_skip_columns,' ','')) || ',';

   LOOP
      FETCH c_tab_col INTO v_col_nm, v_col_def;
      EXIT WHEN c_tab_col%NOTFOUND;
	IF INSTR(v_work_str,','||LOWER(v_col_nm)) = 0 THEN
		v_ret := v_ret || ', ' || p_pref ||LOWER(v_col_def);
	END IF;
   END LOOP;
   CLOSE c_tab_col;
   IF (v_ret IS NOT NULL) THEN v_ret := SUBSTR(v_ret, 2); END IF;
   IF (v_ret IS NULL) THEN
	   raise_application_error(-20201,'Table ' || p_schema||'.'||p_table_name 
		|| ' Does not exist or has no columns');
   END IF;

   RETURN v_ret;
   exception
		  when others
			then dbms_output.put_line(SUBSTR('Error in get_cols_definition ' || sqlerrm,1,220));
   return 'error ' || sqlerrm ;
  END;
  END;
-- ******************************************************************************
-- END OF PUBLIC FUNCTION get_cols_definition 
-- ******************************************************************************

-- ******************************************************************************
-- PUBLIC FUNCTION get_modified_col_names 
-- ******************************************************************************
   FUNCTION get_modified_col_names(schema IN VARCHAR2, table_name IN VARCHAR2, source_colmap$ IN RAW) RETURN VARCHAR2
	IS
   BEGIN
	DECLARE 
	   v_col_nm VARCHAR2(100);
	   v_col_pos number;
	   v_ret VARCHAR2(32767);
	   v_work_str VARCHAR2(32767);
     v_byte_nr INTEGER;
     v_col_map VARCHAR2(32767);
     i INTEGER; j INTEGER;k INTEGER;
     v_val VARCHAR2(5);
	   CURSOR c_tab_col(p_tbl_nm VARCHAR2, p_schm VARCHAR2) IS        
		SELECT column_name, POWER(2,MOD(column_id,8)) col_pos, 1 + floor((column_id)/8) byte_nr 
			FROM all_tab_cols 
			WHERE table_name=UPPER(p_tbl_nm) 
				AND OWNER=UPPER(p_schm)
			ORDER BY column_id;		
   BEGIN
	   SELECT dump(source_colmap$,10) INTO v_col_map FROM DUAL;
	   i:=INSTR(v_col_map,':');
	   v_col_map:=','||SUBSTR(v_col_map,i + 2)||',';
--	dbms_output.put_line('v_col_map='||v_col_map);
	   OPEN c_tab_col(table_name, schema);
	   v_ret := '';
  	 LOOP
--	dbms_output.put_line('--1');
	      FETCH c_tab_col INTO v_col_nm, v_col_pos, v_byte_nr;
	      EXIT WHEN c_tab_col%NOTFOUND;
--	dbms_output.put_line('byte_nr='||to_char(v_byte_nr));
	      i:=INSTR(v_col_map,',',1,v_byte_nr);
	      j:=INSTR(v_col_map,',',1,v_byte_nr + 1);
	      IF ((i=0) OR (j = 0)) THEN
	          EXIT;
	      END IF;
	      v_val := SUBSTR(v_col_map, i + 1, j - i - 1 );
	      k := BITAND(TO_NUMBER(v_val),v_col_pos);
	      IF (k>0) THEN
	     	   v_ret := v_ret || ',' || v_col_nm;
	      END IF;
--     DBMS_OUTPUT.put_line('col='||v_col_nm ||' byte='||TO_CHAR(v_byte_nr)
--          || ' i=' || TO_CHAR(i) || ' j='||TO_CHAR(j)
--          ||' val='||v_val||' col_pos='||TO_CHAR(v_col_pos)||'res='||TO_CHAR(k));
	   END LOOP;
	   CLOSE c_tab_col;
	   IF (v_ret IS NOT NULL) THEN
	      v_ret := SUBSTR(v_ret,2);
	   END IF;
	   RETURN v_ret;
   EXCEPTION
		 WHEN OTHERS
			THEN dbms_output.put_line(SUBSTR('Error in get_modifed_col_names: ',1,220));
			     dbms_output.put_line(SUBSTR(sqlerrm,1,240));
	   return '';
  END;
  END;
-- ******************************************************************************
-- END OF PUBLIC FUNCTION get_modified_col_names 
-- ******************************************************************************



END etl_util;
/
show errors
grant execute on etl_util to public;
DROP PUBLIC SYNONYM etl_util;
CREATE PUBLIC SYNONYM etl_util FOR etl_util;
--select etl_util.get_modified_col_names(schema1 => 'SCOTT'
--, table_name => 'EMP'
--, source_colmap$ => HEXTORAW('C000')) FROM dual;
--
BEGIN
declare v_res VARCHAR2(1000);
BEGIN
	v_res := etl_util.get_modified_col_names('SCOTT', 'EMP', HEXTORAW('0601'));
	dbms_output.put_line('res='||v_res);
END;
END;
/