Hi I am new to using pl/sql on a unix platform and am having trouble writing to a file from within a block. Below is an example of the code that I have. I know that I need to use UTL_FILE to accomplish this; however, I keep getting errors. Can someone please help me?
I am trying to create a csv file of tables name from a schema. However, I cannot figure out how to write the table names into a csv file after getting the user input.
Any help would be appreciated.
Thanks.
prompt Enter Table owner;
accept user_schema char prompt 's (SYSTEM), m (WMSYS), b (Both): ';
declare
user_input varchar2 (40) := '&user_schema';
type t_names is table of all_tables.TABLE_NAME%type;
all_table_names t_names;
begin
if user_input = 'w' then
user_input := '''WMSYS''';
select table_name bulk collect into all_table_names from all_tables where owner = user_input;
else
user_input := '''SYSTEM''';
select table_name bulk collect into all_table_names from all_tables where owner = user_input;
end if;
end;
/