Re: pl*sql errors...

It sounds like you do not have the priviledges to run UTL_FILE. Ask your DBA to give you the role EXECUTE_ANY_PROCEDURE or grant you the rights directly.

Mark

ksjune wrote in message <36E8BA06.17067D45_at_ns.gnuh.co.kr>...
>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
>========================================
>Here is make_sql.sql script
>========================================
>
>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;
> end c_script;
>begin
> 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, ';');
> end loop;
> close c_tname;
> utl_file.fclose(v_fd);
>end full_table;
>end make_sql;
>/
>
Received on Fri Mar 12 1999 - 02:59:04 CST