Issues in exporting column into CSV for more characters

Hello Experts,

I want to export the column name into CSV format but the problem is the column name is too log to export which is trimming few characters from the original length...

I have the table as like below.... and the function to export the data into CSV format which is working perfectly fine...

create table my_survey_resp
(
SURVEY_REQUEST_NAME VARCHAR2(100),
QUESTION_NAME	VARCHAR2(100),
RESPONDENT	VARCHAR2(100),
RESPONSE  VARCHAR2(100)
);

begin
insert into my_survey_resp values ('Survey-1',	'R1 Compliance narrative form',	'USER-1',	'');
insert into my_survey_resp values ('Survey-2',	'R1 Compliance narrative form',	'USER-1',	'');
insert into my_survey_resp values ('Survey-2',	'R1 Evidence List',	'USER-1',	'Registered an evidence');
insert into my_survey_resp values ('Survey-1',	'R1 Evidence List',	'USER-1',	'Registered an evidence');
insert into my_survey_resp values ('Survey-1',	'R2 Compliance narrative form',	'USER-1',	'');
insert into my_survey_resp values ('Survey-1',	'R2 Evidence List',	'USER-1',	'Registered an evidence');
insert into my_survey_resp values ('Survey-2',	'Subject Matter Experts List is the sample question which need to be added ',	'USER-1',	'Subject Matter Experts List');
insert into my_survey_resp values ('Survey-1',	'Subject Matter Experts List is the sample question which need to be added ',	'USER-1',	'Subject Matter Experts List');
insert into my_survey_resp values ('Survey-1',	'Subject Matter Experts List is the sample question which need to be added ',	'USER-1',	'Subject Matter Experts List');
commit;
end;



CREATE OR REPLACE FUNCTION EXP_CSV
(
        p_query     IN VARCHAR2
       ,p_separator IN VARCHAR2 DEFAULT ','
       ,p_dir       IN VARCHAR2
       ,p_filename  IN VARCHAR2
) RETURN NUMBER IS
        l_output      utl_file.file_type;
        l_thecursor   INTEGER DEFAULT dbms_sql.open_cursor;
        l_columnvalue VARCHAR2(2000);
        l_status      INTEGER;
        l_colcnt      NUMBER DEFAULT 0;
        l_separator   VARCHAR2(10) DEFAULT '';
        l_cnt         NUMBER DEFAULT 0;
        l_desctbl     dbms_sql.desc_tab;
BEGIN
        l_output := utl_file.fopen(p_dir
                                  ,p_filename
                                  ,'w');
        dbms_sql.parse(l_thecursor
                      ,p_query
                      ,dbms_sql.native);
        dbms_sql.describe_columns(l_thecursor
                                 ,l_colcnt
                                 ,l_desctbl);
        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 || replace (l_desctbl(i).col_name  , '''', null ) || ',');
                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 := p_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);
        RETURN l_cnt;
END EXP_CSV;

Open in new window


After this when i execute the below block i am able to generate the data into CSV but the only problem is the value which needed to be in column is too long identified...


DECLARE

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

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 ms_custom_csv_exp(l_sqlqry
                              ,','
                              ,'ORA_DIR'
                              ,l_input||l_date||'.CSV')
                  INTO l_result
                  FROM dual;
                  
        
END;

Open in new window


So when i open the csv i see something like below :


SURVEY_REQUEST_NAME,RESPONDENT,QUESTION_NAMES,R1 Compliance narrative form,R1 Evidence List,R2 Compliance narrative form,R2 Evidence List,Subject Matter Experts List i,
Survey-1,USER-1,RESPONSE,,Registered an evidence,,Registered an evidence,Subject Matter Experts List

But the column name should be "Subject Matter Experts List is the sample question which need to be added" but it is only having ",Subject Matter Experts List i".


Can any one have any fine solution to handle for huge values as columns.
LVL 17
Swadhin Ray Asked:
Who is Participating?

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

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what is the

ms_custom_csv_exp

function?
sdstuberCommented:
assuming the function above is what you intended by ms_custome_csv_exp  then the problem is your pivot.

each of the question_name values is pivoted into a column header; but those become identifiers so they are 30 chars max
Swadhin Ray Author Commented:
"EXP_CSV" is the right function and yes as using the define column is making 30 chars max, but in my case i have more length which can be more than that .,,

I am assuming that as we are using the define column function which is in turn like a column max length in oracle is 30 char. But as i am exporting as CSV there should not be any restriction .... so need to do some trick to over some this...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
>>> But as i am exporting as CSV there should not be any restriction .... so need to do some trick to over some this...

doesn't matter.

the text you are exporting is derived from your query.  Your query has a 30 chacter column header.
The fact that you write those 30 characters to a csv is irrelevant.

In other words, you can't use dbms_sql to extract the column header from your query.
Swadhin Ray Author Commented:
so what are the other options to capture the column headers ?
sdstuberCommented:
You're already constructing them as a csv in the code you posted.  Just write them out
Swadhin Ray Author Commented:
I think you are right the issue is with the pivot SQL .

When I executed the below block :

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) ';

        dbms_output.put_line(l_sqlqry);

END;

Open in new window


And when I executed the SQL from the above output then I can see the column name is not having the right length :

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 ('R1 Compliance narrative form','R1 Evidence List','R2 Compliance narrative form','R2 Evidence List','Subject Matter Experts List is the sample question which need to be added ') ) ORDER BY 1, 2 ) WHERE SURVEY_REQUEST_NAME = NVL('Survey-1',SURVEY_REQUEST_NAME) 

Open in new window


The result for "Subject Matter Experts List is the sample question which need to be added " should be the column name but which is getting truncated as like below:

'Subject Matter Experts List i
sdstuberCommented:
that's what I said.  

When you pivot, you create columns, column names s have oracle identifier limits, which is 30

So - don't use the column names to write out your csv.

Just write the headers yourself, you already have the data
Swadhin Ray Author Commented:
Then I am not sure how to pull the column names and then put them on the file as header....
sdstuberCommented:
Just use utl_file.put_line

you already have the column names in l_qn_list

if you don't want them to  have quotes, then do a replace

 or listagg a second time into a different variable

or you could also just loop through question_names in a cursor and write each one with utl_file.put

you have lots of options.  You already have the data, and you're already writing to a file.
Just include the data you want in that file.


If the problem is you don't have a way to pass the header data into your function - then change the function to have take a new parameter.

it's very simple, you have several options, just pick one that suits you best

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swadhin Ray Author Commented:
thanks... will try this and update if i face any issues
Swadhin Ray Author Commented:
I am able to export the header successfully but facing some issues on the data now....

Just to test .. i converted everything on a single PLSQL block ... i am doing some mistake is competing this :

DECLARE

        l_output utl_file.file_type;
        l_input  VARCHAR2(100) := 'Survey-1';
        l_date    VARCHAR2(100);
        l_separator   VARCHAR2(10) := '' ; 
        l_colcnt      NUMBER DEFAULT 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 DEFAULT 0;
        l_desctbl     dbms_sql.desc_tab;

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_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;
--------------------------------------header written from above code 



        dbms_sql.close_cursor(l_thecursor);
        utl_file.fclose(l_output);
END;

Open in new window


But added the other piece of code to get t? he data I am facing the error :


DECLARE

        l_output utl_file.file_type;
        l_input  VARCHAR2(100) := 'Survey-1';
        l_date    VARCHAR2(100);
        l_separator   VARCHAR2(10) := '' ; 
        l_colcnt      NUMBER DEFAULT 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 DEFAULT 0;
        l_desctbl     dbms_sql.desc_tab;

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_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;
--------------------------------------header written from above code 



-----------add the data for the values

 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);

 utl_file.new_line(l_output); 
                    
  dbms_sql.define_column(l_thecursor
                              ,1
                              ,l_columnvalue
                              ,4000);
        l_status := dbms_sql.EXECUTE(l_thecursor);

LOOP
                EXIT WHEN(dbms_sql.fetch_rows(l_thecursor) <= 0);
                l_separator := '';

--- add the values 
 FOR j IN 1 .. 600 ---l_colcnt
                LOOP
                        dbms_sql.column_value(l_thecursor
                                             ,j
                                             ,l_columnvalue);
                       /* utl_file.put(l_output
                                    ,l_separator || l_columnvalue);*/
                        l_separator := ',';
                END LOOP;



--- add the values end 

utl_file.new_line(l_output);
                l_cnt := l_cnt + 1;
        END LOOP;


-----------add the rows end
        dbms_sql.close_cursor(l_thecursor);
        utl_file.fclose(l_output);
END;

Open in new window



Here is the error when added the code to write the data against the header :
Error
Can you guide me what wrong I am doing  ?
Swadhin Ray Author Commented:
Thanks for getting me into the right direction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.