Friday, August 14, 2015

Create CSV File from table

Your Ad Here



CREATE OR REPLACE PROCEDURE CREATE_CSV (p_query IN VARCHAR2)
AS
   l_cur              PLS_INTEGER;
   l_cur_rows         PLS_INTEGER;
   l_column_cnt       PLS_INTEGER;
   l_desc_tab         DBMS_SQL.desc_tab;
   l_buffer           VARCHAR2 (32767);
   l_file             UTL_FILE.file_type;
   l_string           VARCHAR2 (15);


   g_record_cnt       NUMBER := 0;
   g_directory_path   VARCHAR2 (500) := '/usr/tmp'; -- path must be present in utl_file_dir of v$parameter
   g_file_name        VARCHAR2 (250) := 'ra_terms';
   g_seperator        VARCHAR2 (5) := ',';
BEGIN
   g_record_cnt := 0;

   DBMS_OUTPUT.PUT_LINE (' Start writing in CSV file :' || g_file_name);

   l_cur := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (l_cur, p_query, DBMS_SQL.native);
   DBMS_SQL.describe_columns (l_cur, l_column_cnt, l_desc_tab);

   FOR i IN 1 .. l_column_cnt
   LOOP
      DBMS_SQL.define_column (l_cur,
                              i,
                              l_buffer,
                              32767);
   END LOOP;

   l_cur_rows := DBMS_SQL.EXECUTE (l_cur);
   l_file :=
      UTL_FILE.fopen (g_directory_path,
                      g_file_name || '.csv',
                      'w',
                      32767);

   FOR i IN 1 .. l_column_cnt
   LOOP
      IF i > 1
      THEN
         UTL_FILE.put (l_file, g_seperator);
      END IF;

      UTL_FILE.put (l_file, l_desc_tab (i).col_name);
   END LOOP;

   UTL_FILE.new_line (l_file);

   LOOP
      EXIT WHEN DBMS_SQL.fetch_rows (l_cur) = 0;

      FOR i IN 1 .. l_column_cnt
      LOOP
         IF i > 1
         THEN
            UTL_FILE.put (l_file, g_seperator);
         END IF;

         DBMS_SQL.COLUMN_VALUE (l_cur, i, l_buffer);

         IF INSTR (l_buffer, '"') != 0
         THEN
            SELECT REPLACE (l_buffer, '"', '""') INTO l_buffer FROM DUAL;
         END IF;

         IF INSTR (l_buffer, ',') != 0
         THEN
            l_string := '"';
            l_buffer := l_string || l_buffer || l_string;
         ELSE
            l_string := '';
         END IF;

         UTL_FILE.put (l_file, l_buffer);
      END LOOP;

      g_record_cnt := g_record_cnt + 1;
      UTL_FILE.new_line (l_file);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (
      ' Number Of rows inserted in CSV : ' || g_record_cnt);

   UTL_FILE.fclose (l_file);

   DBMS_OUTPUT.PUT_LINE (' End procedure');
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      IF DBMS_SQL.is_open (l_cur)
      THEN
         DBMS_SQL.close_cursor (l_cur);
      END IF;

      DBMS_OUTPUT.PUT_LINE (
         'Unexpected Error : ' || SUBSTR (SQLERRM, 1, 250));
END CREATE_CSV;

--------------------------------------------------------------------------------------------------

-- Call above procedure to generate CSV file data from ra_terms table

DECLARE
BEGIN
   create_csv ('SELECT * FROM ra_terms');
END;


Your Ad Here

1 comment:


  1. very interesting article i feel very enthusiastic while reading and the information provided in this article is so useful for me.content in this article guides in clarifying some of my doubts of oracle fusion financials


    Oracle Fusion HCM Online Training

    ReplyDelete