Home » SQL & PL/SQL » SQL & PL/SQL » Appending CLOB (Oracle 10g)
Appending CLOB [message #664001] Mon, 03 July 2017 01:30 Go to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I have defined a CLOB variable and am trying to append number of rows to it. Once the data exceeds 4000 bytes, i get ORA-01461: can bind a LONG value only for insert into a LONG column, whereas the variable is CLOB which should hold 4GB data. Why do i get this error ?
Re: Appending CLOB [message #664004 is a reply to message #664001] Mon, 03 July 2017 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because you not what you think you did and did something wrong.
Post your code.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Appending CLOB [message #664005 is a reply to message #664004] Mon, 03 July 2017 02:21 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
v_data is the CLOB variable and also i have commented below where i get the error.

CREATE OR replace PROCEDURE Dig_serv_jobs_esclate_test 
AS 
  v_from       VARCHAR2(80) := 'abc@xyz.com'; 
  v_recipient  VARCHAR2(180) := 'abc@xyz.com,'; --, comma required 
  v_cc         VARCHAR2(180); 
  v_subject    VARCHAR2(80) := 'Jobs Not Actioned Since'; 
  v_mail_host  VARCHAR2(30) := '172.16.0.5'; 
  v_mail_conn  utl_smtp.connection; 
  crlf         VARCHAR2(2) := Chr(13) 
                      || Chr(10); 
  v_data       CLOB; 
  m_data_fetch CHAR(1); 
  slen         NUMBER; 
  v_addr       VARCHAR2(2000); 
BEGIN 
    v_from := 'abc@xyz.com'; 

    v_recipient := 'abc@xyz.com,'; --, comma required 

    v_cc := 'abc@xyz.com'; 

    v_subject := 'Esclation Email'; 

    slen := 1; 

    v_data := NULL; 

    m_data_fetch := 'N'; 

    v_data := v_data 
              || 
'JOB_LOCN, JOB_DATE,JOB_NO,JOB_TYPE, GOODS_TYPE, OLD_STATUS, OLD_STATUS_DATE, NEW_STATUS, NEW_STATUS_DATE, NEW_STATUS_AGEING, JOB_AGEING_DAYS, REMARKS, CUSTOMER_NAME, PHONE_NO, ANNOTATION, ITEM_CODE, SERIAL_NO, STOCK_EXPECT_DATE'
; 

FOR rec IN (SELECT rrh_doc_src_locn_code, 
                   rrh_dt, 
                   B.jsc_job_no 
                   JOB_NO, 
                   rrh_flex_03, 
                   vssv_short_name, 
                   (SELECT vssv_code 
                           ||'-' 
                           ||vssv_name 
                    FROM   im_vs_static_value 
                    WHERE  vssv_vs_code LIKE 'STATUS_CTC' 
                           AND vssv_code = B.jsc_curr_status) 
                              OLD_STATUS, 
                   Nvl((SELECT Max(jsc_cr_dt) 
                        FROM   om_job_sta_change_ctc C 
                        WHERE  C.jsc_job_no = B.jsc_job_no 
                               AND C.jsc_new_status = 
                   B.jsc_curr_status), rrh_dt) 
                              OLD_STATUS_DATE, 
                   (SELECT vssv_code 
                           ||'-' 
                           ||vssv_name 
                    FROM   im_vs_static_value 
                    WHERE  vssv_vs_code LIKE 'STATUS_CTC' 
                           AND vssv_code = B.jsc_new_status) 
                              NEW_STATUS, 
                   B.jsc_cr_dt 
                              NEW_STATUS_DATE, 
                   Trunc(SYSDATE) - Trunc(B.jsc_cr_dt) 
                              NEW_STATUS_AGEING, 
                   Trunc(SYSDATE) - rrh_dt 
                              JOB_AGEING_DAYS, 
                   Replace(Replace(B.jsc_remarks, Chr(13), ''), Chr(10), '') 
                   REMARKS, 
                   B.jsc_cust_name, 
                   Replace(B.jsc_tel, ',', '/') 
                   JSC_TEL, 
                   B.jsc_annotation, 
                   B.jsc_item_code, 
                   B.jsc_srno, 
                   (SELECT Stragg(jscs_item_code 
                                  ||'=>' 
                                  ||jscs_exp_dt) 
                    FROM   om_job_sta_change_spare 
                    WHERE  jscs_job_no = B.jsc_job_no 
                           AND jscs_exp_dt IS NOT NULL) 
                              STOCK_EXPECT_DT 
            FROM   (SELECT DISTINCT jsc_job_no, 
                                    Max(jsc_cr_dt) 
                                      over( 
                                        PARTITION BY jsc_job_no) JSC_CR_DT 
                    FROM   om_job_sta_change_ctc) A, 
                   om_job_sta_change_ctc B, 
                   ot_job_head, 
                   ot_rma_req_head, 
                   im_vs_static_value 
            WHERE  A.jsc_job_no = B.jsc_job_no 
                   AND A.jsc_cr_dt = B.jsc_cr_dt 
                   AND jh_flex_17 = B.jsc_job_no 
                   AND Nvl(jh_flex_04, '*') NOT IN ( 
                       'CA', 'TCP', 'CP', 'REL', 
                       'DEL', 'REP', 'RTO', 'RTOF', 
                       'WPAY', 'PRO' ) 
                   AND To_char(rrh_dt, 'YYYYMM') >= '201611' 
                   AND Trunc(B.jsc_cr_dt) <= '29-JUN-2017' 
                   AND rrh_txn_code 
                       || '-' 
                       || rrh_no = jh_flex_17 
                   AND vssv_vs_code = 'GOODTYPE_CTC' 
                   AND vssv_code = rrh_flex_05 
                   AND vssv_short_name <> 'AC' 
                   AND B.jsc_new_status IN ( 'FF', 'PRA', 'WP', 'FS', 
                                             'SW', 'UR', 'CO', 'PNR', 
                                             'CB', 'PR', 'RS', 'NA' ) 
            ORDER  BY 1, 
                      2) LOOP 
    --dummys('Size of V_Data '||LengthB(V_DATA)); 
    m_data_fetch := 'Y'; 

    v_data := v_data 
              || Chr(13) 
              || Chr(10) 
              || rec.rrh_doc_src_locn_code 
              || ',' 
              ||rec.rrh_dt 
              || ',' 
              || rec.job_no 
              || ',' 
              || rec.rrh_flex_03 
              || ',' 
              || rec.vssv_short_name 
              || ',' 
              || rec.old_status 
              || ',' 
              ||rec.old_status_date 
              || ',' 
              || rec.new_status 
              || ',' 
              || rec.new_status_date 
              || ',' 
              ||rec.new_status_ageing 
              || ',' 
              ||rec.job_ageing_days 
              || ',' 
              ||rec.remarks 
              || ',' 
              || rec.jsc_cust_name 
              || ',' 
              || rec.jsc_tel 
              || ',' 
              || rec.jsc_annotation 
              || ',' 
              || rec.jsc_item_code 
              || ',' 
              || rec.jsc_srno 
              || ',' 
              ||rec.stock_expect_dt; 
[color=orangered][b]--Here when the LengthB(V_DATA) just exceeds 4000, i get ORA-01461: can bind a LONG value only for insert into a LONG column [/b][/color]
END LOOP; 

v_mail_conn := sys.utl_smtp.Open_connection(v_mail_host, 25); 

sys.utl_smtp.Helo(v_mail_conn, v_mail_host); 

sys.utl_smtp.Mail(v_mail_conn, v_from); 

WHILE ( Instr(v_recipient, ',', slen) > 0 ) LOOP 
    v_addr := Substr(v_recipient, slen, Instr(Substr(v_recipient, slen), ',') 
                                        - 1); 

    slen := slen + Instr(Substr(v_recipient, slen), ','); 

    utl_smtp.Rcpt(v_mail_conn, v_addr); 
END LOOP; 

utl_smtp.Rcpt(v_mail_conn, v_cc); 

IF m_data_fetch = 'Y' THEN 
  utl_smtp.Data(v_mail_conn, 'Date: ' 
                             || To_char(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') 
                             || crlf 
                             || 'From: ' 
                             || v_from 
                             || crlf 
                             || 'Subject: ' 
                             || v_subject 
                             ||' ' 
                             ||'29-JUN-2017' 
                             || crlf 
                             || 'To: ' 
                             || v_recipient 
                             || crlf 
                             || 'Cc: ' 
                             || v_cc 
                             || crlf 
                             || 'MIME-Version: 1.0' 
                             || crlf 
                             || -- Use MIME mail standard 
                             'Content-Type: multipart/mixed;' 
                             || crlf 
                             || ' boundary="-----SECBOUND"' 
                             || crlf 
                             || crlf 
                             || '-------SECBOUND' 
                             || crlf 
                             || 'Content-Type: text/plain;' 
                             || crlf 
                             || 'Content-Transfer_Encoding: 7bit' 
                             || crlf 
                             || crlf 
                             || 'Dear Sir ,' 
                             || crlf 
                             || -- Message body 
  'Please Find the Report :-  JOBS NOT ACTIONED SINCE 2 DAYS ' 
  || crlf 
  || crlf 
  || 'Sent By,' 
  || crlf 
  ||'ORION' 
  || crlf 
  || 
'(Kindly note "This is Auto Generate Email". Please do not reply this email)' 
  || crlf 
  || crlf 
  || '-------SECBOUND' 
  || crlf 
  || 'Content-Type: text/plain;' 
  || crlf 
  || ' name="excel.csv"' 
  || crlf 
  || 'Content-Transfer_Encoding: 8bit' 
  || crlf 
  || 'Content-Disposition: attachment;' 
  || crlf 
  || ' filename=" excel.csv"' 
  || crlf 
  || crlf 
  || v_data 
  || crlf 
  || -- Content of attachment 
  crlf 
  || '-------SECBOUND--' -- End MIME mail 
  ); 
END IF; 

sys.utl_smtp.Quit(v_mail_conn); 
EXCEPTION 
  WHEN sys.utl_smtp.transient_error OR sys.utl_smtp.permanent_error THEN 
             Raise_application_error(-20000, 'Unable to send mail: ' 
                                             ||v_subject 
                                             ||'=' 
                                             ||v_from 
                                             ||'=' 
                                             ||v_recipient 
                                             ||'=' 
                                             ||v_cc 
                                             ||'=' 
                                             || 
             dbms_utility.format_error_backtrace 
                                             ||'=' 
                                             || SQLERRM); WHEN OTHERS THEN 
             Raise_application_error(-20000, 'Unable to send mail: ' 
                                             ||v_subject 
                                             ||'=' 
                                             ||v_from 
                                             ||'=' 
                                             ||v_recipient 
                                             ||'=' 
                                             ||v_cc 
                                             ||'=' 
                                             || 
             dbms_utility.format_error_backtrace 
                                             ||'=' 
                                             ||SQLERRM); 

             sys.utl_smtp.Quit(v_mail_conn); 
END; 

Re: Appending CLOB [message #664006 is a reply to message #664005] Mon, 03 July 2017 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I suspected you do not correctly use CLOB.
Here's an example on how you have to use it (read only "get_page" procedure and how "l_page" clob variable is used and filled, the rest of the block is useless for you).

Re: Appending CLOB [message #664025 is a reply to message #664006] Mon, 03 July 2017 13:21 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
Use dbms_lob.writeappend
Re: Appending CLOB [message #664027 is a reply to message #664025] Mon, 03 July 2017 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is NOT the core error:
SQL> declare
  2    l clob;
  3  begin
  4    for i in 1..10 loop
  5      dbms_lob.writeappend(l,2000,lpad('x',2000,'x'));
  6    end loop;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1146
ORA-06512: at line 5
Read the link I provided, we will see why the error and dbms_lob.writeappend is not needed.

[Updated on: Mon, 03 July 2017 14:11]

Report message to a moderator

Re: Appending CLOB [message #664032 is a reply to message #664027] Mon, 03 July 2017 23:42 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
u have forgotten to call createtemporary first

/forum/fa/13571/0/

[Updated on: Tue, 04 July 2017 00:12]

Report message to a moderator

Re: Appending CLOB [message #664034 is a reply to message #664032] Tue, 04 July 2017 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have not forgotten it (read the link I provided), I have written the code as OP would do it if it codes it as in his procedure.
So
1/ the problem is NOT "dbms_lob.writeappend" as you suggested
2/ "dbms_lob.writeappend" is not needed.

Re: Appending CLOB [message #664035 is a reply to message #664034] Tue, 04 July 2017 00:36 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I will work on this today and feedback
Re: Appending CLOB [message #664040 is a reply to message #664035] Tue, 04 July 2017 01:22 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I added the below code just before using V_Data variable. Still i get the same error ORA-01461: can bind a LONG value only for insert into a LONG column. The line number points to the line starting with => FOR rec IN (SELECT rrh_doc_src_locn_code, and not to the line where i am trying to append the CLOB var. The cursor actually returns 500+ rows.

dbms_lob.createtemporary (lob_loc => v_data,
                          cache   => TRUE,
                          dur     => dbms_lob.call);
Re: Appending CLOB [message #664041 is a reply to message #664040] Tue, 04 July 2017 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post code with line numbers.
Post code execution with error specifying the line.
Post a description of your table.
Just use SQL*Plus and it will be easy to show us.

[Updated on: Tue, 04 July 2017 01:32]

Report message to a moderator

Re: Appending CLOB [message #664043 is a reply to message #664041] Tue, 04 July 2017 02:20 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I found the problem ORA-01461, and it was because i was trying to debug using a dummy procedure for which the input variable was a Varchar and i was trying to pass the Clob into that Varchar parameter. Now i have removed that dummy proc. Still i get Ora-6502, when appending the CLOB var as seen in screenshot. If i comment that appending portion, then there is no errors.

BEGIN
DIG_SERV_JOBS_ESCLATE_TEST;
END;
BEGIN
*
ERROR at line 1:
ORA-20000: Error : ORA-06512: at "CTCORG.DIG_SERV_JOBS_ESCLATE_TEST", line 13
=ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "CTCORG.DIG_SERV_JOBS_ESCLATE_TEST", line 130
ORA-06512: at line 2

CREATE OR replace PROCEDURE Dig_serv_jobs_esclate_test 
AS 
  v_data CLOB; 
BEGIN 
    dbms_lob.Createtemporary (lob_loc => v_data, CACHE => TRUE, dur => 
    dbms_lob.call); 

    v_data := v_data 
              || 
'JOB_LOCN, JOB_DATE,JOB_NO,JOB_TYPE, GOODS_TYPE, OLD_STATUS, OLD_STATUS_DATE, NEW_STATUS, NEW_STATUS_DATE, NEW_STATUS_AGEING, JOB_AGEING_DAYS, REMARKS, CUSTOMER_NAME, PHONE_NO, ANNOTATION, ITEM_CODE, SERIAL_NO, STOCK_EXPECT_DATE'
; 

FOR rec IN (SELECT rrh_doc_src_locn_code, 
                   rrh_dt, 
                   B.jsc_job_no 
                   JOB_NO, 
                   rrh_flex_03, 
                   vssv_short_name, 
                   (SELECT vssv_code 
                           ||'-' 
                           ||vssv_name 
                    FROM   im_vs_static_value 
                    WHERE  vssv_vs_code LIKE 'STATUS_CTC' 
                           AND vssv_code = B.jsc_curr_status) 
                              OLD_STATUS, 
                   Nvl((SELECT Max(jsc_cr_dt) 
                        FROM   om_job_sta_change_ctc C 
                        WHERE  C.jsc_job_no = B.jsc_job_no 
                               AND C.jsc_new_status = B.jsc_curr_status), rrh_dt 
                   ) 
                              OLD_STATUS_DATE, 
                   (SELECT vssv_code 
                           ||'-' 
                           ||vssv_name 
                    FROM   im_vs_static_value 
                    WHERE  vssv_vs_code LIKE 'STATUS_CTC' 
                           AND vssv_code = B.jsc_new_status) 
                              NEW_STATUS, 
                   B.jsc_cr_dt 
                              NEW_STATUS_DATE, 
                   Trunc(SYSDATE) - Trunc(B.jsc_cr_dt) 
                              NEW_STATUS_AGEING, 
                   Trunc(SYSDATE) - rrh_dt 
                              JOB_AGEING_DAYS, 
                   Replace(Replace(B.jsc_remarks, Chr(13), ''), Chr(10), '') 
                   REMARKS, 
                   B.jsc_cust_name, 
                   Replace(B.jsc_tel, ',', '/') 
                   JSC_TEL, 
                   B.jsc_annotation, 
                   B.jsc_item_code, 
                   B.jsc_srno, 
                   (SELECT Stragg(jscs_item_code 
                                  ||'=>' 
                                  ||jscs_exp_dt) 
                    FROM   om_job_sta_change_spare 
                    WHERE  jscs_job_no = B.jsc_job_no 
                           AND jscs_exp_dt IS NOT NULL) 
                              STOCK_EXPECT_DT 
            FROM   (SELECT DISTINCT jsc_job_no, 
                                    Max(jsc_cr_dt) 
                                      over( 
                                        PARTITION BY jsc_job_no) JSC_CR_DT 
                    FROM   om_job_sta_change_ctc) A, 
                   om_job_sta_change_ctc B, 
                   ot_job_head, 
                   ot_rma_req_head, 
                   im_vs_static_value 
            WHERE  A.jsc_job_no = B.jsc_job_no 
                   AND A.jsc_cr_dt = B.jsc_cr_dt 
                   AND jh_flex_17 = B.jsc_job_no 
                   AND Nvl(jh_flex_04, '*') NOT IN ( 
                       'CA', 'TCP', 'CP', 'REL', 
                       'DEL', 'REP', 'RTO', 'RTOF', 
                       'WPAY', 'PRO' ) 
                   AND To_char(rrh_dt, 'YYYYMM') >= '201611' 
                   AND Trunc(B.jsc_cr_dt) <= '04-jul-2017' 
                   AND rrh_txn_code 
                       || '-' 
                       || rrh_no = jh_flex_17 
                   AND vssv_vs_code = 'GOODTYPE_CTC' 
                   AND vssv_code = rrh_flex_05 
                   AND vssv_short_name <> 'AC' 
                   AND B.jsc_new_status IN ( 'FF', 'PRA', 'WP', 'FS', 
                                             'SW', 'UR', 'CO', 'PNR', 
                                             'CB', 'PR', 'RS', 'NA' ) 
            ORDER  BY 1, 
                      2) LOOP 
    v_data := v_data 
              || Chr(13) 
              || Chr(10) 
              || rec.rrh_doc_src_locn_code 
              || ',' 
              ||rec.rrh_dt 
              || ',' 
              || rec.job_no 
              || ',' 
              || rec.rrh_flex_03 
              || ',' 
              || rec.vssv_short_name 
              || ',' 
              || rec.old_status 
              || ',' 
              ||rec.old_status_date 
              || ',' 
              || rec.new_status 
              || ',' 
              || rec.new_status_date 
              || ',' 
              ||rec.new_status_ageing 
              || ',' 
              ||rec.job_ageing_days 
              || ',' 
              ||rec.remarks 
              || ',' 
              || rec.jsc_cust_name 
              || ',' 
              || rec.jsc_tel 
              || ',' 
              || rec.jsc_annotation 
              || ',' 
              || rec.jsc_item_code 
              || ',' 
              || rec.jsc_srno 
              || ',' 
              ||rec.stock_expect_dt; 
END LOOP; 
EXCEPTION 
  WHEN OTHERS THEN 
             Raise_application_error(-20000, 'Error : ' 
                                             || 
             dbms_utility.format_error_backtrace 
                                             ||'=' 
                                             ||SQLERRM); 
END; 
Re: Appending CLOB [message #664044 is a reply to message #664043] Tue, 04 July 2017 03:18 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
if you use dbms_lob.Createtemporary to initialize BLOB then use appendwrite to combine result, not "||" operator.
Re: Appending CLOB [message #664045 is a reply to message #664044] Tue, 04 July 2017 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

v_data is NOT a BLOB, it is a CLOB.

Re: Appending CLOB [message #664046 is a reply to message #664043] Tue, 04 July 2017 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I found the problem ORA-01461, and it was because i was trying to debug using a dummy procedure for which the input variable was a Varchar and i was trying to pass the Clob into that Varchar parameter. Now i have removed that dummy proc. Still i get Ora-6502, when appending the CLOB var as seen in screenshot. If i comment that appending portion, then there is no errors.

What don't you understand in
Quote:
Post code with line numbers.
Post code execution with error specifying the line.
Post a description of your table.
Just use SQL*Plus and it will be easy to show us
.
In addition, read WHEN OTHERS and you will see yours is just stupid as repeated many times here.
In the end, also search for "SQL injection" I suspect you will use what you are generating in a dynamic statement.

Re: Appending CLOB [message #664050 is a reply to message #664045] Tue, 04 July 2017 04:17 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
yes, CLOB ofcourse - my mistake Smile
Re: Appending CLOB [message #664052 is a reply to message #664050] Tue, 04 July 2017 04:25 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
Yes, ofcourse the problem is likely to be with data in SELECT statement.
You should try to execute that SELECT statement outside the procedure.

PS: I suggested to use writeappend because of best performance in that case (IMHO)

[Updated on: Tue, 04 July 2017 04:25]

Report message to a moderator

Re: Appending CLOB [message #664054 is a reply to message #664052] Tue, 04 July 2017 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I suggested to use writeappend because of best performance in that case
Based on what this should be "better" than ||?

Re: Appending CLOB [message #664055 is a reply to message #664046] Tue, 04 July 2017 05:56 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Thank you for the explanations on When_Others. I have not provided any table description as the errors is exactly on the line of appending the CLOB. Hope rest is below.

SQL> CREATE OR replace PROCEDURE Dig_serv_jobs_esclate_test
  2  AS
  3    v_data CLOB;
  4  BEGIN
  5      dbms_lob.Createtemporary (lob_loc => v_data, CACHE => TRUE, dur =>
  6      dbms_lob.call);
  7  
  8      v_data := v_data
  9                ||
 10  'JOB_LOCN, JOB_DATE,JOB_NO,JOB_TYPE, GOODS_TYPE, OLD_STATUS, OLD_STATUS_DATE, NEW_STATUS, NEW_STATUS_DATE, NEW_STATUS_AGEING, JOB_AGEING_DAYS, REMARKS, CUSTOMER_NAME, PHONE_NO, ANNOTATION, ITEM_CODE, SERIAL_NO, STOCK_EXPECT_DATE'
 11  ;
 12  
 13  FOR rec IN (SELECT rrh_doc_src_locn_code,rrh_dt,B.jsc_job_no JOB_NO,rrh_flex_03,
 14                     vssv_short_name,(
 15                                SELECT vssv_code
 16                                        ||'-'
 17                                        ||vssv_name
 18                                        FROM   im_vs_static_value
 19                                        WHERE  vssv_vs_code LIKE 'STATUS_CTC'
 20                                               AND vssv_code = B.jsc_curr_status)
 21                                     OLD_STATUS,
 22                                Nvl((SELECT Max(jsc_cr_dt)
 23                                        FROM   om_job_sta_change_ctc C
 24                                        WHERE  C.jsc_job_no = B.jsc_job_no
 25                                               AND C.jsc_new_status =
 26                                                   B.jsc_curr_status),
 27                                rrh_dt)
 28                                        OLD_STATUS_DATE,(SELECT vssv_code
 29                                                                ||'-'
 30                                                                ||vssv_name
 31                                                         FROM   im_vs_static_value
 32                                                         WHERE
 33                                vssv_vs_code LIKE 'STATUS_CTC'
 34                                AND vssv_code = B.jsc_new_status)
 35                                                        NEW_STATUS,
 36                                B.jsc_cr_dt NEW_STATUS_DATE,
 37                                Trunc(SYSDATE) - Trunc(B.jsc_cr_dt)
 38                                NEW_STATUS_AGEING,
 39                                Trunc(SYSDATE) - rrh_dt JOB_AGEING_DAYS,Replace(
 40                                Replace(B.jsc_remarks, Chr(13), '')
 41                                , Chr(10), '') REMARKS,B.jsc_cust_name,
 42                                Replace(B.jsc_tel, ',', '/') JSC_TEL,
 43                     B.jsc_annotation,
 44                                B.jsc_item_code,B.jsc_srno,
 45                                        (SELECT Stragg(jscs_item_code
 46                                                       ||'=>'
 47                                                       ||jscs_exp_dt)
 48                                         FROM   om_job_sta_change_spare
 49                                         WHERE  jscs_job_no = B.jsc_job_no
 50                                                AND jscs_exp_dt IS NOT NULL)
 51                                        STOCK_EXPECT_DT
 52              FROM   (SELECT DISTINCT jsc_job_no,Max(jsc_cr_dt)
 53                                                   over(
 54                                                     PARTITION BY jsc_job_no)
 55                                                 JSC_CR_DT
 56                      FROM   om_job_sta_change_ctc) A,om_job_sta_change_ctc B,
 57                     ot_job_head,
 58                     ot_rma_req_head,im_vs_static_value
 59              WHERE  A.jsc_job_no = B.jsc_job_no
 60                     AND A.jsc_cr_dt = B.jsc_cr_dt
 61                     AND jh_flex_17 = B.jsc_job_no
 62                     AND Nvl(jh_flex_04, '*') NOT IN (
 63                         'CA', 'TCP', 'CP', 'REL',
 64                         'DEL', 'REP', 'RTO', 'RTOF',
 65                         'WPAY', 'PRO' )
 66                     AND To_char(rrh_dt, 'YYYYMM') >= '201611'
 67                     AND Trunc(B.jsc_cr_dt) <= '04-jul-2017'
 68                     AND rrh_txn_code
 69                         || '-'
 70                         || rrh_no = jh_flex_17
 71                     AND vssv_vs_code = 'GOODTYPE_CTC'
 72                     AND vssv_code = rrh_flex_05
 73                     AND vssv_short_name <> 'AC'
 74                     AND B.jsc_new_status IN ( 'FF', 'PRA', 'WP', 'FS',
 75                                               'SW', 'UR', 'CO', 'PNR',
 76                                               'CB', 'PR', 'RS', 'NA' )
 77              ORDER  BY 1,2) LOOP
 78      v_data := v_data
 79                || Chr(13)
 80                || Chr(10)
 81                || rec.rrh_doc_src_locn_code
 82                || ','
 83                ||rec.rrh_dt
 84                || ','
 85                || rec.job_no
 86                || ','
 87                || rec.rrh_flex_03
 88                || ','
 89                || rec.vssv_short_name
 90                || ','
 91                || rec.old_status
 92                || ','
 93                ||rec.old_status_date
 94                || ','
 95                || rec.new_status
 96                || ','
 97                || rec.new_status_date
 98                || ','
 99                ||rec.new_status_ageing
100                || ','
101                ||rec.job_ageing_days
102                || ','
103                ||rec.remarks
104                || ','
105                || rec.jsc_cust_name
106                || ','
107                || rec.jsc_tel
108                || ','
109                || rec.jsc_annotation
110                || ','
111                || rec.jsc_item_code
112                || ','
113                || rec.jsc_srno
114                || ','
115                ||rec.stock_expect_dt;
116  END LOOP;
117  END;
118  
119  /

Procedure created.

SQL> exec Dig_serv_jobs_esclate_test;
BEGIN Dig_serv_jobs_esclate_test; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error 
ORA-06512: at "CTCORG.DIG_SERV_JOBS_ESCLATE_TEST", line 78 
ORA-06512: at line 1 
Re: Appending CLOB [message #664056 is a reply to message #664055] Tue, 04 July 2017 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem is not appending the CLOB, it is in what you append to the CLOB which is larger than a VARCHAR2.
Append bit per bit.

What you try to do is to generate a CSV file, isn't it? If this is the cae, it is far from the best way.
Realize that "rec.new_status_date" relies on user NLS_DATE_FORMAT which may not what you expect.

Re: Appending CLOB [message #664058 is a reply to message #664056] Tue, 04 July 2017 06:26 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I tried appending the CLOB after different filter criteria to the cursor, to validate if any specific record has a larger data. But every time i noticed, when the LENGTHB(V_DATA) exceeds 33000 then i get the same error inspite of ordering / filtering the cursor output to limit the rows. Does this byte signify anything ?
Re: Appending CLOB [message #664061 is a reply to message #664058] Tue, 04 July 2017 06:42 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
declare
  v_data CLOB;

  procedure append_str(s varchar2) is
    l       number := length(s);
  begin
    dbms_lob.writeappend(v_data, l, s);
  end;

BEGIN
    dbms_lob.Createtemporary (v_data, true);

    append_str('JOB_LOCN, JOB_DATE,JOB_NO,JOB_TYPE, GOODS_TYPE, OLD_STATUS, OLD_STATUS_DATE, NEW_STATUS, NEW_STATUS_DATE, NEW_STATUS_AGEING, JOB_AGEING_DAYS, REMARKS, CUSTOMER_NAME, PHONE_NO, ANNOTATION, ITEM_CODE, SERIAL_NO, STOCK_EXPECT_DATE');

    for rec in (
      select
        lpad('x', 50, 'x')     as rrh_doc_src_locn_code,
        lpad('x', 50, 'x')     as rrh_dt,
        lpad('x', 50, 'x')     as job_no,
        lpad('x', 50, 'x')     as rrh_flex_03,
        lpad('x', 50, 'x')     as vssv_short_name,
        lpad('x', 50, 'x')     as old_status,
        lpad('x', 50, 'x')     as old_status_date,
        lpad('x', 50, 'x')     as new_status,
        lpad('x', 50, 'x')     as new_status_date,
        lpad('x', 50, 'x')     as new_status_ageing,
        lpad('x', 50, 'x')     as job_ageing_days,
        lpad('x', 50, 'x')     as remarks,
        lpad('x', 50, 'x')     as jsc_cust_name,
        lpad('x', 50, 'x')     as jsc_tel,
        lpad('x', 50, 'x')     as jsc_annotation,
        lpad('x', 50, 'x')     as jsc_item_code,
        lpad('x', 50, 'x')     as jsc_srno,
        lpad('x', 50, 'x')     as stock_expect_dt
        from dual
        connect by level <= 1000) loop
    append_str(
      Chr(13)
        || Chr(10)
        || rec.rrh_doc_src_locn_code
        || ','
        ||rec.rrh_dt
        || ','
        || rec.job_no
        || ','
        || rec.rrh_flex_03
        || ','
        || rec.vssv_short_name
        || ','
        || rec.old_status
        || ','
        ||rec.old_status_date
        || ','
        || rec.new_status
        || ','
        || rec.new_status_date
        || ','
        ||rec.new_status_ageing
        || ','
        ||rec.job_ageing_days
        || ','
        ||rec.remarks
        || ','
        || rec.jsc_cust_name
        || ','
        || rec.jsc_tel
        || ','
        || rec.jsc_annotation
        || ','
        || rec.jsc_item_code
        || ','
        || rec.jsc_srno
        || ','
        ||rec.stock_expect_dt);
  end loop;
  dbms_output.put_line(length(v_data));
end;

works fine:
919227
PL/SQL block completed (00:00:00)
Re: Appending CLOB [message #664062 is a reply to message #664061] Tue, 04 July 2017 07:00 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
YES..finally it works as suggested above by Bbob.

Thank you both, Bbob and Michel.
Re: Appending CLOB [message #664236 is a reply to message #664062] Sun, 09 July 2017 03:39 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
When i attach the CLOB as my email attachment, am getting invalid SMTP Operation

SQL> CREATE OR replace PROCEDURE Dig_serv_jobs_esclate_test1
  2  AS
  3    v_from      VARCHAR2(80) := 'raj.s@cg.com';
  4    v_recipient VARCHAR2(180) := 'raj.s@cg.com,'; --, comma required
  5    v_subject   VARCHAR2(80) := 'Jobs Not Actioned Since';
  6    v_mail_host VARCHAR2(30) := '172.16.0.5';
  7    v_mail_conn utl_smtp.connection;
  8    crlf        VARCHAR2(2) := Chr(13)
  9                        || Chr(10);
 10    v_data      CLOB;
 11    v_addr      VARCHAR2(200);
 12    v_long      LONG;
 13    v_len       INTEGER;
 14    v_index     INTEGER;
 15    slen        NUMBER := 1;
 16  BEGIN
 17      slen := 1;
 18  
 19      v_data := Empty_clob();
 20  
 21      dbms_lob.Createtemporary (lob_loc => v_data, CACHE => TRUE, dur =>
 22      dbms_lob.call);
 23  
 24      FOR rec IN (SELECT *
 25                  FROM   ot_rma_req_head
 26                  WHERE  ROWNUM < 2500) LOOP
 27          v_long := Chr(13)
 28                    || Chr(10)
 29                    || rec.rrh_doc_src_locn_code
 30                    || ','
 31                    ||rec.rrh_dt
 32                    || ','
 33                    || rec.rrh_txn_code
 34                    || ','
 35                    || rec.rrh_no
 36                    || ','
 37                    || rec.rrh_cust_code
 38                    || ','
 39                    || rec.rrh_bill_to_addr_code
 40                    || ','
 41                    ||rec.rrh_addr_line_1
 42                    || ','
 43                    || rec.rrh_addr_line_2
 44                    || ','
 45                    || rec.rrh_addr_line_3
 46                    || ','
 47                    ||rec.rrh_addr_line_4
 48                    || ','
 49                    ||rec.rrh_addr_line_5
 50                    || ','
 51                    ||rec.rrh_email
 52                    || ','
 53                    || rec.rrh_tel
 54                    || ','
 55                    || rec.rrh_mobile
 56                    || ','
 57                    || rec.rrh_flex_01
 58                    || ','
 59                    || rec.rrh_flex_02
 60                    || ','
 61                    || rec.rrh_flex_03
 62                    || ','
 63                    ||rec.rrh_flex_04;
 64  
 65          dbms_lob.Writeappend(v_data, Length(v_long), v_long);
 66      END LOOP;
 67  
 68      v_mail_conn := sys.utl_smtp.Open_connection(v_mail_host, 25);
 69  
 70      sys.utl_smtp.Helo(v_mail_conn, v_mail_host);
 71  
 72      sys.utl_smtp.Mail(v_mail_conn, v_from);
 73  
 74      -- multiple recipients
 75      WHILE ( Instr(v_recipient, ',', slen) > 0 ) LOOP
 76          v_addr := Substr(v_recipient, slen, Instr(Substr(v_recipient, slen), ','
 77                                              )
 78                                              - 1);
 79  
 80          slen := slen + Instr(Substr(v_recipient, slen), ',');
 81  
 82          utl_smtp.Rcpt(v_mail_conn, v_addr);
 83      END LOOP;
 84  
 85      utl_smtp.Data(v_mail_conn, 'Date: '
 86                                 || To_char(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
 87                                 || crlf
 88                                 || 'From: '
 89                                 || v_from
 90                                 || crlf
 91                                 || 'Subject: '
 92                                 || v_subject
 93                                 ||' '
 94                                 ||crlf
 95                                 || 'To: '
 96                                 || v_recipient
 97                                 || crlf
 98                                 || 'MIME-Version: 1.0'
 99                                 || crlf
100                                 || -- Use MIME mail standard
101                                 'Content-Type: multipart/mixed;'
102                                 || crlf
103                                 || ' boundary="-----SECBOUND"'
104                                 || crlf
105                                 || crlf
106                                 || '-------SECBOUND'
107                                 || crlf
108                                 || 'Content-Type: text/plain;'
109                                 || crlf
110                                 || 'Content-Transfer_Encoding: 7bit'
111                                 || crlf
112                                 || crlf
113                                 || 'Dear Sir ,'
114                                 || crlf
115                                 || -- Message body
116      'Please Find the Report :-  JOBS NOT ACTIONED SINCE 2 DAYS '
117      || crlf
118      || crlf
119      || 'Sent By,'
120      || crlf
121      ||'ORION'
122      || crlf
123      ||
124  '(Kindly note "This is Auto Generate Email". Please do not reply this email)'
125      || crlf
126      || crlf
127      || '-------SECBOUND'
128      || crlf
129      || 'Content-Type: text/plain;'
130      || crlf
131      || ' name="excel.csv"'
132      || crlf
133      || 'Content-Transfer_Encoding: 8bit'
134      || crlf
135      || 'Content-Disposition: attachment;'
136      || crlf
137      || ' filename=" excel.csv"'
138      || crlf
139      || crlf
140      || crlf
141      || -- Content of attachment
142      crlf
143      || '-------SECBOUND--' -- End MIME mail
144      );
145  
146      -- Write attachment contents
147      v_len := dbms_lob.Getlength(v_data);
148  
149      v_index := 1;
150  
151      WHILE v_index <= v_len LOOP
152          utl_smtp.Write_data(v_mail_conn, dbms_lob.Substr(v_data, 32000, v_index)
153          );
154  
155          v_index := v_index + 32000;
156      END LOOP;
157  
158      -- End attachment
159      sys.utl_smtp.Quit(v_mail_conn);
160  END;
161  
162  /

Procedure created.

SQL> exec dig_serv_jobs_esclate_test1;
BEGIN dig_serv_jobs_esclate_test1; END;

*
ERROR at line 1:
ORA-29277: invalid SMTP operation 
ORA-06512: at "SYS.UTL_SMTP", line 44 
ORA-06512: at "SYS.UTL_SMTP", line 284 
ORA-06512: at "CTCORG.DIG_SERV_JOBS_ESCLATE_TEST1", line 152 
ORA-06512: at line 1 

Re: Appending CLOB [message #664237 is a reply to message #664236] Sun, 09 July 2017 06:03 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-29277: invalid SMTP operation
 *Cause:  The SMTP operation was invalid at the current stage of the SMTP
          transaction.
 *Action: Retry the SMTP operation at the appropriate stage of the SMTP
          transaction.
You missed the call to utl_smtp.open_data after the recipient list and before the first write_data.


Previous Topic: Get last 200 records from table in oracle
Next Topic: Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement
Goto Forum:
  


Current Time: Fri Mar 29 03:44:31 CDT 2024