ORA-01461: can bind a LONG value only for insert into a LONG column
I have one procedure, which will form a query needed for some reports.. its giving me some error which i am unable to find out, only for one particular type of parameter values..
actually i have a table sam, in which i am storing the query which is generated by the proc, but only for one particular condition the record is not getting inserted.. actually i tried to debug and found out that an insert statement is failing.. but unable to find out the reason why.. googled out the error, but haven't found a reasonable solution..
attaching the procedure to replicate the error, appreciate your help in resolving this..
attache is the proc,
before this one has to create this table
create table sam (col1 clob);
and after this procedure to be compiled, which will get compiled without any errors and has to execute this block
DECLARE
r sys_refcursor;
BEGIN
bam_csat_report_new_marufa
'03-NOV-2009',
'2',
'0017',
'',
r
);
END;
you will see the error when you do select * from sam
ORA-01461: can bind a LONG value only for insert into a LONG column
actually i have a table sam, in which i am storing the query which is generated by the proc, but only for one particular condition the record is not getting inserted.. actually i tried to debug and found out that an insert statement is failing.. but unable to find out the reason why.. googled out the error, but haven't found a reasonable solution..
attaching the procedure to replicate the error, appreciate your help in resolving this..
attache is the proc,
before this one has to create this table
create table sam (col1 clob);
and after this procedure to be compiled, which will get compiled without any errors and has to execute this block
DECLARE
r sys_refcursor;
BEGIN
bam_csat_report_new_marufa
'03-NOV-2009',
'2',
'0017',
'',
r
);
END;
you will see the error when you do select * from sam
ORA-01461: can bind a LONG value only for insert into a LONG column
I see no procedure?
ASKER
sorry.. missed the attachment.. reattached here as a snippet..
CREATE OR REPLACE PROCEDURE APPS.bam_csat_report_new_marufa (
p_from_date VARCHAR2, --- FROM DATE
p_to_date VARCHAR2, --- TO DATE
p_report_type VARCHAR2, --- SURVEY, FEEDBACK ETC
p_circle_id VARCHAR2, --- CIRCLE ID
p_table_ext VARCHAR2, --- TABLE EXTENSION Ex _MUM , _KOL etc
p_mr_data_ref_cur OUT sys_refcursor
)
AS
l_str1 VARCHAR2 (32767);
l_str2 VARCHAR2 (32767);
l_str3 VARCHAR2 (32767);
l_str4 VARCHAR2 (32767);
l_str3a VARCHAR2 (32767);
l_bscs_suffix VARCHAR2 (30);
l_view_suffix VARCHAR2 (30);
v_length number;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE SAM';
/*
BEGIN
SELECT bscs_suffix, attribute7
INTO l_bscs_suffix, l_view_suffix
FROM xx_circle_mast
WHERE circle_id = p_circle_id
GROUP BY bscs_suffix, attribute7;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;*/
--insert into sam values ('CSAT_REPORT_NEW '||P_REPORT_TYPE);
--commit;
IF p_report_type = '0'
THEN --- FEEDBACK Report
-- L_STR1 := 'select replace(replace(MOBILENO||'',''|| REPLACE(REPLACE(CASE WHEN INSTR(replace(FEEDBACK,'' '',''-''),CHR(13)) > 0 THEN SUBSTR(feedback,1,INSTR(feedback,CHR(13))-1) ELSE feedback END,'','',''-''),CHR(10),'' '')||'',''|| TO_CHAR(FEEDBACKDT,''DD-MON-YYYY HH24:MI:SS'')||'',''|| TO_CHAR(MESSAGE1DT,''DD-MON-YYYY HH24:MI:SS''),''"'',''-'') ,chr(10),'' '')';
-- L_STR1 := L_STR1 ||' from CSAT_FEEDBACK_TAB'||''||p_table_ext||''||' A';
-- L_STR1 := L_STR1 ||' WHERE TRUNC(a.FEEDBACKDT) BETWEEN '''||p_from_Date||''' AND '''||p_to_Date||''' ';
-- L_STR1 := L_STR1 ||' and A.circleid= '''||P_circle_id||'''';
l_str1 :=
'select replace(replace(MOBILENO||'',''|| REPLACE(REPLACE(CASE WHEN INSTR(replace(FEEDBACK,'' '',''-''),CHR(13)) > 0 THEN SUBSTR(feedback,1,INSTR(feedback,CHR(13))-1) ELSE feedback END,'','',''-''),CHR(10),'' '')||'',''|| TO_CHAR(FEEDBACKDT,''DD-MON-YYYY HH24:MI:SS'')||'',''|| TO_CHAR(MESSAGE1DT,''DD-MON-YYYY HH24:MI:SS''),''"'',''-'') ,chr(10),'' '')';
l_str1 :=
l_str1
|| ' FROM CSAT_FEEDBACK_TAB'
|| ''
|| p_table_ext
|| ''
|| ' CSAT , ORG_SR_SUMMARY'
|| ''
|| p_table_ext
|| ''
|| ' ORG ';
l_str1 := l_str1 || ' WHERE CSAT.MOBILENO = ORG.CUSTOMER_CELL_NO ';
l_str1 := l_str1 || ' AND CSAT.CIRCLEID = ORG.CIRCLE_ID ';
l_str1 := l_str1 || ' AND SUBSTR(ORG.INCIDENT_TYPE,3,1) = ''C'' ';
l_str1 :=
l_str1
|| ' AND CSAT.FEEDBACKDT BETWEEN TO_DATE('''
|| p_from_date
|| ''',''DD-MON-YYYY HH24:MI:SS'') AND TO_DATE('''
|| p_to_date
|| ''',''DD-MON-YYYY HH24:MI:SS'') ';
l_str1 := l_str1 || ' and CSAT.CIRCLEID = ''' || p_circle_id || '''';
ELSIF p_report_type = '1'
THEN -- ACTIVITY PERFORMANCE
--L_STR2 := 'SELECT replace(replace(REPLACE(fnd.user_name,'','',''-'') ||'',''||REPLACE(csat.CREATED_BY_USER,'','',''-'') ||'',''|| REPLACE(ors.created_by_group,'','',''-'')||'',''|| ';
l_str2 :=
'SELECT replace(replace(REPLACE(fnd.user_name,'','',''-'') ||'',''|| ';
l_str2 := l_str2 || ' REPLACE (fnd.user_name,'','',''-'') ||'',''|| ';
-- soft_close_id,
l_str2 := l_str2 || ' REPLACE (t.user_name,'','',''-'') ||'',''|| ';
-- soft_close_user_name,
l_str2 := l_str2 || ' REPLACE (t.group_name,'','',''-'') ||'',''|| ';
-- soft_close_user_group,
l_str2 := l_str2 || ' COUNT(1) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response1)) = ''YES'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response1)) = ''NO'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response1)) IS NULL THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response1)) = ''YES'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response1)) = ''NO'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND( ( SUM(CASE WHEN TRIM(UPPER(response1)) IS NULL THEN 1 ELSE 0 END) / COUNT(1) ) * 100, 2) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response2)) = ''YES'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response2)) = ''NO'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response2)) IS NULL THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response2)) = ''YES'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response2)) = ''NO'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND( ( SUM(CASE WHEN TRIM(UPPER(response2)) IS NULL THEN 1 ELSE 0 END) / COUNT(1) ) * 100, 2) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response3)) = ''YES'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response3)) = ''NO'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response3)) IS NULL THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response3)) = ''YES'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response3)) = ''NO'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND( ( SUM(CASE WHEN TRIM(UPPER(response3)) IS NULL THEN 1 ELSE 0 END) / COUNT(1) ) * 100, 2) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response4)) = ''YES'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response4)) = ''NO'' THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' SUM(CASE WHEN TRIM(UPPER(response4)) IS NULL THEN 1 ELSE 0 END) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response4)) = ''YES'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND(SUM(CASE WHEN TRIM(UPPER(response4)) = ''NO'' THEN 1 ELSE 0 END)/COUNT(1) * 100,2) ||'',''|| ';
l_str2 :=
l_str2
|| ' ROUND( ( SUM(CASE WHEN TRIM(UPPER(response4)) IS NULL THEN 1 ELSE 0 END) / COUNT(1) ) * 100, 2),''"'',''-'') ,chr(10),'' '')';
l_str2 :=
l_str2 || ' FROM csat_result_tab' || '' || p_table_ext || ''
|| ' csat, ';
l_str2 :=
l_str2 || ' org_sr_summary' || '' || p_table_ext || '' || ' ors, ';
l_str2 := l_str2 || ' fnd_user' || '' || p_table_ext || '' || ' fnd ';
l_str2 := l_str2 || ' , org_incident_history' || p_table_ext || ' t ';
-- new table
l_str2 :=
l_str2
|| ' WHERE ors.incident_no = t.incident_id and csat.incident_no = ors.incident_no and ';
l_str2 :=
l_str2
|| ' fnd.user_id = t.user_id AND csat.incident_no = t.incident_id
AND ors.incident_no = t.incident_id
AND t.status_name IN (''Closed'', ''Completed'', ''Soft Closed'')
AND t.sequence_no IN (
SELECT DECODE (MAX (sequence_no - 1),
0, MAX (sequence_no),
MAX (sequence_no - 1)
)
FROM org_incident_history'
|| p_table_ext
|| ' h
WHERE h.incident_id = t.incident_id
-- AND h.status_name = t.status_name
)
AND t.status_name IN (''Closed'', ''Completed'', ''Soft Closed'') AND ';
--L_STR2 := L_STR2|| ' TRUNC(SR_CREATION_DATE) BETWEEN '''||p_from_Date||''' AND '''||p_to_Date||''' ';
-- HemantP / 20090305 / A / Changes done for replacing 'creation_dt' with 'Last_update_dt' / 2008/68386/CEN
--L_STR2 := L_STR2|| ' trunc(last_update_dt) = TO_DATE ('''|| p_from_Date ||''',''DD-MON-YYYY'' )';
l_str2 :=
l_str2
|| ' last_update_dt >= TO_DATE ('''
|| p_from_date
|| ''', ''DD-MON-YYYY HH24:MI:SS'') ';
l_str2 :=
l_str2
|| ' AND last_update_dt <= TO_DATE ('''
|| p_to_date
|| ''', ''DD-MON-YYYY HH24:MI:SS'') ';
--L_STR2 := L_STR2|| ' AND sr_creation_date <= TO_DATE ('''|| p_to_Date || ' 23:59:59'', ''DD-MON-YYYY HH24:MI:SS'') ';
-- HemantP / 20090305 / A / Changes done for replacing 'creation_dt' with 'Last_update_dt' / 2008/68386/CEN -- Change Over
l_str2 := l_str2 || ' and CSAT.circle_id= ''' || p_circle_id || '''';
l_str2 := l_str2 || ' and substr(ors.sr_sub_type,1,1) = ''C'' ';
-- Only Complaints in NEW_CSAT Reports
l_str2 :=
l_str2
|| ' GROUP BY fnd.user_name, t.user_name, t.group_name, t.sequence_no ';
l_str2 := l_str2 || ' , t.user_name, t.group_name ';
ELSIF p_report_type = '2'
THEN --- SURVEY Report
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3 :=
'SELECT replace(replace(REPLACE(fnd.user_name,'','',''-'')||'',''||REPLACE(t.user_name,'','',''-'') ||'',''|| ';
l_str3 := l_str3 || ' REPLACE(t.GROUP_NAME,'','',''-'') ||'',''|| ';
l_str3 := l_str3 || ' REPLACE (fnd1.user_name,'','',''-'') ||'',''|| ';
-- soft_close_id,
l_str3 := l_str3 || ' REPLACE (t1.user_name,'','',''-'') ||'',''|| ';
-- soft_close_user_name,
l_str3 := l_str3 || ' REPLACE (t1.group_name,'','',''-'') ||'',''|| ';
-- soft_close_user_group,
l_str3 := l_str3 || ' REPLACE (fnd2.user_name,'','',''-'') ||'',''|| ';
--hard_close_id,
l_str3 := l_str3 || ' REPLACE (t2.user_name,'','',''-'') ||'',''|| ';
--hard_close_user_name,
l_str3 := l_str3 || ' REPLACE (t2.group_name,'','',''-'') ||'',''|| ';
select length(l_str3) into v_length from dual;
dbms_output.Put_line('Length of 1 '||v_length);
--hard_close_user_group
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change OVer
l_str3 := l_str3 || ' a.incident_no ||'',''|| ';
l_str3 := l_str3 || ' a.incident_type ||'',''|| ';
l_str3 := l_str3 || ' a.incident_sub_type ||'',''|| ';
-- Purushottam D/ 29062011 /A/ Media column/ 2011/84373/CEN
l_str3 := l_str3 || ' a.media ||'',''|| ';
-- Purushottam D/ 29062011 /A/ Media column/ 2011/84373/CEN -- change over
l_str3 := l_str3 || ' b.outcome ||'',''|| '; -- sr_final_outcome
l_str3 :=
l_str3
|| ' to_char(a.sr_creation_date,''MM/DD/YYYY HH24:MI:SS'') ||'',''|| ';
l_str3 :=
l_str3
|| ' TO_CHAR (t2.end_time, ''DD-MON-YYYY HH24:MI:SS'') ||'',''|| ';
--sr_closure_date,
l_str3 := l_str3 || ' a.mobile_no ||'',''|| ';
l_str3 := l_str3 || ' a.cust_segment ||'',''|| ';
-- HemantP/20090225/ E / Uncommented below 1 line code./ 2009/80959/HYG
l_str3 := l_str3 || ' a.mood_of_interaction ||'',''|| ';
-- HemantP/20090225/ E / Uncommented below 1 line code./ 2009/80959/HYG -- Change Over
-- PurushottamD/20100406/ A / New column added./ 22010/83281/CEN
l_str3 := l_str3 || ' b.is_front_line_closure || '','' || ';
-- PurushottamD/20100406/ A / New column added./ 22010/83281/CEN -- Change Over
l_str3 :=
l_str3
|| ' decode(b.call_back_reqd,''S'',''SMS'', ''Y'',''CALL BACK'', ''F'',''FAX'', ''L'',''LETTER'', ''E'',''EMAIL'', b.call_back_reqd) ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response1)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response1)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response1)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response2)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response2)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response2)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response3)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response3)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response3)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response4)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
|| ' CASE WHEN TRIM(UPPER(response4)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3 :=
l_str3
--|| ' CASE WHEN TRIM(UPPER(response4)) IS NULL THEN 1 ELSE 0 END,''"'',''-'') ,chr(10),'' '') ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN
|| ' CASE WHEN TRIM(UPPER(response4)) IS NULL THEN 1 ELSE 0 END || '','' || ';
-- Purushottam D/20100714 / A / Enterprise_corp_code Enterprise_Corp_Description added / 2010/83460/CEN
l_str3 :=
l_str3
|| '(SELECT b.area_id
FROM customer_all'
|| l_bscs_suffix
|| ' b, org_sr_summary'
|| p_table_ext
|| ' a
WHERE a.customer_id = b.customer_id
AND a.incident_no = i.incident_number
AND b.area_id IS NOT NULL
AND ROWNUM < 2) || '','' || ';
l_str3 :=
l_str3
|| '(SELECT ac.ACC_NAME
FROM customer_all'
|| l_bscs_suffix
|| ' b, org_sr_summary'
|| p_table_ext
|| ' a,
NOC_ACC_MASTER ac,NOC_ACC_GROUP_MASTER ag
WHERE a.customer_id = b.customer_id
AND a.incident_no = i.incident_number
AND b.AREA_ID=ac.area_id(+)
AND ac.ACC_GROUP_ID=ag.ACC_GROUP_ID
AND b.area_id IS NOT NULL
AND ROWNUM < 2) || '','' || ';
-- Purushottam D/20100714 / A / Enterprise_corp_code Enterprise_Corp_Description added / 2010/83460/CEN --change done
-- SAGAR V/20111122/ A / ENTERPRISE_CODE IS ADDED / 2011/84462/CEN --
l_str3 :=
l_str3
|| '(SELECT ac.ACC_CODE
FROM customer_all'
|| l_bscs_suffix
|| ' b, org_sr_summary'
|| p_table_ext
|| ' a,
NOC_ACC_MASTER ac,NOC_ACC_GROUP_MASTER ag
WHERE a.customer_id = b.customer_id
AND a.incident_no = i.incident_number
AND b.AREA_ID=ac.area_id(+)
AND ac.ACC_GROUP_ID=ag.ACC_GROUP_ID
AND b.area_id IS NOT NULL
AND ROWNUM < 2) || '','' || ';
-- SAGAR V/20111122/ A / ENTERPRISE_CODE IS ADDED / 2011/84462/CEN -- change over
l_str3 :=
l_str3 || ' REPLACE(I.EXTERNAL_ATTRIBUTE_3,'','',''-'') || '','' || ';
-- roaminguser
l_str3 :=
l_str3
|| ' (SELECT INITCAP (group_desc)
FROM jtf_rs_groups_vl'
|| p_table_ext
|| '
WHERE end_date_active IS NULL
AND group_desc IS NOT NULL
AND attribute6 = a.circle_id
AND GROUP_ID = (
SELECT attribute3
FROM jtf_rs_resource_extns'
|| p_table_ext
|| '
WHERE user_name = i.external_attribute_3 AND user_name IS NOT NULL
AND CREATION_DATE =(SELECT MAX(CREATION_DATE)
FROM jtf_rs_resource_extns'
|| p_table_ext
|| '
WHERE user_name = i.external_attribute_3
AND user_name IS NOT NULL
AND end_date_active IS NULL))),''"'',''-'') ,chr(10),'' '') ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN -- change over
select length(l_str3) into v_length from dual;
dbms_output.Put_line('Length of 2 '||v_length);
l_str3 := l_str3 || ' FROM csat_result_tab' || p_table_ext || ' A, ';
l_str3 := l_str3 || ' org_sr_summary' || p_table_ext || ' B, ';
l_str3 := l_str3 || ' fnd_user' || p_table_ext || ' fnd ';
l_str3 := l_str3 || ' , fnd_user' || p_table_ext || ' fnd1 ';
-- new table
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3 := l_str3 || ' , fnd_user' || p_table_ext || ' fnd2, ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN
l_str3 :=
l_str3 || ' CS_INCIDENTS_ALL_B' || '' || l_view_suffix || ''
|| ' I, ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN -- change over
l_str3 := l_str3 || ' org_incident_history' || p_table_ext || ' t, ';
select length(l_str3) into v_length from dual;
dbms_output.Put_line('Length of 3 '||v_length);
-- new table
l_str3 := l_str3 || ' org_incident_history' || p_table_ext || ' t1, ';
-- new table
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3 := l_str3 || ' org_incident_history' || p_table_ext || ' t2 ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
l_str3 := l_str3 || ' WHERE a.incident_no = b.incident_no and ';
-- Purushottam D/20100317 / A / condition added / 2010/83241/CEN
l_str3 := l_str3 || ' I.INCIDENT_NUMBER = TO_CHAR(A.INCIDENT_NO) and ';
-- Purushottam D/20100317 / A / condition added / 2010/83241/CEN -- Change Over
l_str3 :=
l_str3
|| ' fnd.user_id = t.user_id
AND fnd1.user_id = t1.user_id
AND a.incident_no = t.incident_id ';
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3 :=
l_str3
|| ' and fnd2.user_id = t2.user_id
AND a.incident_no = t2.incident_id ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
dbms_output.Put_line('Length of 4 ');
--L_STR3 := L_STR3|| ' AND a.incident_no = t1.incident_id AND t.status_name IN (''Closed'', ''Completed'', ''Soft Closed'')
l_str3 :=
l_str3
|| ' AND a.incident_no = t1.incident_id
AND t.sequence_no IN ( SELECT MIN(h.sequence_no)
FROM org_incident_history'
|| p_table_ext
|| ' h
WHERE h.incident_id = t.incident_id
-- AND h.status_name = t.status_name
-- AND h.status_name IN (''Closed'',''Completed'',''Soft Closed'')
)
-- HemantP/20090424 / E / columns changed / NC000000202276
-- AND t1.status_name IN (''Closed'',''Completed'',''Soft Closed'')
AND t1.status_name IN (''Soft Closed'')
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
AND t1.sequence_no IN (SELECT MAX (sequence_no)
FROM org_incident_history'
|| p_table_ext
|| ' h1
WHERE h1.incident_id = t1.incident_id
-- AND h1.status_name = t1.status_name
-- HemantP/20090424 / E / columns changed / NC000000202276
AND h1.status_name IN (''Soft Closed'')) ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
l_str3 :=
l_str3
|| ' AND t2.status_name IN (''Closed'', ''Completed'')
AND t2.sequence_no IN (
SELECT MAX (sequence_no)
FROM org_incident_history'
|| p_table_ext
|| ' h2
WHERE h2.incident_id = t2.incident_id
AND h2.status_name IN (''Closed'', ''Completed'')) ';
-- HemantP / 20090305 / A / Changes done for replacing 'creation_dt' with 'Last_update_dt' / 2008/68386/CEN
l_str3 :=
l_str3
|| ' AND last_update_dt >= TO_DATE ('''
|| p_from_date
|| ''', ''DD-MON-YYYY HH24:MI:SS'') ';
l_str3 :=
l_str3
|| ' AND last_update_dt <= TO_DATE ('''
|| p_to_date
|| ''', ''DD-MON-YYYY HH24:MI:SS'') ';
--L_STR3 := L_STR3|| ' AND sr_creation_date <= TO_DATE ('''|| p_to_Date || ' 23:59:59'', ''DD-MON-YYYY HH24:MI:SS'') ';
-- HemantP / 20090305 / A / Changes done for replacing 'creation_dt' with 'Last_update_dt' / 2008/68386/CEN -- Change Over
l_str3 := l_str3 || ' and A.circle_id= ''' || p_circle_id || '''';
l_str3 := l_str3 || ' and B.circle_id= ''' || p_circle_id || '''';
l_str3 := l_str3 || ' and substr(a.INCIDENT_SUB_TYPE,1,1) = ''C'' ';
-- Only Complaints in NEW_CSAT Reports
-- HemantP / 20090518 / A / the second query is added below / INC000000235907
-- HemantP/20090424 / E / columns changed / NC000000202276
-- L_STR3A := L_STR3A|| ' UNION ALL ';
l_str3a :=
l_str3a
|| ' SELECT replace(replace(REPLACE(fnd.user_name,'','',''-'')||'',''||REPLACE(t.user_name,'','',''-'') ||'',''|| ';
l_str3a := l_str3a || ' REPLACE(t.GROUP_NAME,'','',''-'') ||'',''|| ';
l_str3a :=
l_str3a || ' REPLACE (fnd1.user_name,'','',''-'') ||'',''|| ';
-- soft_close_id,
l_str3a := l_str3a || ' REPLACE (t1.user_name,'','',''-'') ||'',''|| ';
-- soft_close_user_name,
l_str3a := l_str3a || ' REPLACE (t1.group_name,'','',''-'') ||'',''|| ';
-- soft_close_user_group,
l_str3a :=
l_str3a || ' REPLACE (fnd2.user_name,'','',''-'') ||'',''|| ';
--hard_close_id,
l_str3a := l_str3a || ' REPLACE (t2.user_name,'','',''-'') ||'',''|| ';
--hard_close_user_name,
l_str3a := l_str3a || ' REPLACE (t2.group_name,'','',''-'') ||'',''|| ';
--hard_close_user_group
l_str3a := l_str3a || ' a.incident_no ||'',''|| ';
l_str3a := l_str3a || ' a.incident_type ||'',''|| ';
l_str3a := l_str3a || ' a.incident_sub_type ||'',''|| ';
-- Purushottam D/ 29062011 /A/ Media column/ 2011/84373/CEN
l_str3a := l_str3a || ' a.media ||'',''|| ';
-- Purushottam D/ 29062011 /A/ Media column/ 2011/84373/CEN -- change over
l_str3a := l_str3a || ' b.outcome ||'',''|| '; -- sr_final_outcome
l_str3a :=
l_str3a
|| ' to_char(a.sr_creation_date,''MM/DD/YYYY HH24:MI:SS'') ||'',''|| ';
l_str3a :=
l_str3a
|| ' TO_CHAR (t2.end_time, ''DD-MON-YYYY HH24:MI:SS'') ||'',''|| ';
--sr_closure_date,
l_str3a := l_str3a || ' a.mobile_no ||'',''|| ';
l_str3a := l_str3a || ' a.cust_segment ||'',''|| ';
-- HemantP/20090225/ E / Uncommented below 1 line code./ 2009/80959/HYG
l_str3a := l_str3a || ' a.mood_of_interaction ||'',''|| ';
-- HemantP/20090225/ E / Uncommented below 1 line code./ 2009/80959/HYG -- Change Over
-- PurushottamD/20100406/ A / New column added./ 22010/83281/CEN
l_str3a := l_str3a || ' b.is_front_line_closure || '','' || ';
-- PurushottamD/20100406/ A / New column added./ 22010/83281/CEN -- Change Over
l_str3a :=
l_str3a
|| ' decode(b.call_back_reqd,''S'',''SMS'', ''Y'',''CALL BACK'', ''F'',''FAX'', ''L'',''LETTER'', ''E'',''EMAIL'', b.call_back_reqd) ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response1)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response1)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response1)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response2)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response2)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response2)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response3)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response3)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response3)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response4)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
|| ' CASE WHEN TRIM(UPPER(response4)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str3a :=
l_str3a
--|| ' CASE WHEN TRIM(UPPER(response4)) IS NULL THEN 1 ELSE 0 END,''"'',''-'') ,chr(10),'' '') ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN
|| ' CASE WHEN TRIM(UPPER(response4)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
-- Purushottam D/20100714 / A / Enterprise_corp_code Enterprise_Corp_Description added / 2010/83460/CEN
l_str3a :=
l_str3a
|| '(SELECT b.area_id
FROM customer_all'
|| l_bscs_suffix
|| ' b, org_sr_summary'
|| p_table_ext
|| ' a
WHERE a.customer_id = b.customer_id
AND a.incident_no = i.incident_number
AND b.area_id IS NOT NULL
AND ROWNUM < 2) || '','' || ';
l_str3a :=
l_str3a
|| '(SELECT ac.ACC_NAME
FROM customer_all'
|| l_bscs_suffix
|| ' b, org_sr_summary'
|| p_table_ext
|| ' a,
NOC_ACC_MASTER ac,NOC_ACC_GROUP_MASTER ag
WHERE a.customer_id = b.customer_id
AND a.incident_no = i.incident_number
AND b.AREA_ID=ac.area_id(+)
AND ac.ACC_GROUP_ID=ag.ACC_GROUP_ID
AND b.area_id IS NOT NULL
AND ROWNUM < 2) || '','' || ';
-- Purushottam D/20100714 / A / Enterprise_corp_code Enterprise_Corp_Description added / 2010/83460/CEN --change done
-- SAGAR V/20111122/ A / ENTERPRISE_CODE IS ADDED / 2011/84462/CEN --
l_str3a :=
l_str3a
|| '(SELECT ac.ACC_CODE
FROM customer_all'
|| l_bscs_suffix
|| ' b, org_sr_summary'
|| p_table_ext
|| ' a,
NOC_ACC_MASTER ac,NOC_ACC_GROUP_MASTER ag
WHERE a.customer_id = b.customer_id
AND a.incident_no = i.incident_number
AND b.AREA_ID=ac.area_id(+)
AND ac.ACC_GROUP_ID=ag.ACC_GROUP_ID
AND b.area_id IS NOT NULL
AND ROWNUM < 2) || '','' || ';
-- SAGAR V/20111122/ A / ENTERPRISE_CODE IS ADDED / 2011/84462/CEN -- change over
l_str3a :=
l_str3a
|| ' REPLACE(I.EXTERNAL_ATTRIBUTE_3,'','',''-'') || '','' || ';
-- roaminguser
l_str3a :=
l_str3a
|| ' (SELECT INITCAP (group_desc)
FROM jtf_rs_groups_vl'
|| p_table_ext
|| '
WHERE end_date_active IS NULL
AND group_desc IS NOT NULL
AND attribute6 = a.circle_id
AND GROUP_ID = (
SELECT attribute3
FROM jtf_rs_resource_extns'
|| p_table_ext
|| '
WHERE user_name = i.external_attribute_3 AND user_name IS NOT NULL
AND CREATION_DATE =(SELECT MAX(CREATION_DATE)
FROM jtf_rs_resource_extns'
|| p_table_ext
|| '
WHERE user_name = i.external_attribute_3
AND user_name IS NOT NULL
AND end_date_active IS NULL))),''"'',''-'') ,chr(10),'' '') ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN -- change over
l_str3a := l_str3a || ' FROM csat_result_tab' || p_table_ext || ' A, ';
l_str3a := l_str3a || ' org_sr_summary' || p_table_ext || ' B, ';
l_str3a := l_str3a || ' fnd_user' || p_table_ext || ' fnd ';
l_str3a := l_str3a || ' , fnd_user' || p_table_ext || ' fnd1 ';
-- new table
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3a := l_str3a || ' , fnd_user' || p_table_ext || ' fnd2, ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN
l_str3a :=
l_str3a || ' CS_INCIDENTS_ALL_B' || '' || l_view_suffix || ''
|| ' I, ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN -- change over
l_str3a := l_str3a || ' org_incident_history' || p_table_ext || ' t, ';
-- new table
l_str3a := l_str3a || ' org_incident_history' || p_table_ext || ' t1, ';
-- new table
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3a := l_str3a || ' org_incident_history' || p_table_ext || ' t2 ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
l_str3a := l_str3a || ' WHERE a.incident_no = b.incident_no and ';
l_str3a :=
l_str3a
|| ' fnd.user_id = t.user_id
AND fnd1.user_id = t1.user_id
AND a.incident_no = t.incident_id ';
-- HemantP/20090424 / E / columns changed / NC000000202276
l_str3a :=
l_str3a
|| ' and fnd2.user_id = t2.user_id
AND a.incident_no = t2.incident_id ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN
l_str3a := l_str3a || ' AND I.INCIDENT_NUMBER = TO_CHAR(A.INCIDENT_NO) ';
-- Purushottam D/20100317 / A / Roaming user and Roaming Group added / 2010/83241/CEN -- change over
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
--L_STR3A := L_STR3A|| ' AND a.incident_no = t1.incident_id AND t.status_name IN (''Closed'', ''Completed'', ''Soft Closed'')
l_str3a :=
l_str3a
|| ' AND a.incident_no = t1.incident_id
-- Purushottam D/20090811 / E / condition changed to overcome mismatch issue / INC000000389141
AND t.status_name IN (''Closed'')
-- Purushottam D/20090811 / E / condition changed to overcome mismatch issue / INC000000389141--Change Over
AND t.sequence_no IN ( SELECT MIN(h.sequence_no)
FROM org_incident_history'
|| p_table_ext
|| ' h
WHERE h.incident_id = t.incident_id
-- AND h.status_name = t.status_name
-- AND h.status_name IN (''Closed'',''Completed'',''Soft Closed'')
)
-- HemantP/20090424 / E / columns changed / NC000000202276
-- AND t1.status_name IN (''Closed'',''Completed'',''Soft Closed'')
AND t1.status_name IN (''Closed'')
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
AND t1.sequence_no IN (SELECT MAX (sequence_no)
FROM org_incident_history'
|| p_table_ext
|| ' h1
WHERE h1.incident_id = t1.incident_id
-- AND h1.status_name = t1.status_name
-- HemantP/20090424 / E / columns changed / NC000000202276
AND h1.status_name IN (''Closed'')) ';
-- HemantP/20090424 / E / columns changed / NC000000202276 -- Change Over
l_str3a :=
l_str3a
|| ' AND t2.status_name IN (''Closed'')
AND t2.sequence_no IN (
SELECT MAX (sequence_no)
FROM org_incident_history'
|| p_table_ext
|| ' h2
WHERE h2.incident_id = t2.incident_id
AND h2.status_name IN (''Closed'')) ';
-- HemantP / 20090305 / A / Changes done for replacing 'creation_dt' with 'Last_update_dt' / 2008/68386/CEN
l_str3a :=
l_str3a
|| ' AND last_update_dt >= TO_DATE ('''
|| p_from_date
|| ''', ''DD-MON-YYYY HH24:MI:SS'') ';
l_str3a :=
l_str3a
|| ' AND last_update_dt <= TO_DATE ('''
|| p_to_date
|| ''', ''DD-MON-YYYY HH24:MI:SS'') ';
--L_STR3A := L_STR3A|| ' AND sr_creation_date <= TO_DATE ('''|| p_to_Date || ' 23:59:59'', ''DD-MON-YYYY HH24:MI:SS'') ';
-- HemantP / 20090305 / A / Changes done for replacing 'creation_dt' with 'Last_update_dt' / 2008/68386/CEN -- Change Over
l_str3a := l_str3a || ' and A.circle_id= ''' || p_circle_id || '''';
l_str3a := l_str3a || ' and B.circle_id= ''' || p_circle_id || '''';
l_str3a := l_str3a || ' and substr(a.INCIDENT_SUB_TYPE,1,1) = ''C'' ';
-- Only Complaints in NEW_CSAT Reports
-- HemantP / 20090518 / A / the second query is added below / INC000000235907 -- Change Over
ELSIF p_report_type = '3'
THEN
l_str4 :=
'SELECT a.incident_no ||'',''||to_char(a.sr_creation_date,''MM/DD/YYYY HH24:MI:SS'') ||'',''||a.mobile_no||'',''||c.circle_name||'',''|| ';
l_str4 :=
l_str4
|| ' a.cust_type||'',''|| a.cust_segment||'',''|| b.callduration||'',''||b.last_menu||'',''|| ';
l_str4 :=
l_str4
|| ' CASE WHEN TRIM(UPPER(response1)) = ''YES'' THEN 1 ELSE 0 END ||'',''|| ';
l_str4 :=
l_str4
|| ' CASE WHEN TRIM(UPPER(response1)) = ''NO'' THEN 1 ELSE 0 END||'',''|| ';
l_str4 :=
l_str4
|| ' CASE WHEN TRIM(UPPER(response1)) IS NULL THEN 1 ELSE 0 END ||'',''|| ';
l_str4 :=
l_str4
|| ' CASE WHEN TRIM(UPPER(response2)) = ''YES'' THEN 1 ELSE 0 END||'',''|| ';
l_str4 :=
l_str4
|| ' CASE WHEN TRIM(UPPER(response2)) = ''NO'' THEN 1 ELSE 0 END ||'',''|| ';
l_str4 :=
l_str4
|| ' CASE WHEN TRIM(UPPER(response2)) IS NULL THEN 1 ELSE 0 END ';
l_str4 :=
l_str4 || ' FROM csat_result_tab' || '' || p_table_ext || ''
|| ' A, ';
l_str4 := l_str4 || ' servionuser.newcrm@to_rapcti B, ';
l_str4 := l_str4 || ' xx_circle_mast c ';
l_str4 := l_str4 || ' WHERE a.incident_no = b.incident_no and ';
l_str4 := l_str4 || ' a.circle_id = b.circle_id and ';
l_str4 := l_str4 || ' a.circle_id = c.circle_id ';
l_str4 := l_str4 || ' and A.circle_id= ''' || p_circle_id || '''';
l_str4 :=
l_str4
|| ' and A.SR_CREATION_DATE >= TO_DATE('''
|| p_from_date
|| ''',''DD-MON-YYYY HH24:MI:SS'') ';
l_str4 :=
l_str4
|| ' A.SR_CREATION_DATE <= TO_DATE('''
|| p_to_date
|| ''',''DD-MON-YYYY HH24:MI:SS'') ';
END IF;
--INSERT INTO NATCRM.TAB_DEBUG_REPORT (QRY_TEXT) VALUES(l_str3);
-- COMMIT;
IF p_report_type = '0'
THEN
-- INSERT INTO NATCRM.TAB_DEBUG_REPORT (QRY_TEXT) VALUES(l_str1);
-- COMMIT;
INSERT INTO sam
VALUES (l_str1);
COMMIT;
OPEN p_mr_data_ref_cur FOR (l_str1);
ELSIF p_report_type = '1'
THEN
-- INSERT INTO NATCRM.TAB_DEBUG_REPORT (QRY_TEXT) VALUES(l_str2);
-- COMMIT;
INSERT INTO sam
VALUES (l_str2);
COMMIT;
OPEN p_mr_data_ref_cur FOR (l_str2);
ELSIF p_report_type = '2'
THEN
-- INSERT INTO NATCRM.TAB_DEBUG_REPORT (QRY_TEXT) VALUES(l_str3);
-- COMMIT;
insert into sam values('Test'||'Test');
commit;
dbms_output.put_line('Length');
--select length (l_str3) into v_length from dual;
dbms_output.put_line('Length of '||v_length);
-- insert into sam values(v_length);
-- commit;
--l_str4:=l_str3||SQLERRM;
INSERT INTO sam
VALUES ('Survey1 -' || l_str3);
COMMIT;
--select length (l_str3a) into l_str3a from dual;
--insert into sam values('Length of st3a:'||v_length);
INSERT INTO sam
VALUES ('Survey2 -');
COMMIT;
-- Purushottam D/20090811 / E / changed to UNION from UNION ALLto overcome mismatch issue / INC000000389141
-- OPEN p_mr_data_ref_cur FOR l_str3 || ' UNION ALL ' || l_str3a;
insert into sam values('Query ran successfully');
-- Purushottam D/20090811 / E / changed to UNION from UNION ALLto overcome mismatch issue / INC000000389141--Change Over
ELSIF p_report_type = '3'
THEN
--INSERT INTO NATCRM.TAB_DEBUG_REPORT (QRY_TEXT) VALUES(l_str4);
-- COMMIT;
INSERT INTO sam
VALUES (l_str4);
COMMIT;
OPEN p_mr_data_ref_cur FOR (l_str4);
END IF;
EXCEPTION
WHEN OTHERS
THEN
--select length (l_str4) into l_str4 from dual;
--l_str3:=l_str3||' '||l_str3a;
l_str3:=sqlerrm;
INSERT INTO sam
VALUES (l_str3);
--
COMMIT;
DBMS_OUTPUT.put_line ('CSAT REPORT:' || SQLERRM);
END;
/
The issue is in the "p_report_type = '2'" parts, did you change something to that part recently? It's by far the longest query that is generated, maybe it doesn't fit in l_str3, what are the lengths if you see any in dbms_output for "Length of 1..4"? What if you (for testing) comment out the lines between 248 and 278, does that make any difference?
ASKER
actually.. the length function itself is failing.. i tried to see the length using the v_length variable, but with no luck. in case i comment out the v_length variable it will throw error there itself and go in exception block with some different error.. unimplemented feature or conversion requested something like this...
regarding the change... possibly that could have got a change.. even then we need to have the same code working , if not we have got in a situation to fix it atleast..!!
this error is reproducable, you too can execute this with same parameters and can see this..
regarding the change... possibly that could have got a change.. even then we need to have the same code working , if not we have got in a situation to fix it atleast..!!
this error is reproducable, you too can execute this with same parameters and can see this..
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 slightwv for the suggestion.. after making the suggested changes.. i was able to get the count of characters., ie., 4325 but i want to get this inserted in a table.. how can i accomplish that.. still the insert statement is failing with same error..
ASKER
used the same logic for substr too.. splitted the characters into two parts, first part consisting of 1500 characters and second part as remaining string and inserted that in two parts.. thank you very much slightwv for the suggestion..
Glad to help.
ASKER