Tuesday, 13 October 2009

Using Oracle Embedded PL/SQL Gateway (DBMS_EPG)

After struggling (yet again) to remember how to look at the current Embedded PL/SQL Gateway configuration and create a new DAD, I decided that I would finally write it up into a few foolproof scripts:

To display the current EPG configuration:

Turn on DBMS_OUTPUT and run the following script...
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;




To add a new DAD:

BEGIN

dbms_epg.create_dad
( dad_name => 'dad name'
, path => 'URL Path'
);

dbms_epg.set_dad_attribute
( dad_name => 'dad name'
, attr_name => 'database-username'
, attr_value => 'database username'
);

dbms_epg.authorize_dad
( dad_name => 'dad name'
, user => 'database username'
);

END;
/

No comments:

Post a Comment