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',
                               '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
LVL 16
Wasim Akram ShaikAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Outside pl/sql a string can only be 4000 characters.

>>select length(l_str3) into v_length from dual;

To check the length do:
v_length := length(l_str3);

or just:
dbms_output.Put_line('Length of 1 '||  length(l_str3));
                                                       


That said:  What column is a LONG?
0
 
Wasim Akram ShaikAuthor Commented:
database version is 10.2.0.1
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
I see no procedure?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Wasim Akram ShaikAuthor Commented:
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; 
/

Open in new window

0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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?
0
 
Wasim Akram ShaikAuthor Commented:
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..
0
 
Wasim Akram ShaikAuthor Commented:
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..
0
 
Wasim Akram ShaikAuthor Commented:
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..
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.