Home » SQL & PL/SQL » SQL & PL/SQL » Append more rows in single clob column (Oracle,10g,windows server 2003)
Append more rows in single clob column [message #581621] Tue, 09 April 2013 06:17 Go to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
Hi

I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.

Can you help me in acheving this in best possible method.

thanks.

// in the below procedure test_clob.textt is clob field.


CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
v_dto|| '0' ||to_number(TO_CHAR (SYSDATE, 'ddmmyyhhmmss'));
v_dd_count NUMBER (9);
v_sub_count NUMBER (9);
v_seq_loop NUMBER (9) := 2;
v_total NUMBER (20, 2);
BEGIN
SELECT COUNT (*) AS dd, SUM (cnt) AS subc,
COUNT (*) + SUM (cnt) + 2 AS seq_1, ltrim(to_char(round(SUM (gtc),2),'9999990.99')), ltrim(to_char(round(SUM (empc),2),'9999990.99')),ltrim(to_char(round(SUM (gtc)+ SUM (empc),2),'9999990.99'))
INTO v_dd_count, v_sub_count,
v_mas_seq, v_gov_total, v_emp_total,v_total
FROM (SELECT dto, dd, COUNT (*) AS cnt, SUM (b.gt) AS gtc,
SUM (b.emp_cont) AS empc
FROM pfns.pf_ns_schedule_fu b
WHERE dto = v_dto
GROUP BY dto, dd) a;

v_text_exp :=
'1^FH^P^'
|| v_dto
|| '^1^^^^^^^'||chr(10)||'2^BH^1^R^'
|| v_dto
|| '^'
|| TO_CHAR (SYSDATE, 'ddmmyyyy')
|| '^'
|| v_batchno
|| '^^'
|| v_dd_count
|| '^'
|| v_sub_count
|| '^'
|| ltrim(to_char(v_gov_total,'9999990.99'))
|| '^'
|| ltrim(to_char(v_emp_total,'9999990.99'))
|| '^^'
|| ltrim(to_char(v_total,'9999990.99'))
|| '^';

FOR i IN (SELECT ROWNUM AS sno, a.*
FROM (SELECT dto, dd, COUNT (*) AS cnt,
ltrim(to_char(ROUND (SUM (b.gt), 2),'9999990.99')) AS gtc,
ltrim(to_char(ROUND (SUM (b.emp_cont), 2),'9999990.99')) AS empc
FROM pfns.pf_ns_schedule_fu b
WHERE dto = 4011486
GROUP BY dto, dd) a)
LOOP
v_seq_loop := v_seq_loop + 1;
v_text_exp :=
v_text_exp||chr(10)
|| v_seq_loop
|| '^DH^'
|| '1^'
|| i.sno
|| '^'
|| i.dd
|| '^'
|| i.cnt
|| '^'
|| i.gtc
|| '^'
|| i.empc
|| '^^';

FOR j IN (SELECT ROWNUM AS lineno, s.*
FROM (SELECT pran, ltrim(to_char(round(gt,2),'9999990.99')) gt, ltrim(to_char(round(emp_cont,2),'9999990.99')) emp_cont,
ltrim(to_char(round(gt + emp_cont,2),'9999990.99')) AS total_contribution,
'C' AS typ,
TO_CHAR (salary_date, 'mm') AS smonth,
TO_CHAR (salary_date, 'yyyy') AS syear
FROM pfns.pf_ns_schedule_fu
WHERE dd = i.dd ) s)



LOOP
v_seq_loop := v_seq_loop + 1;
v_text_exp :=
v_text_exp||chr(10)
|| v_seq_loop
|| '^SD^1^'
|| i.sno ||'^'||j.lineno
|| '^'
|| j.pran
|| '^'
|| j.gt
|| '^'
|| j.emp_cont
|| '^^'
|| j.total_contribution
|| '^'
|| j.typ
|| '^'
|| j.smonth
|| '^'
|| j.syear
|| '^Remarks^';
END LOOP;


END LOOP;

INSERT INTO pfns.test_clob
(textt
)
VALUES (v_text_exp
);

COMMIT;
END;
/
Re: Append more rows in single clob column [message #581627 is a reply to message #581621] Tue, 09 April 2013 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Tue, 20 November 2012 09:15
Welcome to the forum.

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" button to verify.
Also always post your Oracle version, with 4 decimals.

...

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

[Updated on: Tue, 09 April 2013 08:45]

Report message to a moderator

Re: Append more rows in single clob column [message #581693 is a reply to message #581627] Wed, 10 April 2013 00:26 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
/* Formatted on 2013/04/10 10:55 (Formatter Plus v4.8.Cool */
CREATE OR REPLACE PROCEDURE pro_test
AS
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20)
:= v_dto || '0' || TO_NUMBER (TO_CHAR (SYSDATE, 'ddmmyyhhmmss'));
v_dd_count NUMBER (9);
v_sub_count NUMBER (9);
v_seq_loop NUMBER (9) := 2;
v_total NUMBER (20, 2);
BEGIN
SELECT COUNT (*) AS dd, SUM (cnt) AS subc,
COUNT (*) + SUM (cnt) + 2 AS seq_1,
LTRIM (TO_CHAR (ROUND (SUM (gtc), 2), '9999990.99')),
LTRIM (TO_CHAR (ROUND (SUM (empc), 2), '9999990.99')),
LTRIM (TO_CHAR (ROUND (SUM (gtc) + SUM (empc), 2), '9999990.99'))
INTO v_dd_count, v_sub_count,
v_mas_seq,
v_gov_total,
v_emp_total,
v_total
FROM (SELECT dto, dd, COUNT (*) AS cnt, SUM (b.gt) AS gtc,
SUM (b.emp_cont) AS empc
FROM pfns.pf_ns_schedule_fu b
WHERE dto = v_dto
GROUP BY dto, dd) a;

v_text_exp :=
'1^FH^P^'
|| v_dto
|| '^1^^^^^^^'
|| CHR (10)
|| '2^BH^1^R^'
|| v_dto
|| '^'
|| TO_CHAR (SYSDATE, 'ddmmyyyy')
|| '^'
|| v_batchno
|| '^^'
|| v_dd_count
|| '^'
|| v_sub_count
|| '^'
|| LTRIM (TO_CHAR (v_gov_total, '9999990.99'))
|| '^'
|| LTRIM (TO_CHAR (v_emp_total, '9999990.99'))
|| '^^'
|| LTRIM (TO_CHAR (v_total, '9999990.99'))
|| '^';

FOR i IN (SELECT ROWNUM AS sno, a.*
FROM (SELECT dto, dd, COUNT (*) AS cnt,
LTRIM (TO_CHAR (ROUND (SUM (b.gt), 2),
'9999990.99'
)
) AS gtc,
LTRIM
(TO_CHAR (ROUND (SUM (b.emp_cont), 2),
'9999990.99'
)
) AS empc
FROM pfns.pf_ns_schedule_fu b
WHERE dto = 4011486
GROUP BY dto, dd) a)
LOOP
v_seq_loop := v_seq_loop + 1;
v_text_exp :=
v_text_exp
|| CHR (10)
|| v_seq_loop
|| '^DH^'
|| '1^'
|| i.sno
|| '^'
|| i.dd
|| '^'
|| i.cnt
|| '^'
|| i.gtc
|| '^'
|| i.empc
|| '^^';

FOR j IN
(SELECT ROWNUM AS lineno, s.*
FROM (SELECT pran,
LTRIM (TO_CHAR (ROUND (gt, 2), '9999990.99')) gt,
LTRIM (TO_CHAR (ROUND (emp_cont, 2), '9999990.99')
) emp_cont,
LTRIM
(TO_CHAR (ROUND (gt + emp_cont, 2), '9999990.99')
) AS total_contribution,
'C' AS typ, TO_CHAR (salary_date, 'mm') AS smonth,
TO_CHAR (salary_date, 'yyyy') AS syear
FROM pfns.pf_ns_schedule_fu
WHERE dd = i.dd) s)
LOOP
v_seq_loop := v_seq_loop + 1;
v_text_exp :=
v_text_exp
|| CHR (10)
|| v_seq_loop
|| '^SD^1^'
|| i.sno
|| '^'
|| j.lineno
|| '^'
|| j.pran
|| '^'
|| j.gt
|| '^'
|| j.emp_cont
|| '^^'
|| j.total_contribution
|| '^'
|| j.typ
|| '^'
|| j.smonth
|| '^'
|| j.syear
|| '^Remarks^';
END LOOP;
END LOOP;

INSERT INTO pfns.test_clob
(textt
)
VALUES (v_text_exp
);
END;
/
Re: Append more rows in single clob column [message #581699 is a reply to message #581693] Wed, 10 April 2013 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 09 April 2013 15:45
From your previous topic:

Michel Cadot wrote on Tue, 20 November 2012 09:15
Welcome to the forum.

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" button to verify.
Also always post your Oracle version, with 4 decimals.

...

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel



It takes 10 seconds to read the second link and know how to format.
DO IT!

Regards
Michel

[Updated on: Wed, 10 April 2013 00:40]

Report message to a moderator

Re: Append more rows in single clob column [message #581709 is a reply to message #581699] Wed, 10 April 2013 01:32 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
CREATE OR replace PROCEDURE Pro_test 
AS 
  v_mas_seq    NUMBER (9); 
  v_gov_total  NUMBER (20, 2); 
  v_emp_total  NUMBER (20, 2); 
  v_text_exp   CLOB; 
  v_pageaccess VARCHAR2 (15); 
  v_dto        NUMBER (7) := 4011486; 
  v_batchno    NUMBER (20) := v_dto 
                           || '0' 
                           || To_number (To_char (SYSDATE, 'ddmmyyhhmmss')); 
  v_dd_count   NUMBER (9); 
  v_sub_count  NUMBER (9); 
  v_seq_loop   NUMBER (9) := 2; 
  v_total      NUMBER (20, 2); 
BEGIN 
    SELECT Count (*)                 AS dd, 
           SUM (cnt)                 AS subc, 
           Count (*) + SUM (cnt) + 2 AS seq_1, 
           Ltrim (To_char (Round (SUM (gtc), 2), '9999990.99')), 
           Ltrim (To_char (Round (SUM (empc), 2), '9999990.99')), 
           Ltrim (To_char (Round (SUM (gtc) + SUM (empc), 2), '9999990.99')) 
    INTO   v_dd_count, v_sub_count, v_mas_seq, v_gov_total, 
    v_emp_total, v_total 
    FROM   (SELECT dto, 
                   dd, 
                   Count (*)        AS cnt, 
                   SUM (b.gt)       AS gtc, 
                   SUM (b.emp_cont) AS empc 
            FROM   pfns.pf_ns_schedule_fu b 
            WHERE  dto = v_dto 
            GROUP  BY dto, 
                      dd) a; 

    v_text_exp := '1^FH^P^' 
                  || v_dto 
                  || '^1^^^^^^^' 
                  || Chr (10) 
                  || '2^BH^1^R^' 
                  || v_dto 
                  || '^' 
                  || To_char (SYSDATE, 'ddmmyyyy') 
                  || '^' 
                  || v_batchno 
                  || '^^' 
                  || v_dd_count 
                  || '^' 
                  || v_sub_count 
                  || '^' 
                  || Ltrim (To_char (v_gov_total, '9999990.99')) 
                  || '^' 
                  || Ltrim (To_char (v_emp_total, '9999990.99')) 
                  || '^^' 
                  || Ltrim (To_char (v_total, '9999990.99')) 
                  || '^'; 

    FOR i IN (SELECT ROWNUM AS sno, 
                     a.* 
              FROM   (SELECT dto, 
                             dd, 
                             Count (*) 
                             AS 
                             cnt, 
                             Ltrim (To_char (Round (SUM (b.gt), 2), '9999990.99'
                                     )) 
                             AS 
                             gtc, 
                             Ltrim (To_char (Round (SUM (b.emp_cont), 2), 
                                    '9999990.99')) AS 
                             empc 
                      FROM   pfns.pf_ns_schedule_fu b 
                      WHERE  dto = 4011486 
                      GROUP  BY dto, 
                                dd) a) LOOP 
        v_seq_loop := v_seq_loop + 1; 

        v_text_exp := v_text_exp 
                      || Chr (10) 
                      || v_seq_loop 
                      || '^DH^' 
                      || '1^' 
                      || i.sno 
                      || '^' 
                      || i.dd 
                      || '^' 
                      || i.cnt 
                      || '^' 
                      || i.gtc 
                      || '^' 
                      || i.empc 
                      || '^^'; 

        FOR j IN (SELECT ROWNUM AS lineno, 
                         s.* 
                  FROM   (SELECT pran, 
                                 Ltrim (To_char (Round (gt, 2), '9999990.99')) 
                                 gt, 
                                 Ltrim (To_char (Round (emp_cont, 2), 
                                        '9999990.99')) 
                                 emp_cont, 
                                 Ltrim (To_char (Round (gt + emp_cont, 2), 
                                        '9999990.99') 
                                 ) AS 
                                         total_contribution, 
                                 'C' 
                                 AS typ, 
                                 To_char (salary_date, 'mm') 
                                 AS 
                                 smonth, 
                                 To_char (salary_date, 'yyyy') 
                                 AS 
                                 syear 
                          FROM   pfns.pf_ns_schedule_fu 
                          WHERE  dd = i.dd) s) LOOP 
            v_seq_loop := v_seq_loop + 1; 

            v_text_exp := v_text_exp 
                          || Chr (10) 
                          || v_seq_loop 
                          || '^SD^1^' 
                          || i.sno 
                          || '^' 
                          || j.lineno 
                          || '^' 
                          || j.pran 
                          || '^' 
                          || j.gt 
                          || '^' 
                          || j.emp_cont 
                          || '^^' 
                          || j.total_contribution 
                          || '^' 
                          || j.typ 
                          || '^' 
                          || j.smonth 
                          || '^' 
                          || j.syear 
                          || '^Remarks^'; 
        END LOOP; 
    END LOOP; 

    INSERT INTO pfns.test_clob 
                (textt) 
    VALUES      (v_text_exp ); 
END; 
Re: Append more rows in single clob column [message #581711 is a reply to message #581709] Wed, 10 April 2013 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you help me in acheving this in best possible method.

You have to:
1/ Tell us what the procedure must actually achieve WITH WORDS, we have not to reverse engineer your code
2/ Post the CREATE statements for ALL Objects and INSERT statements for few rows in each table so we can test what we're thinking about

Regards
Michel
Re: Append more rows in single clob column [message #581736 is a reply to message #581711] Wed, 10 April 2013 04:01 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
Hi Michel,

In this code we are fetching two result set, One is summary data say (1) and other one is details of the summary (2).
the code will format the result data in particular format. We need to concat all the result in single variable and need to insert the variable in a table.

problem is when the (j from above code) query fetches records more than 1000's then it throws ORA-06502: PL/SQL: numeric or value error and when it is in 100's , it is successfull.

Pl suggest best way to handle this.

eg
v_text_exp := 1.a +2.a1+2.a2+2.a3+1.b+2.b1+2.b2+....


thanks.



Re: Append more rows in single clob column [message #581737 is a reply to message #581736] Wed, 10 April 2013 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 10 April 2013 08:38
...
You have to:
1/ Tell us what the procedure must actually achieve WITH WORDS, we have not to reverse engineer your code
2/ Post the CREATE statements for ALL Objects and INSERT statements for few rows in each table so we can test what we're thinking about
...


Do you think with what you say and ONLY what you say, without the code, we can write a code that fits your needs?

Regards
Michel

[Updated on: Wed, 10 April 2013 04:03]

Report message to a moderator

Re: Append more rows in single clob column [message #581760 is a reply to message #581737] Wed, 10 April 2013 07:21 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
Hi Michel

I tried hard to find out the solution, But not sucessfull.
Re: Append more rows in single clob column [message #581763 is a reply to message #581760] Wed, 10 April 2013 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't ask you to find the solution but to help to find it to help you.
Help us to help you and post what is requested.

Regards
Michel
Re: Append more rows in single clob column [message #581930 is a reply to message #581763] Fri, 12 April 2013 00:09 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
What kind of information is required.
Re: Append more rows in single clob column [message #581938 is a reply to message #581930] Fri, 12 April 2013 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read my posts.

Regards
Michel
Re: Append more rows in single clob column [message #582389 is a reply to message #581938] Thu, 18 April 2013 00:14 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
Hi Michel,

Pl find the create object script , few insert statement and procedure.
CREATE TABLE pf_np_sch_fp
(
  pf_np_sch_fpid  INTEGER,
  CANCEL                   CHAR(1 BYTE),
  sourceid                 INTEGER,
  mapname                  VARCHAR2(20 BYTE),
  username                 VARCHAR2(50 BYTE),
  modifiedon               DATE,
  createdby                VARCHAR2(50 BYTE),
  createdon                DATE,
  wkid                     VARCHAR2(15 BYTE),
  app_level                NUMBER(3),
  app_desc                 NUMBER(1),
  app_slevel               NUMBER(3),
  dt                      VARCHAR2(7 BYTE),
  DO                      VARCHAR2(10 BYTE),
  ran                     VARCHAR2(12 BYTE),
  govt                     NUMBER(10,2),
  emp_cont                 NUMBER(10,2),
  employee_id              VARCHAR2(25 BYTE),
  batch_no                 VARCHAR2(20 BYTE),
  transaction_no           VARCHAR2(25 BYTE),
  salary_date              DATE
);

CREATE TABLE test_clob
(
  textt           CLOB,
  batch_no        NUMBER(20),
  transaction_id  VARCHAR2(30 BYTE),
  dt             NUMBER(7),
  sdate           DATE
);


/* Insert statement */

INSERT INTO pf_np_sch_fp
            (pf_np_sch_fpid, CANCEL, sourceid, mapname, username, modifiedon,
             createdby, createdon, wkid, app_level, app_desc, app_slevel, dt,
             DO, ran, govt, emp_cont, employee_id, batch_no, transaction_no,
             salary_date
            )
     VALUES (12345680179, NULL, NULL, NULL, NULL, NULL,
             NULL, NULL, NULL, NULL, NULL, NULL, '4011486',
             'SGV127937A', '111002433916', 10, 10, NULL, NULL, NULL,
             TO_DATE ('07/27/2012 11:57:31 AM', 'MM/DD/YYYY HH:MI:SS AM')
            );
INSERT INTO pf_np_sch_fp
            (pf_np_sch_fpid, CANCEL, sourceid, mapname, username, modifiedon,
             createdby, createdon, wkid, app_level, app_desc, app_slevel, dt,
             DO, ran, govt, emp_cont, employee_id,
             batch_no, transaction_no,
             salary_date
            )
     VALUES (12345680181, NULL, NULL, NULL, NULL, NULL,
             NULL, NULL, NULL, NULL, NULL, NULL, '4011486',
             'SGV127939C', '110001946533', 10, 10, NULL,
             '4011486090413010438', NULL,
             TO_DATE ('07/27/2012 11:57:31 AM', 'MM/DD/YYYY  
HH:MI:SS AM' )
            );
INSERT INTO pf_np_sch_fp
            (pf_np_sch_fpid, CANCEL, sourceid, mapname, username, modifiedon,
             createdby, createdon, wkid, app_level, app_desc, app_slevel, dt,
             DO, ran, govt, emp_cont, employee_id, batch_no, transaction_no,
             salary_date
            )
     VALUES (12345680182, NULL, NULL, NULL, NULL, NULL,
             NULL, NULL, NULL, NULL, NULL, NULL, '4011486',
             'SGV127937A', '110031687905', 10, 10, NULL, NULL, NULL,
             TO_DATE ('07/27/2012 11:57:31 AM', 'MM/DD/YYYY HH:MI:SS AM')
            );
INSERT INTO pf_np_sch_fp
            (pf_np_sch_fpid, CANCEL, sourceid, mapname, username, modifiedon,
             createdby, createdon, wkid, app_level, app_desc, app_slevel, dt,
             DO, ran, govt, emp_cont, employee_id, batch_no, transaction_no,
             salary_date
            )
     VALUES (12345680183, NULL, NULL, NULL, NULL, NULL,
             NULL, NULL, NULL, NULL, NULL, NULL, '4011486',
             'SGV127936G', '110051940333', 10, 10, NULL, NULL, NULL,
             TO_DATE ('07/27/2012 11:57:31 AM', 'MM/DD/YYYY HH:MI:SS AM')
            );
INSERT INTO pf_np_sch_fp
            (pf_np_sch_fpid, CANCEL, sourceid, mapname, username, modifiedon,
             createdby, createdon, wkid, app_level, app_desc, app_slevel, dt,
             DO, ran, govt, emp_cont, employee_id,
             batch_no, transaction_no,
             salary_date
            )
     VALUES (12345680184, NULL, NULL, NULL, NULL, NULL,
             NULL, NULL, NULL, NULL, NULL, NULL, '4011486',
             'SGV127939C', '110071830252', 10, 10, NULL,
             '4011486090413010438', NULL,
             TO_DATE ('07/27/2012 11:57:31 AM', 'MM/DD/YYYY  
HH:MI:SS AM' )
            );
            commit;
            
            
CREATE OR REPLACE PROCEDURE pro_pf_np_fp
AS
   v_mas_seq      NUMBER (9);
   v_gov_total    NUMBER (20, 2);
   v_emp_total    NUMBER (20, 2);
   v_text_exp     CLOB;
   v_pageaccess   VARCHAR2 (15);
   v_dt           NUMBER (7)     := 4011486;
   v_batchno      NUMBER (20)
              := v_dt || '0' || TO_NUMBER (TO_CHAR (SYSDATE, 'ddmmyyhhmmss'));
   v_do_count     NUMBER (9);
   v_sub_count    NUMBER (9);
   v_seq_loop     NUMBER (9)     := 2;
   v_total        NUMBER (20, 2);
BEGIN
   SELECT COUNT (*) AS DO, SUM (cnt) AS subc,
          COUNT (*) + SUM (cnt) + 2 AS seq_1,
          LTRIM (TO_CHAR (ROUND (SUM (govtc), 2), '9999990.99')),
          LTRIM (TO_CHAR (ROUND (SUM (empc), 2), '9999990.99')),
          LTRIM (TO_CHAR (ROUND (SUM (govtc) + SUM (empc), 2), '9999990.99'))
     INTO v_do_count, v_sub_count,
          v_mas_seq,
          v_gov_total,
          v_emp_total,
          v_total
     FROM (SELECT   dt, DO, COUNT (*) AS cnt, SUM (b.govt) AS govtc,
                    SUM (b.emp_cont) AS empc
               FROM pf_np_sch_fp b
              WHERE dt = v_dt
           GROUP BY dt, DO) a;

   v_text_exp :=
         '1^FH^P^'
      || v_dt
      || '^1^^^^^^^'
      || CHR (10)
      || '2^BH^1^R^'
      || v_dt
      || '^'
      || TO_CHAR (SYSDATE, 'ddmmyyyy')
      || '^'
      || v_batchno
      || '^^'
      || v_do_count
      || '^'
      || v_sub_count
      || '^'
      || LTRIM (TO_CHAR (v_gov_total, '9999990.99'))
      || '^'
      || LTRIM (TO_CHAR (v_emp_total, '9999990.99'))
      || '^^'
      || LTRIM (TO_CHAR (v_total, '9999990.99'))
      || '^';

   FOR i IN (SELECT ROWNUM AS sno, a.*
               FROM (SELECT   dt, DO, COUNT (*) AS cnt,
                              LTRIM (TO_CHAR (ROUND (SUM (b.govt), 2),
                                              '9999990.99'
                                             )
                                    ) AS govtc,
                              LTRIM
                                   (TO_CHAR (ROUND (SUM (b.emp_cont), 2),
                                             '9999990.99'
                                            )
                                   ) AS empc
                         FROM pf_np_sch_fp b
                        WHERE dt = 4011486
                     GROUP BY dt, DO) a)
   LOOP
      v_seq_loop := v_seq_loop + 1;
      v_text_exp :=
            v_text_exp
         || CHR (10)
         || v_seq_loop
         || '^DH^'
         || '1^'
         || i.sno
         || '^'
         || i.DO
         || '^'
         || i.cnt
         || '^'
         || i.govtc
         || '^'
         || i.empc
         || '^^';

      FOR j IN
         (SELECT ROWNUM AS lineno, s.*
            FROM (SELECT ran,
                         LTRIM (TO_CHAR (ROUND (govt, 2), '9999990.99')) govt,
                         LTRIM (TO_CHAR (ROUND (emp_cont, 2), '9999990.99')
                               ) emp_cont,
                         LTRIM
                            (TO_CHAR (ROUND (govt + emp_cont, 2),
                                      '9999990.99')
                            ) AS total_contribution,
                         'C' AS typ, TO_CHAR (salary_date, 'mm') AS smonth,
                         TO_CHAR (salary_date, 'yyyy') AS syear
                    FROM pf_np_sch_fp
                   WHERE DO = i.DO) s)
      LOOP
         v_seq_loop := v_seq_loop + 1;
         v_text_exp :=
               v_text_exp
            || CHR (10)
            || v_seq_loop
            || '^SD^1^'
            || i.sno
            || '^'
            || j.lineno
            || '^'
            || j.ran
            || '^'
            || j.govt
            || '^'
            || j.emp_cont
            || '^^'
            || j.total_contribution
            || '^'
            || j.typ
            || '^'
            || j.smonth
            || '^'
            || j.syear
            || '^Remarks^';
      END LOOP;
   END LOOP;

   INSERT INTO test_clob
               (textt
               )
        VALUES (v_text_exp
               );

   COMMIT;
END;
/
Re: Append more rows in single clob column [message #582391 is a reply to message #582389] Thu, 18 April 2013 00:17 Go to previous messageGo to next message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
Im facing the issue only when the No. of rows in 'pro_pf_np_fp' table is greater than 5000. The error is ORA-06502: PL/SQL: numeric or value error.
Re: Append more rows in single clob column [message #582409 is a reply to message #582391] Thu, 18 April 2013 04:09 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Because you are using clob datatype so you have to use dbms_lob.append method not varchar2 style || operator.Just try like this


CREATE TABLE CLOB_TEST(CLOB_COL CLOB,COL_ID NUMBER);


DECLARE
  v_text_exp CLOB;
  v_text_exp_part1 VARCHAR2(32767);
  v_text_exp_part2 varchar2(32767);
  len INTEGER;
BEGIN
  v_text_exp:='1';
  FOR i IN 1 .. 10
  loop
    v_text_exp_part1:='2';--append i's data
    dbms_lob.APPEND(v_text_exp,v_text_exp_part1);--add the appended data to main string
    v_text_exp_part1:=NULL;--making empty to hold next record
    FOR j IN 1..32767
    loop
      v_text_exp_part2:='3';--append j's data
      dbms_lob.APPEND(v_text_exp,v_text_exp_part2);--add the appended data to main string
      v_text_exp_part2:=NULL;--making empty to hold next record
    END loop;
  END loop;
  len:=LENGTH(v_text_exp);
  dbms_output.put_line(len);
  INSERT INTO clob_test VALUES
    (v_text_exp,1
    );
END;


SELECT clob_col,
  LENGTH(clob_col)tot_len,
  LENGTH(clob_col)-LENGTH(REPLACE(CLOB_COL,'2',''))lenof2,
  LENGTH(clob_col)-LENGTH(REPLACE(CLOB_COL,'3',''))lenof3,
  LENGTH(clob_col)-LENGTH(REPLACE(CLOB_COL,'1',''))lenof1
FROM clob_test;
Re: Append more rows in single clob column [message #582419 is a reply to message #582409] Thu, 18 April 2013 04:56 Go to previous message
visitsenthil
Messages: 15
Registered: November 2012
Location: Bangalore,India
Junior Member
Ya its working,Thanks sss111ind
Previous Topic: Problem with Sys_refcursor
Next Topic: Why are FK not deferrable by default
Goto Forum:
  


Current Time: Sat Oct 25 16:37:44 CDT 2014

Total time taken to generate the page: 0.08107 seconds