Re: pl*sql errors...

You may also need to check with your dba to find what directories you have access to. In the init.ora file the dba specifies the utl_file_dir paramter which limits what the utl_file package can write/read from.

Patrick Flahan
flahan_at_earthlink.net

Mark Gumbs <mgumbs_at_hotmail.com> wrote in message news:36e8d607.0_at_145.227.194.253...
>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 - 09:58:42 CST