?
Solved

ORA-01461: can bind a LONG value only for insert into a LONG column

Posted on 2012-05-29
9
Medium Priority
?
1,768 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Wasim Akram Shaik
  • 5
  • 2
  • 2
9 Comments
 
LVL 16

Author Comment

by:Wasim Akram Shaik
ID: 38023873
database version is 10.2.0.1
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 38023978
I see no procedure?
0
 
LVL 16

Author Comment

by:Wasim Akram Shaik
ID: 38023990
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 38024542
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
 
LVL 16

Author Comment

by:Wasim Akram Shaik
ID: 38025296
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38026914
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
 
LVL 16

Author Comment

by:Wasim Akram Shaik
ID: 38026935
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
 
LVL 16

Author Closing Comment

by:Wasim Akram Shaik
ID: 38026974
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38026989
Glad to help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question