Link to home
Start Free TrialLog in
Avatar of Swadhin Ray
Swadhin RayFlag for United States of America

asked on

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.
Avatar of Sean Stuber
Sean Stuber

what is the

ms_custom_csv_exp

function?
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
Avatar of Swadhin Ray

ASKER

"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...
>>> 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.
so what are the other options to capture the column headers ?
You're already constructing them as a csv in the code you posted.  Just write them out
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
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
Then I am not sure how to pull the column names and then put them on the file as header....
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks... will try this and update if i face any issues
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 :
User generated image
Can you guide me what wrong I am doing  ?
Thanks for getting me into the right direction.