Exporting columns headers more than 30 character into CSV format using UTL_FILE
Published:
Browse All Articles > Exporting columns headers more than 30 character into CSV format using UTL_FILE
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:
To insert some sample data let's execute the below PLSQL block:
Once done you will see the below data on the table:
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.
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 :
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:
Once executed then we will see the result like below:
![pic-3.png]()
Now we can see the column name with more than 30 characters getting added as header of the file.
I am using the sample table to simulate the issue and to check how we can resolve this problem.
Here is my sample table:
Create the sample table:
create table MY_SURVEY_RESP
(
SURVEY_REQUEST_NAME VARCHAR2(100),
QUESTION_NAME VARCHAR2(4000),
RESPONDENT VARCHAR2(100),
RESPONSE VARCHAR2(100)
);
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;
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
Now we have to export the data for one survey request name like below:
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.wordpr
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;
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;
The output from the above block:
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;
Once executed then we will see the result like below:
Now we can see the column name with more than 30 characters getting added as header of the file.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Here's the question referenced above where slobaray asked how to do it.
https://www.experts-exchange.com/questions/28680635/Issues-in-exporting-column-into-CSV-for-more-characters.html
Author
Commented:Regards,
Sloba