Programming

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;
/

You need to read the Oracle docset, what you are asking is more than we can do in the forum: