DBMS_METADATA [message #190872] |
Fri, 01 September 2006 13:33 |
rurajase
Messages: 1 Registered: September 2006
|
Junior Member |
|
|
Hello,
I have created a simple table EMP. Its structure looks like:
EMP_ID, FIRST_NAME, LAST_NAME, HOURS_WORKED.
I have a stored proc that looks like
CREATE OR REPLACE PROCEDURE sp_emp (emp_id IN NUMBER)
IS
tmpVar VARCHAR2(10);
empidVar NUMBER;
BEGIN
tmpVar := 0;
empidVar := emp_id;
SELECT first_name
INTO tmpVar
FROM emp
WHERE emp_id=empidVar;
END sp_emp;
Is there any way I could extract the metadata out of the stored proc using the DBMS_METADATA pkg. I used the DBMS_METADATA.GET_XML sub program like this:
create or replace procedure get_metadata IS
h NUMBER;
th NUMBER;
doc XMLTYPE;
BEGIN
doc := DBMS_METADATA.GET_XML('PROCEDURE','SP_EMP','SCHEMA');
INSERT into my_metadata(md) values (doc); --MY_METADATA is another table I created with one field 'md' with a CLOB datatype.
COMMIT;
END;
I am not too happy with the reesults.I have attached it for you to see...
|
|
|