{"id":12781,"date":"2024-01-05T08:27:53","date_gmt":"2024-01-05T07:27:53","guid":{"rendered":"https:\/\/roglacup.com\/klaus62\/?p=12781"},"modified":"2024-01-05T14:31:24","modified_gmt":"2024-01-05T13:31:24","slug":"data-dictionary-storage","status":"publish","type":"post","link":"https:\/\/roglacup.com\/klaus62\/2024\/01\/05\/data-dictionary-storage\/","title":{"rendered":"Data Dictionary Storage"},"content":{"rendered":"\n

Views: User_Objects, User_source, User_Errors, User_Object_size, User_Dependicies<\/strong><\/p>\n\n\n\n

–Query the User_Objects view for all procedures and functions<\/strong>
SELECT object_name, status
FROM user_objects
WHERE object_type IN(‘PROCEDURE’, ‘FUNCTION’);<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

—Determine which objects have source code<\/strong>
SELECT DISTINCT name, type
FROM user_source;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

–Retrieve the code for the raise_salary procedure<\/strong>
SELECT text
FROM user_source
WHERE name = ‘RAISE_SALARY’
ORDER BY line;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

–Execute a query retrieving all errors for the Error_Salary procedure<\/strong>
SELECT line, text, attribute, message_number
FROM user_errors
WHERE name = ‘ERROR_SALARY’
ORDER BY line;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

–Query the user_object_size to ascertain storage information for our objects.<\/strong>
SELECT *
FROM user_object_size
WHERE type IN(‘PROCEDURE’, ‘FUNCTION’);<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

–Query the USER_DEPENDENCIES to find the dependency information on the
–SALARY_VALID procedure<\/strong>
<\/p>\n\n\n\n

SELECT referenced_name, referenced_type
FROM user_dependencies
WHERE name = ‘SALARY_VALID’;<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Dependency Tracking Utility SETUP – utldtree.sql<\/strong><\/p>\n\n\n\n

DEPTREE_FILL()<\/strong> – fills IDEPTREE table<\/p>\n\n\n\n

<\/p>\n\n\n\n

Package<\/strong><\/p>\n\n\n\n

Oracle Packages are database objects that are unique to the Oracle DBMS. They are containers that group logically-related objects into a single definition. Packages have two parts – a specification and a body. The:<\/p>\n\n\n\n