<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
4,841 Points
1,841 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
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

SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month