DECLARE

  v_emprecs  emp_util.emprec_tab_t; 
  --cur_1        SYS_REFCURSOR;
  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);

  sqlCommand sqlType;
  cur_1 curtype;
  p_tier_id NUMBER := 2;
  

  
  max_rows       CONSTANT NUMBER := 100; 

--  CURSOR cur_1 (p_tier_id NUMBER) IS 
--            SELECT *
--                FROM ALL_ORGNAMES_FROM_ORGS_TABLE a
--				WHERE exists (SELECT 'x' FROM 'tier'||p_tier_id||'_orgs' b
--					  		  WHERE a.org_name like b.org_name
--							 ) 
 

							 
   sqlCommand := 'SELECT * FROM ALL_ORGNAMES_FROM_ORGS_TABLE a WHERE exists (SELECT ''x'' FROM ''tier'''||

||'''_orgs'' b WHERE a.org_name like b.org_name );';							 							 
							 							 		
BEGIN
  OPEN cur_1 FOR SQLCommand;
  LOOP  
    EXIT WHEN cur_1%NOTFOUND;
    FETCH cur_1 BULK COLLECT INTO v_emprecs LIMIT max_rows;
    FORALL i IN 1..org.COUNT
      INSERT INTO competitor_orgs_all (market_share_tier, org_id, org_name, )
         VALUES(p_tier_id, org.org_id(i), org.org_name(i) );
   END LOOP;
 CLOSE cur_1;
END;

create table 		competitor_tier2_orgs nologging as
select distinct		org_id, org_name
from   				ALL_ORGNAMES_FROM_ORGS_TABLE
where				[B]org_name like 'ACTUATE%'[/B]
or					org_name like 'ARIBA%'
or 					org_name like '%BMC SOFTWARE%'
or 					org_name like 'BORLAND%' 
or 					org_name like 'BRIO%'
or					org_name like 'COMMERCE ONE%'
-- or					(org_name like 'COMPUTER SCIENCES CORP%' or org_name like 'CSC COMPUTER SCIENCES %'
-- 					or org_name in ('COMPUTER SCIENCES','CSC')) -- CONSULTING 
or					org_name like 'I2%'
or					org_name like 'INFORMATICA%'
or					(org_name like 'INFORMATION BUILDERS%' or org_name like 'IBI%')
or					org_name like 'JBOSS%'
or					org_name like 'MANUGISTICS%'
or					org_name like '%MICROSTRATEGY%'
or					(org_name like 'NATIONAL CASH REGISTER%' or org_name like '%NCR%' 
					or org_name like 'TERADATA%')
or					org_name like 'PLUMTREE SOFTWARE%'
or					org_name like 'QUEST%'
--					or org_name like '%QUEST - JD EDWARDS USERS GROUP%')
or					([B]org_name like 'QWEST %' 
					and org_name not like 'QWEST%DIAGNOSTIC%'
					and org_name not like 'QWEST%DENTAL%'
					and	org_name not like 'QWEST%FOODS%'
					and	org_name not like 'QWEST%AIR%PART%'
					and	org_name not like 'QWEST%BRIAN%ALBANO'[/B])
					/* QWEST COMMUNICATIONS - not direct competitor or in Oracle's space */
or					org_name like 'RATIONAL%' 
-- or					(org_name like 'SIEMENS BUSINESS SERVICE%') -- CONSULTING; PRIMARY INDUSTRY IS TELECOMMUNICATIONS --
or					org_name like 'SILVERSTREAM%'
or					org_name like 'SUN MICRO%SYSTEM%'
or					(org_name like '%SYBASE%' )
order by			org_name asc
;

alter table			competitor_tier2_orgs
add	  				(competitor_name		  varchar2(500),
					mkt_share_tier		   		varchar2(3))
;


update				competitor_tier2_orgs
set					competitor_name = 'ACTUATE'
where				org_name like 'ACTUATE%'
;

sqlCommand2 := '';
FOR l_lookup in (SELECT competitor_name, org_like, org_not_like
                   FROM <lookup_table_name>
                   WHERE p_tier_id = p_tier_id -- oops column and variable names equal, you should change one of them)
                ) LOOP
   -- fill sqlCommand2 from l_lookup.org_like and l_lookup.org_not_like
   -- maybe it would help to use REPLACE( l_lookup.org_not_like, '|', ''' and org_name not like ''' )
END LOOP;
sqlCommand2 := substr(sqlCommand2, 1, length(sqlCommand2)-<appropriate_length>);
dbms_output.put_line(sqlCommand2);  -- just show the content

CREATE OR REPLACE PROCEDURE COMPETITOR_ORGS_LOOKUP IS
  
  org tier_orgs%ROWTYPE;
  
  TYPE nest_tab_t IS TABLE OF NUMBER;
  TYPE nest_tab_t2 IS TABLE OF VARCHAR;
  nt   nest_tab_t2 := nest_tab_t2(); 
  nt_comp nest_tab_t2 := nest_tab_t2();
  nt_tier_id  nest_tab_t := nest_tab_t();
  nt2  nest_tab_t2 := nest_tab_t2();
  nt2_comp nest_tab_t2 := nest_tab_t2();
  nt2_tier_id  nest_tab_t := nest_tab_t();
  ret  VARCHAR2(100) := NULL;
   
  
  TYPE orgnames_t IS TABLE OF ALL_ORGNAMES_FROM_ORGS_TABLE%ROWTYPE; 
  v_orgrecs    orgnames_t;
  cur_1        SYS_REFCURSOR;
  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);

  sqlCommand sqlType;
  sqlCommand2 sqlType;

  
  objcur curtype;
  cur1 	curtype;
  cur2  curtype;

  oname VARCHAR2(100);
  --oname2 VARCHAR2(200); 

  p_tier_id NUMBER := 2;


-- code_text varchar2(2000):=      'begin split_vc2; end;'

  
  max_rows       CONSTANT NUMBER := 100; 


							 							 		
BEGIN

  sqlCommand2 := 'SELECT * FROM tier_orgs';							 
						 							  
   sqlCommand := 'SELECT func_sys_refcursor( cursor ( select :cur_1, :cur_2  from dual ) ) INTO cur_1 from dual' ;

	OPEN cur2 FOR sqlCommand2;
	FETCH cur2 INTO ORG;
	
	  FOR i IN 1..100 LOOP 
	    nt.EXTEND; 

		IF i = 1 THEN 		SELECT 'org_name like' || parse(org.org_like, i), org.competitor_name, org.p_tier_id
		   		 			into nt(i), nt_comp(i), nt_tier_id (i) FROM DUAL; 
		ELSE   	 			SELECT 'or org_name like' || parse(org.org_like, i), org.competitor_name, org.p_tier_id
							into nt(i), nt_comp(i), nt_tier_id (i) FROM DUAL;
		END IF;
	  END LOOP; 

  	  FOR j IN 1..100 LOOP 
	    nt2.EXTEND; 
					  	   SELECT 'and org_name not like' || parse(org.org_like, j), org.competitor_name, org.p_tier_id
						   into nt2(j), nt2_comp(j), nt2_tier_id (j) FROM DUAL; 
	  END LOOP;  
	  
	  
   CLOSE cur2;
   
   
   IF nt.count != 0 THEN
   
	   For i IN 1..nt.count LOOP
	   	   
		   	  ret := ret || nt(i);
			  
	   END LOOP;
	   	   
		   IF (nt2 IS NULL) THEN 
		   	  ret := 'WHERE ( ' || ret || ' )';
		   ELSE 
		   
		   		For j IN 1..nt2.count LOOP
					  
					ret := ret || nt2(j);
				END LOOP;
		
			ret := 'WHERE ( ' || ret || ' )';   
		   END IF;
   
   END IF;

   	 
  OPEN objcur FOR SQLCommand;	 
  
  
 LOOP  
    EXIT WHEN objcur%NOTFOUND;

  FETCH objcur INTO oname;

  
   execute immediate oname USING 'SELECT org_id, org_name FROM ALL_ORGNAMES_FROM_ORGS_TABLE ', ret;
   
  CLOSE objcur;
  
    FETCH cur_1 BULK COLLECT INTO v_orgrecs LIMIT max_rows;
    FORALL i IN 1..v_orgrecs.COUNT
      INSERT INTO competitor_orgs_all(ORG_ID,ORG_NAME,COMPETITOR_NAME,MKT_SHARE_TIER	) --(market_share_tier, org_id, org_name)
         VALUES ( v_orgrecs(i), org.competitor_name, org.p_tier_id)  ;--(p_tier_id, v_orgrecs.org_id, v_orgrecs.org_name(i) );
   END LOOP;
  CLOSE cur_1; 
   
  
END;

/*This is an additional function I wrote to accomplish what I wanted above.*/

create or replace function func_sys_refcursor (
  rc in sys_refcursor
) return VARCHAR as

  v_a VARCHAR2(100);
  v_b varchar2(100);

  v_ret VARCHAR2(200) := NULL;

begin

  loop
    fetch rc into v_a, v_b;
    exit when rc%notfound;
	
	  v_ret := v_a ||' ' || v_b;
  end loop;

  return v_ret;
end;
/


/* ..and one more parse function to parse my strings in the nested tables I have created.*/

CREATE OR REPLACE FUNCTION parse (par_str IN CHAR, par_n IN NUMBER) 
  RETURN CHAR 
IS
  SUBSTR VARCHAR2(100);
BEGIN
  SELECT SUBSTR(par_str, 
                INSTR(par_str, '|', 1, par_n - 1) + 1,
                INSTR(par_str, '|', 1, par_n) - INSTR(par_str, '|', 1, par_n - 1) - 1
               )
  INTO SUBSTR
  FROM dual;
                    
  RETURN trim(SUBSTR);
END;
/