plsql dynamic sql - weird error in repeated execution where contents differ in each loop

I have a feeling i might know whats going on, but can any-one answer this anyway ?

Code :-

I'm trying to select a different column ( may be char or non-char ) into a char variable each time around the loop. When the actual database column is a char type i get a :-

 ORA-01722: invalid number

error.

code extract is :-

        loop

....................
				
		if lThisDataType = 2  /* numeric */
		then
			sFunc			:= ' ' ;
			sEndFunc		:= ' ' ;
		else
			sFunc			:= 'to_char(' ;
			sEndFunc		:= ')' ;
		end if ;

		execute immediate 'select 	' || sFunc ||
						sThisColumn 
					  || sEndFunc || ' ' ||


			       'from	' || sThisTable  || ' ' ||

			       'where	' || sThisColumn || ' ' || ' = :b1 '

				into	sThisColumnValue 
				using 	recContactAudit.LENTITYINSTANCEKEY ;

...........

end loop

Thanks, Mark. Received on Thu Jul 31 2003 - 04:52:28 CDT