SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'PROCEDURE' OR ui.TYPE = 'PROCEDURE';

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'FUNCTION' OR ui.TYPE = 'FUNCTION';