adding quotes to oracle file

Question

i have a oracle proc which adds tab spaces to the table data and prints it a to oracle file.But now i also need the quotes around some columns some columns dnot need any quotes.

this proc looks like this..

create or replace PROCEDURE usp_create_nestor AS

v_column_name      VARCHAR2(4000);
TYPE t_bulk_collect_test_tab IS TABLE OF VARCHAR2(4000);
v_tab         t_bulk_collect_test_tab;
clob_var      CLOB;
v_file      utl_file.file_type;
ref_cur       SYS_REFCURSOR;
v_qry           VARCHAR2(500);
v_flag      NUMBER := 0;
v_year VARCHAR2(80);

BEGIN

SELECT LISTAGG ( column_name,'||CHR(9)||' ) WITHIN GROUP (ORDER BY column_id)
INTO v_column_name FROM all_tab_cols WHERE owner = 'schema' AND table_name LIKE '%tablename%';
v_qry := 'SELECT '|| v_column_name || ' from tablename';
v_flag := 1;
v_file := utl_file.fopen('filename.txt','w');

OPEN ref_cur FOR v_qry;

LOOP
    FETCH ref_cur BULK COLLECT INTO v_tab LIMIT 100;
    FOR i IN 1..v_tab.count LOOP
        utl_file.put_line(v_file,v_tab(i));  -- WRITE INTO FILE
    END LOOP;

    EXIT WHEN v_tab.count = 0;
END LOOP;

CLOSE ref_cur;
utl_file.fclose(v_file);  -- CLOSE FILE
v_flag := 2;
dbms_output.put_line('Success');
         UTL_FILE.FCLOSE_ALL;
END;

The file output looks like this:

A B C D E F

The file Output should look like this:

A B "C" D "E" "F"

Answer 1

Add a CASE expression in your LISTAGG query.

Example:

SELECT LISTAGG(CASE 
                 WHEN column_name LIKE '%NAME%' 
                   THEN '''"''||' || column_name || '||''"'''  
                 ELSE column_name 
               END, '||CHR(9)||' ) 
         WITHIN GROUP (ORDER BY column_id) AS v_column_name 
FROM   all_tab_cols 
WHERE  owner = 'HR' 
       AND table_name = 'DEPARTMENTS'; 

which gives

DEPARTMENT_ID||CHR(9)||'"'||DEPARTMENT_NAME||'"'||CHR(9)||MANAGER_ID||CHR(9)||LOCATION_ID

Running as a query

SELECT department_id
       || CHR(9)||'"'|| department_name||'"'||CHR(9)||MANAGER_ID||CHR(9)
       || location_id as output
FROM departments;   

OUTPUT                           
10  "Administration"    200 1700      
20  "Marketing" 201 1800           
30  "Purchasing"    114 1700          
40  "Human Resources"   203 2400     
50  "Shipping"  121 1500            
60  "IT"    103 1400                  
70  "Public Relations"  204 2700  
...
...