| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> pl*sql errors...
Hi !!!! everyone...
I'd like to make scription of table script.
When I lost table creation script, I'll make table creation script using
below PL*SQL code.
But I got a problem.
I have executed like this.
SQL>@make_sql
Package created.
Package body dropped.
Package body created.
SQL> exec make_sql.full_table('table_name');
but I have error messages...
Could you explain me what's the problem?
Package created.begin make_sql.full_table('ocsipslh'); end;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 82
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "TESTDBA.MAKE_SQL", line 91
ORA-06512: at line 1
N.B: When I use DBMS_OUTPUT.PUT_LINE, It works fine.
But I use UTL_FILE.PUT_LINE occurs errors....
ksjune_at_ns.gnuh.co.kr
create or replace package make_sql
as
procedure full_table(intab in varchar2);
end make_sql;
/
drop package body make_sql;
create package body make_sql
as
procedure full_table(intab in varchar2)
is
v_tname user_tables.table_name%TYPE;
v_tablespace_name user_tables.tablespace_name%TYPE;
v_pct_free user_tables.pct_free%TYPE;
v_pct_used user_tables.pct_used%TYPE;
v_ini_trans user_tables.ini_trans%TYPE;
v_initial_extent user_tables.initial_extent%TYPE;
v_next_extent user_tables.next_extent%TYPE;
v_min_extents user_tables.min_extents%TYPE;
v_pct_increase user_tables.pct_increase%TYPE;
v_freelists user_tables.freelists%TYPE;
v_freelist_groups user_tables.freelist_groups%TYPE;
v_fd utl_file.file_type;
cursor c_tname
is
select table_name, tablespace_name,
pct_free, pct_used, ini_trans,
initial_extent, next_extent, min_extents,
pct_increase, freelists, freelist_groups
from user_tables
where table_name like upper(intab)||'%';
procedure c_script(tab in varchar2) is
v_column_name user_tab_columns.column_name%TYPE;
v_data_type user_tab_columns.data_type%TYPE;
v_data_length user_tab_columns.data_length%TYPE;
v_data_precision user_tab_columns.data_precision%TYPE;
v_data_scale user_tab_columns.data_scale%TYPE;
v_nullable user_tab_columns.nullable%TYPE;
v_default_length user_tab_columns.default_length%TYPE;
v_default_length user_tab_columns.default_length%TYPE;
v_data_default user_tab_columns.data_default%TYPE;
v_col varchar2(60);
v_rowcount integer;
v_count integer := 0;
cursor c_cname
is
select column_name, data_type, data_length,
data_precision, data_scale, nullable,
nvl(default_length,0), data_default
from user_tab_columns
where table_name = upper(tab)
order by column_id;
begin
select max(column_id)
into v_rowcount
from user_tab_columns
where table_name = upper(tab);
open c_cname;
loop
v_count := v_count + 1;
fetch c_cname into v_column_name, v_data_type,
v_data_length,
v_data_precision, v_data_scale,
v_nullable,
v_default_length, v_data_default;
exit when c_cname%NOTFOUND;
if v_data_type = 'NUMBER'
then
if v_data_scale = 0
then
v_col := v_column_name||' '||v_data_type||
'('||v_data_precision||')';
else
v_col := v_column_name||' '||v_data_type||'('||
v_data_precision||','||v_data_scale||')';
end if;
else
v_col := v_column_name||' '||v_data_type||
'('||v_data_length||')';
end if;
v_col := rpad(v_col,25,' ');
if v_default_length > 0
then
v_col := v_col||'default '||v_data_default;
end if;
v_col := rpad(v_col, 40, ' ');
if v_nullable = 'N'
then
v_col := v_col||'not null';
end if;
v_col := rpad(v_col,50,' ');
if v_count <> v_rowcount
then
v_col := v_col||',';
end if;
utl_file.put_line(v_fd, v_col);
end loop;
v_fd := utl_file.fopen('./','full_table.sql','W');
open c_tname;
loop
fetch c_tname into v_tname, v_tablespace_name,
v_pct_free, v_pct_used,
v_ini_trans,
v_initial_extent, v_next_extent,
v_min_extents,
v_pct_increase, v_freelists,
v_freelist_groups;
exit when c_tname%NOTFOUND;
utl_file.put_line(v_fd, 'create table '||v_tname);
utl_file.put_line(v_fd, '(');
c_script(v_tname);
utl_file.put_line(v_fd, ')');
v_initial_extent := floor(v_initial_extent/1048576);
v_next_extent := floor(v_next_extent/1048576);
utl_file.put_line(v_fd, 'pctfree '||v_pct_free);
utl_file.put_line(v_fd, 'pctused '||v_pct_used);
utl_file.put_line(v_fd, 'initrans '||v_ini_trans);
utl_file.put_line(v_fd, 'tablespace '||v_tablespace_name);
utl_file.put_line(v_fd, 'storage (');
utl_file.put_line(v_fd, ' initial
'||v_initial_extent);
utl_file.put_line(v_fd, ' next
'||v_next_extent);
utl_file.put_line(v_fd, ' pctincrease
'||v_pct_increase);
utl_file.put_line(v_fd, ' minextents
'||v_min_extents);
utl_file.put_line(v_fd, ' freelists
'||v_freelists);
utl_file.put_line(v_fd, ' freelist groups
'||v_freelist_groups);
utl_file.put_line(v_fd, ' )');
utl_file.put_line(v_fd, ';');
 |
 |