Sorting contents of a text column

user11966195

Hi,

I have a output is below format. in DLL_OUTPUT column. I need to sort this output for ALTER statments and place it back in the table.

 

DDL_OUTPUT

     ALTER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" MODIFY ("STATUS" CONSTRAINT "NN_ADP 

     ALTER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" MODIFY ("STATUS_DATE" CONSTRAINT "N

     ALTER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" MODIFY ("BATCHN" CONSTRAINT "NN_ADP

     ATER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" MODIFY ("DISBURSEN" CONSTRAINT "NN_

     ALTER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" ADD CONSTRAINT "PK_ADP_ALPHA_DISB_X HPUX

     ALTER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" MODIFY ("PAY_METHOD" CONSTRAINT "NN

     ALTER TABLE "UNIACC"."ADP_ALPHA_DISB_XREF" MODIFY ("ADP_DISBURSEN" CONSTRAINT

I am using below code to do that but it is missing the last line.

OPEN constraint_list;
  LOOP
    a_data := ddl_arr();
    a_sort := ddl_arr();
    v_sorted_ddl := NULL;

    FETCH constraint_list INTO v_oracle_sid, v_obj_name, v_obj_type, v_ddl_output;

    v_list := v_ddl_output;

    LOOP
           l_idx := instr(v_list,chr(10));
           IF l_idx > 0 THEN
              v_ddl_output:=substr(v_list,1,l_idx-1);
              v_list := substr(v_list,l_idx+length(chr(10)));
               IF (INSTR(v_ddl_output,'ALTER') > 0) THEN
                  a_data.extend;
                  a_data(a_data.count) := v_ddl_output;
               END IF;                   
             ELSE
              exit;
        END IF;
    END LOOP;
    select cast(multiset(select * from table(a_data) order by 1) as ddl_arr)
    into a_sort from dual;
   
    FOR l_loop in 1..a_sort.count
    LOOP
       v_sorted_ddl := v_sorted_ddl || a_sort(l_loop) || chr(13);
    END LOOP;
 
     
    UPDATE table tddl
    SET    tddl.ddl_output = v_sorted_ddl
    WHERE  tddl.oracle_sid = v_oracle_sid
    AND    tddl.obj_name = v_obj_name
    AND    tddl.obj_type = v_obj_type
    AND    tddl.daemon_type = v_dc_daemon_type;

    EXIT WHEN constraint_list%NOTFOUND;

  END LOOP;
 
  CLOSE constraint_list;

How would I achieve that. Please note this works fine in 10g but create problem in 11G and last line from the output is missed while updating the table.