how to export output in a CSV format through a procedure

Question

How to export output in a CSV format through a procedure. My output is stored in v_output variable

Please find below query

   Declare
    view_name     VARCHAR2(200);
    v_str          VARCHAR2 (1000);
    v_output     VARCHAR2(4000);
    CURSOR tbl IS
         SELECT view_name 
         FROM all_views
         WHERE OWNER = SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA')
         ORDER BY 1 ;
    BEGIN
    OPEN tbl ;
         LOOP
         FETCH tbl INTO view_name;
         EXIT WHEN tbl%NOTFOUND;
              v_str := 'Select '''|| view_name ||','' || count (*) from ' || view_name ;
              EXECUTE IMMEDIATE v_str INTO v_output;

              DBMS_OUTPUT.PUT_LINE(v_output);
         END LOOP;
    CLOSE tbl;
    END;

**

current output :

V_DSP_BUSINESS_DATE,7
V_DSP_DEPARTMENT,0
V_DSP_EMPLOYEE_DEACTIVATED,515
V_DSP_EMPLOYEE_GED,0
V_DSP_EMP_DEPARTMENT,0

I want to export this output in a CSV format.

Answer 1

You can use UTL_FILE Package for writing to a flat file, this file will be generated on the DataBase Server.

PreRequisites for using UTL_FILE:

a. Create a Directory Object pointing to a physical location on the database. b. Ensure that the user/schema you are using has Read/Write access to this location c. Ensure that the UTL_FILE is installed on the database (run utl_file as SYS) and grant execute on the UTL_FILE to the account you are using.

Pseudo Code for UTL_FILE:

DECLARE
  view_name VARCHAR2 (200);
  v_str     VARCHAR2 (1000);
  v_output  VARCHAR2 (4000);
  CURSOR tbl IS
    SELECT   view_name
    FROM     all_views
    WHERE    owner = Sys_context ('USERENV', 'CURRENT_SCHEMA')
    ORDER BY 1;

l_filehandle utl_file.file_type%TYPE; --Create a Variable with  Filetype record
BEGIN
  l_filehandle := utl_file.fopen(<directory_object>, <filename>, 'W'); --Call to open the file for Write Operation
  OPEN tbl;
  LOOP
    FETCH tbl
    INTO  view_name;

    EXIT
  WHEN tbl%NOTFOUND;
    v_str := 'Select '
    || view_name
    || ',  count (*) from '
    ||view_name;
    EXECUTE IMMEDIATE v_str INTO v_output;
    utl_file.Put_line(l_filehandle,v_output); --Actual Writing of line infile
  END LOOP;
  CLOSE tbl;
  utl_file.Fclose(l_filehandle);
END;

Hope this helps