Re: pl*sql errors...

Check if output directory exists and accessible for user oracle.

In article <36E8BA06.17067D45_at_ns.gnuh.co.kr>,   ksjune <ksjune_at_ns.gnuh.co.kr> wrote:
> 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;
> /
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 12 1999 - 21:29:24 CST