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.