CODEWITHSUNDEEP
oracle-databaseplsql
user8590010
user8590010

Reputation:

How to spool the result of this pl/SQL Procedure

So that is my code and i have a cursor and i want to spool the result of this cursor taht you can see in the dbms_output.

set serveroutput on
clear screen;

spool C:\Users\p.k\Documents\text.txt

declare
    l_output   utl_file.file_type;
    cursor cp_username (p_v_username in varchar2)
    is
       select owner, table_name          
       from   all_tables
       where owner = p_v_username
       order by owner, table_name ;   
begin
     l_output := utl_file.fopen();
     dbms_output.put_line('Alle Tabellen der User: &g_username');
     for cp_kur in cp_username('&g_username') loop
         dbms_output.put_line('Tabelle: '|| cp_kur.table_name );
     end loop;
end;

spool off;

i have started using the spool first of all but he has create the file and has putted the output with the error and then i started using the utl_file looking at a tutorial...

Upvotes: 1

Views: 774

Answers (1)

Popeye
Popeye

Reputation: 35930

You need to use utl_file.put as follows:

declare
    l_output   utl_file.file_type; 
begin
     l_output := utl_file.fopen(
                  'utl_dir' -- File location
                , 'test_file.txt' -- File name
                , 'w' -- Open mode: w = write.
                    );
     utl_file.put(l_output,'Alle Tabellen der User: &g_username');
     for cp_kur in (select owner, table_name          
       from   all_tables
       where owner = '&g_username'
       order by owner, table_name) loop
         utl_file.put(l_output, 'Tabelle: '|| cp_kur.table_name );
     end loop;
     utl_file.fclose(l_output);
end;

Upvotes: 1

Related Questions