Thursday, 7 July 2011

Remove special CH from Temp table

declare
TYPE cur_typ IS REF CURSOR;
c_rec cur_typ;
l_table_name varchar2(1000);
l_column_name varchar2(1000);
query_str varchar2(1000);
update_query_str varchar2(1000);
begin
l_table_name:='TEMP_TABLE_NAME';
query_str := 'SELECT column_name FROM ALL_COL_COMMENTS where table_name =:p_table_name ';
begin
OPEN c_rec FOR query_str using l_table_name ;
LOOP
FETCH c_rec INTO l_column_name;
EXIT WHEN c_rec%NOTFOUND;
dbms_output.put_line(l_table_name||' '||l_column_name);
update_query_str:='update '||l_table_name||' set "'||l_column_name ||'" = substr("'||l_column_name||'",1, instr("'||l_column_name||'",CHR(13))-1) where instr("'||l_column_name||'",CHR(13)) <>0' ;
--update_query_str:='update '||l_table_name||' set "'||l_column_name ||'" = trunc(;
EXECUTE IMMEDIATE update_query_str ;
END LOOP;
CLOSE c_rec;
end;
end;

No comments:

Post a Comment