Oracle re Create table with max length

dynamic plsql example






set serverout on
set feedback off
set verify off

accept v_table prompt 'enter table name : '

declare

type r_c is ref cursor;
c_ref r_c;
v_cname col.cname%type;
t_table varchar2(30);
v_table varchar2(30);
t_command varchar2(200);
t_cid integer;
t_val number(10);
stat integer;
row_count integer;
tot_rows number;
tot_col number;

begin

t_table := '&v_table';
open c_ref for select cname from col where tname=t_table ;
select count(*) into tot_col from col where tname=t_table ;
dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
dbms_output.put_line(' ');

dbms_output.put('select ');

loop
fetch c_ref into v_cname;
exit when c_ref%NOTFOUND;
tot_rows:= c_ref%ROWCOUNT;

t_command := 'select max(length("'||v_cname|| '")) from &v_table';
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_val);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_val);

if t_val is null then
t_val := 1;
end if;

dbms_output.put('substr('||v_cname||',1,'||t_val||') ' ||v_cname );
if tot_rows < tot_col then
dbms_output.put_line(' ,' );
else
dbms_output.put_line(' from "'||t_table||'"' );
end if;

end loop;
close c_ref;

dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
dbms_output.put_line(' . ');
end;
/
undefine t
set feedback on
set verify on



SQL> @tab
enter table name : EMP
.
.
.
select substr(EMPNO,1,4) EMPNO ,
substr(ENAME,1,6) ENAME ,
substr(JOB,1,9) JOB ,
substr(MGR,1,4) MGR ,
substr(HIREDATE,1,9) HIREDATE ,
substr(SAL,1,4) SAL ,
substr(COMM,1,4) COMM ,
substr(DEPTNO,1,2) DEPTNO from "EMP"