Export query result to csv in oracle stored procedure. The last column name is not getting printed

Question

So i have a query that i would like to execute through a stored procedure and export the output of the query to a CSV file. But only the last column name is not getting printed.So i am using the following stored procedure to do it:

create or replace PROCEDURE parseCSV(

p_file_name     VARCHAR2, -- filename
p_sql_query        VARCHAR2, -- select * from table or some such query
p_delimiter     CHAR,     -- column delimiter
p_file_dir         VARCHAR2 -- Oracle directory name
)
AS

    l_cursor_handle  INTEGER;
    l_dummy              NUMBER;
    l_col_cnt          INTEGER;
    l_rec_tab            DBMS_SQL.DESC_TAB;
    l_current_col      NUMBER(16);
    l_current_line   VARCHAR2(2047);
    l_column_value   VARCHAR2(300);
    l_file_handle      UTL_FILE.FILE_TYPE;
    l_print_text       VARCHAR2(100);
    l_record_count   NUMBER(16) := 0;

    BEGIN
       l_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'a', 2047); 
       l_cursor_handle := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(l_cursor_handle, p_sql_query, DBMS_SQL.native);
       l_dummy := DBMS_SQL.EXECUTE(l_cursor_handle);
       DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_handle, l_col_cnt, l_rec_tab); 
        dbms_output.put_line(l_col_cnt);
       l_current_col := l_rec_tab.FIRST;
       dbms_output.put_line(l_current_col);

       IF (l_current_col IS NOT NULL) THEN
          LOOP
             DBMS_SQL.DEFINE_COLUMN(l_cursor_handle, l_current_col, l_column_value, 300);

             l_print_text := l_rec_tab(l_current_col).col_name || 
             p_delimiter;
             l_current_col := l_rec_tab.NEXT(l_current_col);
             IF l_current_col IS NULL/*handling for last delimiter for 
            column */
             THEN
             l_print_text:=substr(l_print_text,-1);
             END IF;
             UTL_FILE.PUT (l_file_handle, l_print_text);
             EXIT WHEN (l_current_col IS NULL);
          END LOOP;
       END IF;
       UTL_FILE.PUT_LINE (l_file_handle,' ');
       LOOP
          EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cursor_handle) = 0; 

          l_current_line := '';
          FOR l_current_col IN 1..l_col_cnt LOOP
             DBMS_SQL.COLUMN_VALUE (l_cursor_handle, l_current_col, l_column_value);
             l_print_text := l_column_value || p_delimiter;
             IF l_current_col =l_col_cnt
             then
             l_current_line := l_current_line || l_column_value;
             ELSE
             l_current_line := l_current_line || l_column_value || 
             p_delimiter;
             END IF;
          END LOOP;
          l_record_count := l_record_count + 1;
          UTL_FILE.PUT_LINE (l_file_handle, l_current_line);
       END LOOP;
       UTL_FILE.FCLOSE (l_file_handle);
       DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
    END;

The procedure when executed processes the query and then stores the result into a delimited file. But the last column name is missing

    ID  NAME     DEPT_ID    DEPT    
    1   Prathick    55      FSU   India
    2   Sunny       55      FSU   Japan
    3   Muthu       55      FSU   India
    4   Manoj       55      FSU   Japan

The expected output...

    ID  NAME     DEPT_ID    DEPT   Country  
    1   Prathick    55      FSU     India
    2   Sunny       55      FSU     Japan
    3   Muthu       55      FSU     India
    4   Manoj       55      FSU     Japan

Could someone please help me..I am new to oracle.I would really appreciate it

Answer 1

SUBSTR('abcdefg,', -1) = ','. I don't think this is what you really meant.

In your column printing loop, replace

l_print_text:=substr(l_print_text,-1);

with

l_print_text := SUBSTR(l_print_text, 1, LENGTH(l_print_text)-1);

Best of luck.