SQL & PL/SQL

Hi Grues,

I have written a procedure and aimed to take data from a table and check it in exception table, if it exists in exception table then replace string, the following example will make it easy to understan:

replace(colname, 'abc','ABC')

here colname is passed parameter and instead of abc i have passed exception colname

create or replace procedure pr_tit 
		(pr_tablename in varchar2,
		 pr_colname   in varchar2)

AUTHID CURRENT_USER
IS
   TYPE FIELDCurTyp IS REF CURSOR;

   exception_cur   FIELDCurTyp;
   tab_cur         FIELDCurTyp;
	
   sqlSt       VARCHAR2 (500);
   theField    VARCHAR2 (500);
   theField1    VARCHAR2 (500);
  

begin


	sqlst:='select IDX, '||pr_colname|| ' from '|| pr_tablename ;

	open tab_cur for sqlst;

	loop

		FETCH tab_cur INTO theField;

      		EXIT WHEN tab_cur%NOTFOUND OR tab_cur%NOTFOUND IS NULL;
	
		
		sqlst:='select acronym from poi_shared.sh_poi_acronyms';

		open exception_cur for sqlst;

			loop 
				FETCH exception_cur INTO theField1;

      				EXIT WHEN exception_cur%NOTFOUND OR exception_cur%NOTFOUND IS NULL;

				if instr(thefield,thefield1)>0 then 
			
					thefield:=replace(thefield, thefield1, upper(thefield1));
					dbms_output.put_line(thefield);

				else
					null;

				end if;


				--dbms_output.put_line(thefield1);

			end loop;

		

		--dbms_output.put_line(thefield);

	end loop;

	EXCEPTION WHEN NO_DATA_FOUND THEN 

		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');


		WHEN OTHERS THEN 

		DBMS_OUTPUT.PUT_LINE(SQLERRM);

		

end;

/

Best Regards,
Mich