Swadhin Ray
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...
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...
So when i open the csv i see something like below :
SURVEY_REQUEST_NAME,RESPON DENT,QUEST ION_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.
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;
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;
So when i open the csv i see something like below :
SURVEY_REQUEST_NAME,RESPON
Survey-1,USER-1,RESPONSE,,
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.
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
each of the question_name values is pivoted into a column header; but those become identifiers so they are 30 chars max
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...
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.
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.
ASKER
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
ASKER
I think you are right the issue is with the pivot SQL .
When I executed the below block :
And when I executed the SQL from the above output then I can see the column name is not having the right length :
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
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;
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)
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
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
ASKER
Then I am not sure how to pull the column names and then put them on the file as header....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks... will try this and update if i face any issues
ASKER
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 :
But added the other piece of code to get t? he data I am facing the error :
Here is the error when added the code to write the data against the header :
Can you guide me what wrong I am doing ?
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;
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;
Here is the error when added the code to write the data against the header :
Can you guide me what wrong I am doing ?
ASKER
Thanks for getting me into the right direction.
ms_custom_csv_exp
function?