1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Prepend data for CLOB

Discussion in 'SQL PL/SQL' started by arunshankar.c, Jan 18, 2012.

  1. arunshankar.c

    arunshankar.c Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hello,

    Could you please help me on the below case. To append a string, below code can be used, for eg : v_str in the below case i.e the current entry for v_str is appended for the previously stored string value.

    Code (Text):

    v_str := v_str ||'Total Records = 0 Processed = 0' || CHR(13) || CHR(10);
    SELECT data_value INTO g_clob FROM ni_sim_order_artifact WHERE artifact_type = 60002 AND sim_order_fk = p_sim_order_pk FOR UPDATE;
    DBMS_LOB.WRITEAPPEND(g_clob, length(v_str), v_str);
    INSERT INTO NI_SIM_ORDER_HST values(ni_sim_order_hst_seq.nextval, p_sim_order_pk, GMTSYSDATE, p_user_id, 60003,'Moved to state Rejected, refer validation logs.');
    UPDATE NI_SIM_ORDER SET STATUS=60003 WHERE ORDER_NUMBER=p_batch_number;
    COMMIT;
     
    Could you please suggest a way in which I can prepend v_str value every time instead of appending.

    For example :
    (just a eg)
    Lets assume 1 was inserted, now in above statements if i want to insert 2 to ni_sim_order_artifact table then in current setup 1 is inserted and then 2. Please let me know a way to insert 1 initially and then 2 needs to be prepended to 1.


    one way i can think of is :

    Code (Text):


    > set long 2000000
    > select * from ni_sim_order_artifact;

    SIM_ORDER_ARTIFACT_PK ARTIFACT_TYPE SIM_ORDER_FK DATA_VALUE
    --------------------- ------------- ------------ --------------------------------------------------------------------------------
                        1             2           20 Original Value

    > update ni_sim_order_artifact set data_value = 'PreAppend '||data_value where sim_order_artifact_pk = 1;

    1 row updated.

    > select * from ni_sim_order_artifact;

    SIM_ORDER_ARTIFACT_PK ARTIFACT_TYPE SIM_ORDER_FK DATA_VALUE
    --------------------- ------------- ------------ --------------------------------------------------------------------------------
                        1             2           20 PreAppend Original Value
     
    Thank you
    Arun
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is no shortcut I guess..

    Code (SQL):



    SQL> SELECT * FROM X_TEST;

    DST_CLOB
    --------------------------------------------------------------------------------
    Original VALUE

    SQL>
    SQL> DECLARE
    DESTIN_CLOB  CLOB;
    SOURCE_CLOB  CLOB :=TO_CLOB('PreAppend ');
    BEGIN

     SELECT DST_CLOB
     INTO DESTIN_CLOB
     FROM X_TEST FOR UPDATE;

      DBMS_LOB.append( SOURCE_CLOB ,DESTIN_CLOB);

      UPDATE X_TEST
      SET DST_CLOB = SOURCE_CLOB;

    END;  2    3    4    5    6    7    8    9   10   11   12   13   14   15
     16  /

    PL/SQL PROCEDURE successfully completed.

    SQL> SELECT * FROM X_TEST;

    DST_CLOB
    --------------------------------------------------------------------------------
    PreAppend Original VALUE

    SQL>