Home » SQL & PL/SQL » SQL & PL/SQL » Appending CLOB (Oracle 10g)
Appending CLOB [message #664001] |
Mon, 03 July 2017 01:30 |
chat2raj.s
Messages: 163 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 #664005 is a reply to message #664004] |
Mon, 03 July 2017 02:21 |
chat2raj.s
Messages: 163 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 #664027 is a reply to message #664025] |
Mon, 03 July 2017 14:10 |
|
Michel Cadot
Messages: 68693 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 #664040 is a reply to message #664035] |
Tue, 04 July 2017 01:22 |
chat2raj.s
Messages: 163 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 #664043 is a reply to message #664041] |
Tue, 04 July 2017 02:20 |
chat2raj.s
Messages: 163 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 #664052 is a reply to message #664050] |
Tue, 04 July 2017 04:25 |
|
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 #664055 is a reply to message #664046] |
Tue, 04 July 2017 05:56 |
chat2raj.s
Messages: 163 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 #664058 is a reply to message #664056] |
Tue, 04 July 2017 06:26 |
chat2raj.s
Messages: 163 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 |
|
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 #664236 is a reply to message #664062] |
Sun, 09 July 2017 03:39 |
chat2raj.s
Messages: 163 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 |
|
Michel Cadot
Messages: 68693 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.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:49:23 CDT 2024
|