List of all Packages and the path to the package

[CraigFSindorf]

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?

[dougp]

All of it.

You'll need a recursive common table expression.

This site doesn't like code.  Good luck.

[Spyhop]

I created a function to get the path info and called it in the query showed

Code: [Select]

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;


[dougp]

Since I'm currently working with this...

https://pastebin.com/zPn7rmje