Home » SQL & PL/SQL » SQL & PL/SQL » CLOB Column Append (Oracle 11 G R2, Windows Server 2008 R2 )
CLOB Column Append [message #577231] Wed, 13 February 2013 10:56 Go to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Gurus,

I have a requirement when I need to append and show the contents of a CLOB(rich text) column into the Open office report.

The use case is as below.
User(s) can enter the details into a CLOB column.
This data entered by each user need to be concatenated onto another CLOB column which will hold all the history of changes. Mainly i wanted to perform a concatenation of these two columns.

|   
|   User Entered                |   Stored                                     |
|   Data Stored in Clob Column 1|   Data Stored in Clob Column 2               |
|   v_clob_1                    |   v_clob_target                              | 
-------------------------------------------------------------------------------
|"Text 1"  and a image          |"Text 1"  and a image                         |
-------------------------------------------------------------------------------




Please let me know if there is a method to achieve this case.


I had tried the following method to achieve this. But no success.

v_merge clob;
v_clob_target clob;
v_clob_1 clob;

1)
DBMS_LOB.APPEND(v_merge, v_clob_target);
DBMS_LOB.APPEND(v_merge, v_clob_1);

2)
DBMS_LOB.APPEND(v_clob_target, v_clob_1);

3)
v_merge:= v_clob_target || ''|| v_clob_1;
v_clob_target := v_merge;


Thanks & Regards
Javed A. Khan
Re: CLOB Column Append [message #577232 is a reply to message #577231] Wed, 13 February 2013 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v_clob_target := v_clob_target || ''|| v_clob_1;

Regards
Michel
Re: CLOB Column Append [message #577238 is a reply to message #577232] Wed, 13 February 2013 11:48 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

I will post if this helps...
Re: CLOB Column Append [message #577239 is a reply to message #577238] Wed, 13 February 2013 12:01 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Yes it works...but it brings the performance down.. any way to fasten this up.
Re: CLOB Column Append [message #577265 is a reply to message #577239] Wed, 13 February 2013 17:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We have no information of what you mean by "it brings the performance down".
Compare to what?
Post evidences.

Regards
Michel
Re: CLOB Column Append [message #577453 is a reply to message #577265] Fri, 15 February 2013 15:54 Go to previous messageGo to next message
javed.khan
Messages: 308
Registered: November 2006
Location: Banglore
Senior Member

Running the simple query with two clobs took me more then 4 minutes.

SELECT 
           
           complaint_code_data AS complaint_code_data,
           components_data||''||inv_data,
           FROM ms_com_complaint_details_v


the columns complaint_code_data,components_data and inv_data i am preparing in a package functions as clob.
for example
FUNCTION component_details (
      p_complaint_id   VARCHAR2,
      p_pid            NUMBER DEFAULT NULL
   )
      RETURN CLOB
   IS
      html_data    CLOB;
      data_check   BOOLEAN DEFAULT FALSE;
   BEGIN
      FOR i IN
         (SELECT component_id, component_description,
                 ms_apps_utilities.get_display_value
                    (100000,
                     'MS COM Component ID Action LOV',
                     component_id_action
                    ) AS component_id_action,
                 ms_apps_utilities.get_display_value
                                  (100000,
                                   'MS COM Failure Mode LOV',
                                   failure_mode
                                  ) AS failure_mode,
                 failure_code, component_summary
            FROM ms_com_components_com
           WHERE component_complaint_number = p_complaint_id
             AND component_id IS NOT NULL)
      LOOP
         data_check := TRUE;
         html_data :=
               html_data
            || '<tr>
                            
                        </tr>
            
                        <tr>
                            <td colspan="3" height="10" ></td>
                            <td></td>
                            <td colspan="3"></td>
                            <td></td>
                            <td colspan="3"></td>
                        </tr> 
                        <tr>
                            <td ></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;font-weight: bold;">Component ID</td>
                            <td></td>
                            <td class=""></td>
                            <td></td>
                            <td colspan="7" style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;font-weight: bold;">Component Description</td>
                            <td></td
                        </tr>';
         html_data :=
               html_data
            || '<tr>
                            <td ></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;"
 >'
            || i.component_id
            || '</td>
                            <td></td>
                            <td class=""></td>
                            <td></td>
                            <td colspan"7" style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;"
 >'
            || i.component_description
            || '</td>
                        </tr>';
         html_data :=
               html_data
            || '<tr>
                            <td class="" colspan="11"></td>
                        </tr>
            
                        <tr>
                            <td colspan="3" height="10" ></td>
                            <td></td>
                            <td colspan="3"></td>
                            <td></td>
                            <td colspan="3"></td>
                        </tr> 
                        <tr>
                            <td ></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;font-weight: bold;">Component ID Action</td>
                            <td></td>
                            <td class=""></td>
                            <td></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;font-weight: bold;">Failure Mode</td>
                            <td></td
                            <td class=""></td>
                            <td></td>
                            <td></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;font-weight: bold;">Failure Code</td>
                            <td></td>
                            <td class=""></td>
                            <td></td>
                            </tr>';
         html_data :=
               html_data
            || '<tr>
                            <td ></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;"
 >'
            || i.component_id_action
            || '</td>
                            <td></td>
                            <td class=""></td>
                            <td></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;"
 >'
            || i.failure_mode
            || '</td>
                            <td></td>
                            <td class=""></td>
                            <td></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;"
 >'
            || i.failure_code
            || '</td>
             
                        </tr>';
         html_data :=
               html_data
            || '<tr>
                            <td class="" colspan="11"></td>
                        </tr>
            
                        <tr>
                            <td colspan="3" height="10" ></td>
                            <td></td>
                            <td colspan="3"></td>
                            <td></td>
                            <td colspan="3"></td>
                        </tr> 
                        
                        <tr>
                            <td ></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;font-weight: bold;">Component Summary</td>
                            <td></td>
                            <td class=""></td>
                            
                            </tr>';
         html_data :=
               html_data
            || '<tr>
                            <td ></td>
                            <td style="font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif; color: #4E6585;margin-left: 5px;font-size: 11px;"
 >'
            || i.component_summary
            || '</td></td>
             <tr> <td style="background-color:#C4D7E9;border-bottom-color:solid;" height="1" colspan="11"></td></tr>
                        </tr>';
      END LOOP;

      IF data_check = TRUE
      THEN
         RETURN html_data;
      ELSE
         RETURN NULL;
      END IF;

      RETURN html_data;
   END;


without concatenation it runs fine.. when i put concat it puts me to sleep.

Javed
Re: CLOB Column Append [message #577456 is a reply to message #577453] Sat, 16 February 2013 00:54 Go to previous message
Michel Cadot
Messages: 58907
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without which concatenation? There are many in your code.
Where do come components_data and inv_data?
What is ms_com_complaint_details_v?

Regards
Michel


Previous Topic: how to install exceldocumenttype
Next Topic: Using Minus operator to get the changed data info between two tables
Goto Forum:
  


Current Time: Wed Aug 27 12:06:54 CDT 2014

Total time taken to generate the page: 0.06084 seconds