I found this to list all packages:
SELECT cmobjnames.cmid,
cmstoreids.storeid,
cmobjects.pcmid,
cmobjnames.name,
cmobjnames.mapdlocaleid,
cmobjnames.localeid,
cmlocales.locale,
cmclasses.name as type
FROM cmobjects, cmobjnames, cmclasses, cmstoreids, cmlocales
WHERE
cmobjects.cmid = cmobjnames.cmid
AND cmobjects.classid = cmclasses.classid
AND cmobjects.cmid = cmstoreids.cmid
AND cmclasses.name ='package'
AND cmobjnames.localeid = cmlocales.localeid and cmlocales.locale in ('en')
order by name
what would i alter to get the path to the packages?
All of it.
You'll need a recursive common table expression.
This site doesn't like code. Good luck.
I created a function to get the path info and called it in the query showed
CREATE OR REPLACE FUNCTION getpath(p_pcmid IN NUMBER) RETURN NVARCHAR2 IS
l_parentname NVARCHAR2(100);
l_pcmid NUMBER;
BEGIN
IF p_pcmid = 0 THEN
RETURN 'root';
ELSE
SELECT n.name object_name
,o.pcmid
INTO l_parentname
,l_pcmid
FROM rnr_tbls.cmobjnames n
,rnr_tbls.cmobjects o
WHERE o.cmid = n.cmid
AND o.cmid = p_pcmid
AND isdefault = 1;
END IF;
RETURN getpath(l_pcmid) || '\' || l_parentname;
EXCEPTION
WHEN OTHERS THEN
RETURN SQLERRM;
END getpath;
Since I'm currently working with this...
https://pastebin.com/zPn7rmje