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

Reference taken from article 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:


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_DATAfunction:
 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;

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_datafunction 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:

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”. 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:

pic-3.png

Now we can see the column name with more than 30 characters getting added as header of the file.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s