Finding Embedded PL/SQL  DAD attributes      Nov 27, 2009

i have found the following useful script to extract DAD name and DAD information when using

the embedded Plsql gateway

the credit goes to  http://stevensutcliffe.blogspot.com/2009/10/sing-oracle-embedded-plsql-gateway.html

DECLARE
a_attr_names DBMS_EPG.VARCHAR2_TABLE;
a_attr_values DBMS_EPG.VARCHAR2_TABLE;
a_dad_names DBMS_EPG.VARCHAR2_TABLE;
a_mappings DBMS_EPG.VARCHAR2_TABLE;
n_counter NUMBER;
n_counter2 NUMBER;
BEGIN
--
/* Global Attributes */
DBMS_OUTPUT.PUT_LINE('Global Attributes:');
DBMS_OUTPUT.PUT_LINE('==================');
--
DBMS_EPG.GET_ALL_GLOBAL_ATTRIBUTES
( a_attr_names
, a_attr_values
);
--
IF a_attr_names.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No attributes found.');
ELSE
FOR n_counter IN 1..a_attr_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(a_attr_names(n_counter),40,' ') || a_attr_values(n_counter));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(' ');
--
DBMS_OUTPUT.PUT_LINE('DAD Details:');
DBMS_OUTPUT.PUT_LINE('============');
--
DBMS_EPG.GET_DAD_LIST (a_dad_names);
FOR n_counter IN 1..a_dad_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Dad ' || TO_CHAR(n_counter,'FM00') || ': ' || a_dad_names(n_counter));
DBMS_OUTPUT.PUT_LINE(RPAD('-',LENGTH('Dad ' || TO_CHAR(n_counter,'FM00') || ': ' || a_dad_names(n_counter)),'-'));
--
DBMS_EPG.GET_ALL_DAD_MAPPINGS
( a_dad_names(n_counter)
, a_mappings
);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Mappings('||a_mappings.COUNT||')');
IF a_mappings.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(' No mappings found.');
ELSE
FOR n_counter2 IN 1..a_mappings.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(' ' || a_mappings(n_counter2));
END LOOP;
END IF;
--
DBMS_EPG.GET_ALL_DAD_ATTRIBUTES
( a_dad_names(n_counter)
, a_attr_names
, a_attr_values
);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Attributes:');
IF a_attr_names.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(' No attributes found.');
ELSE
FOR n_counter2 IN 1..a_attr_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(' ' || RPAD(a_attr_names(n_counter2),35,' ') || a_attr_values(n_counter2));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;