Pages

Tuesday, June 23, 2009

Quick PS Util - Display Table data in order of Column Name

When using Toad or other tools that displays results in a grid that can be scrolled from left to right, and there are many columns, I usually have to to go back and forth a few times to find a column. Some of the solutions to this --

1. creataing a function returns a sql that displays fields in the order of filed name:
CREATE OR REPLACE function  fo(table_in IN varchar2)RETURN varchar2
IS
sql1 varchar2(6000);
cnt integer;
rec_name varchar2(30);

cursor c1 is
select column_name as fieldname from all_tab_columns where TABLE_NAME=rec_name order by column_name;

BEGIN

cnt :=0;
sql1 :='select ';
rec_name :='PS_'||upper(table_in);

FOR zz IN c1 LOOP
sql1:=sql1 || zz.fieldname||',';
cnt:=cnt+1;
END LOOP;

if cnt = 0 then

rec_name := upper(table_in);

FOR zz IN c1 LOOP
sql1:=sql1 || zz.fieldname||',';
cnt:=cnt+1;
END LOOP;
end if;

if cnt = 0 then
return table_in ||' columns not found';
end if;

sql1 := sql1 || '''---'' from ' || rec_name ;

RETURN sql1 ;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Errro - '||SQLCODE||' -ERROR- '||SQLERRM);

END;
/

show error function fo


Ex:
  • select fo('installation') from dual -- default to 'PS_'
  • select fo('psrecdefn') from dual
  • select fo('all_objects') from dual