<

Exporting columns headers more than 30 character into CSV format using UTL_FILE

Published on
4,743 Points
1,743 Views
Last Modified:
I got a requirement to export the table data into CSV file. The major issue was like the column values from the table needed to be transposed and used as a header on the flat file. The challenge was that the column values were more than 30 characters and can reach up to 4000 characters.
 
I am using the sample table to simulate the issue and to check how we can resolve this problem.
 
Here is my sample table:

pic-4.JPG
Create the sample table:
 
create table MY_SURVEY_RESP
(
SURVEY_REQUEST_NAME VARCHAR2(100),
QUESTION_NAME	VARCHAR2(4000),
RESPONDENT	VARCHAR2(100),
RESPONSE  VARCHAR2(100)
);

Open in new window


To insert some sample data let's execute the below PLSQL block:
 
BEGIN
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R2 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R2 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'Subject Matter Experts List'
                ,'USER-1'
                ,'Subject Matter Experts List');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'Subject Matter Experts List'
                ,'USER-1'
                ,'Subject Matter Experts List');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'Subject Matter Experts List'
                ,'USER-1'
                ,'Subject Matter Experts List');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'Supplemental Information'
                ,'USER-1'
                ,'R2 SUPPLEMENTAL INFORMATION');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'Supplemental Information'
                ,'USER-1'
                ,'R1 SUPPLEMENTAL INFORMATION');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'Supplemental Information'
                ,'USER-1'
                ,'Supplemental Information Discussion concerning environmental protection.');
        COMMIT;
END;

Open in new window


Once done you will see the below data on the table:
 
SQL> select * from MY_SURVEY_RESP;

SURVEY_REQUEST_NAME       QUESTION_NAME                                                                    RESPONDENT       RESPONSE      
------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------
Survey-1                  R1 Compliance narrative form                                                     USER-1           
Survey-2                  R1 Compliance narrative form                                                     USER-1           
Survey-2                  R1 Evidence List                                                                 USER-1           Registered an evidence
Survey-1                  R1 Evidence List                                                                 USER-1           Registered an evidence
Survey-1                  R2 Compliance narrative form                                                     USER-1           
Survey-1                  R2 Evidence List                                                                 USER-1           Registered an evidence
Survey-2                  Subject Matter Experts List is the sample question which need to be added        USER-1           Subject Matter Experts List
Survey-1                  Subject Matter Experts List is the sample question which need to be added        USER-1           Subject Matter Experts List
Survey-1                  Subject Matter Experts List is the sample question which need to be added        USER-1           Subject Matter Experts List

9 rows selected

Open in new window


Now we have to export the data for one survey request name like below:
 
pic-1.png 
But normally when we export to CSV or any flat file format we use UTL_FILE utility package from Oracle, Now the bigger issues appear, like how to bring the question name (which has more than 4000 characters to the column name). In my earlier blog if you see I am using similar way to export the column and data by passing the SQL query to the EXP_DATA function:

 https://slobaexpert.wordpress.com/2012/08/21/exporting-tab-delimited-or-csv-from-oracle/
 
In the function we have to have two parts: one to write the header and the second to loop to write the data. 
 
FOR i IN 1 .. 255
        LOOP
                BEGIN
                        dbms_sql.define_column(l_thecursor
                                              ,i
                                              ,l_columnvalue
                                              ,2000);
                        l_colcnt := i;
                        utl_file.put(l_output
                                    ,l_separator || l_desctbl(i).col_name || '|');
                EXCEPTION
                        WHEN OTHERS THEN
                                IF (SQLCODE = -1007)
                                THEN
                                        EXIT;
                                ELSE
                                        RAISE;
                                END IF;
                END;
        END LOOP;

Open in new window



But if we do like this then the DBMS SQL will refer to the column name schema definition of Oracle which is limited to 30 characters, so the column name cannot be more than 30 characters. By using the same function I can get the data exported, but the code below is not exactly what is needed (but it is nearly the same). Let us see if we use exp_data function to export and then see what the export looks like :
 
DECLARE

        l_qn_list VARCHAR2(4000);
        l_sqlqry  VARCHAR2(4000);
        l_input   VARCHAR2(100) := 'Survey-1';
        l_result  VARCHAR2(100);
        l_date    VARCHAR2(100);

BEGIN

        SELECT to_char(SYSDATE
                      ,'_DD_MM_YYYY_HH24_MI_SS')
          INTO l_date
          FROM dual;

        SELECT listagg('''' || question_name || ''''
                      ,',') within
         GROUP(
         ORDER BY question_name)
          INTO l_qn_list
          FROM (SELECT DISTINCT question_name
                  FROM my_survey_resp
                 WHERE survey_request_name = l_input);

        --dbms_output.put_line(l_Qn_List) ; 

        l_sqlqry := 'SELECT *
  FROM (   SELECT *
             FROM ( SELECT SURVEY_REQUEST_NAME
                         , Respondent
                         , ''RESPONSE'' Question_Names
                         , Question_Name
                         , Response
                      FROM MY_SURVEY_RESP ) PIVOT (MAX ( Response )
                                         FOR Question_Name
                                         IN (';

        l_sqlqry := l_sqlqry || l_qn_list || ')';

        l_sqlqry := l_sqlqry || ' ) ORDER BY 1, 2 ) WHERE SURVEY_REQUEST_NAME = NVL(''' ||
                    l_input || ''',SURVEY_REQUEST_NAME) ';

        SELECT exp_data(l_sqlqry
                      ,','
                      ,'ORA_DIR'
                      ,l_input || l_date || '.CSV')
          INTO l_result
          FROM dual;

        dbms_output.put_line(l_result);

END;

Open in new window

 
The output from the above block:

pic-2.png
Now we can see that we are getting the data as expected but the column “H” the column name is getting trimmed as this will make use of the standard column definition which will limit up to 30 characters. Sean D Stuber, one of the world’s best Oracle experts I have come across, guided me by saying “Just write the headers yourself, you already have the data” (Link to my question what I asked and the response from Sean D Stuber). Then it was very easy to achieve the solution, because we know we are using UTL_FILE and we can still extract the column names and write it into the file which is not restricted, and we can achieve the same solution.

Here is the sample PLSQL block to achieve the final solution:
 
DECLARE

        l_output      utl_file.file_type;
        l_input       VARCHAR2(100) := 'Survey-1';
        l_date        VARCHAR2(100);
        l_separator   VARCHAR2(10) DEFAULT '';
        l_colcnt      NUMBER := 0;
        l_qn_list     VARCHAR2(4000);
        l_col_list    VARCHAR2(4000);
        l_sqlqry      VARCHAR2(4000);
        l_thecursor   INTEGER DEFAULT dbms_sql.open_cursor;
        l_columnvalue VARCHAR2(4000);
        l_status      INTEGER;
        l_cnt         NUMBER := 0;
        l_desctbl     dbms_sql.desc_tab;

BEGIN

        SELECT to_char(SYSDATE
                      ,'_DD_MM_YYYY_HH24_MI_SS')
          INTO l_date
          FROM dual;

        SELECT '''' || 'SURVEY_REQUEST_NAME, Respondent,Question_Names,' ||
               listagg(question_name
                      ,',') within
         GROUP(
         ORDER BY question_name) || ''''
          INTO l_col_list
        
          FROM (SELECT DISTINCT question_name
                  FROM my_survey_resp
                 WHERE survey_request_name = 'Survey-1');

        l_output := utl_file.fopen('ORA_DIR'
                                  ,l_input || l_date || '.CSV'
                                  ,'w');

        FOR i IN (SELECT regexp_substr((l_col_list)
                                      ,'[^,]+'
                                      ,1
                                      ,LEVEL) myinput
                    FROM dual
                  CONNECT BY LEVEL <= regexp_count((l_col_list)
                                                  ,',') + 1)
        LOOP
                BEGIN
                
                        utl_file.put(l_output
                                    ,l_separator
                                     
                                     || REPLACE(i.myinput
                                               ,''''
                                               ,NULL) || ',');
                EXCEPTION
                        WHEN OTHERS THEN
                                IF (SQLCODE = -1007)
                                THEN
                                        EXIT;
                                ELSE
                                        RAISE;
                                END IF;
                END;
        END LOOP;

        SELECT listagg('''' || question_name || ''''
                      ,',') within
         GROUP(
         ORDER BY question_name)
          INTO l_qn_list
          FROM (SELECT DISTINCT question_name
                  FROM my_survey_resp
                 WHERE survey_request_name = l_input);

        l_sqlqry := 'SELECT *
  FROM (   SELECT *
             FROM ( SELECT SURVEY_REQUEST_NAME
                         , Respondent
                         , ''RESPONSE'' Question_Names
                         , Question_Name
                         , Response
                      FROM my_survey_resp ) PIVOT (MAX ( Response )
                                         FOR Question_Name
                                         IN (';

        l_sqlqry := l_sqlqry || l_qn_list || ')';

        l_sqlqry := l_sqlqry || ' ) ORDER BY 1, 2 ) WHERE SURVEY_REQUEST_NAME = NVL(''' ||
                    l_input || ''',SURVEY_REQUEST_NAME) ';

        dbms_sql.parse(l_thecursor
                      ,l_sqlqry
                      ,dbms_sql.native);
        dbms_sql.describe_columns(l_thecursor
                                 ,l_colcnt
                                 ,l_desctbl);

        FOR i IN 1 .. 1000
        LOOP
                BEGIN
                        dbms_sql.define_column(l_thecursor
                                              ,i
                                              ,l_columnvalue
                                              ,2000);
                        l_colcnt := i;
                EXCEPTION
                        WHEN OTHERS THEN
                                IF (SQLCODE = -1007)
                                THEN
                                        EXIT;
                                ELSE
                                        RAISE;
                                END IF;
                END;
        END LOOP;

        utl_file.new_line(l_output);

        dbms_sql.define_column(l_thecursor
                              ,1
                              ,l_columnvalue
                              ,2000);
        l_status := dbms_sql.EXECUTE(l_thecursor);

        LOOP
                EXIT WHEN(dbms_sql.fetch_rows(l_thecursor) <= 0);
                l_separator := '';
        
                FOR i IN 1 .. l_colcnt
                LOOP
                        dbms_sql.column_value(l_thecursor
                                             ,i
                                             ,l_columnvalue);
                        utl_file.put(l_output
                                    ,l_separator || l_columnvalue);
                        l_separator := ',';
                END LOOP;
        
                utl_file.new_line(l_output);
                l_cnt := l_cnt + 1;
        END LOOP;
        dbms_sql.close_cursor(l_thecursor);
        utl_file.fclose(l_output);
END;

Open in new window



Once executed then we will see the result like below: 

pic-3.pngNow we can see the column name with more than 30 characters getting added as header of the file.
0
Comment
Author:Swadhin Ray
2 Comments
LVL 74

Expert Comment

by:sdstuber
For reader reference:

Here's the question referenced above where slobaray asked how to do it.
http://www.experts-exchange.com/Database/Oracle/Q_28680635.html
0
LVL 17

Author Comment

by:Swadhin Ray
Thanks @ sdstuber : I provided the link to the question on the article including the information.

Regards,
Sloba
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month