PL/SQL Convert Rows to Delimited String

SELECT SUBSTR (SYS_CONNECT_BY_PATH (name , ‘,’), 2) OBJECT_NAME INTO l_tenant_objects
FROM (
SELECT OBJ.name , ROW_NUMBER () OVER (ORDER BY OBJ.name ) rn,
COUNT (*) OVER () cnt
FROM OBJECTS OBJ
INNER JOIN (SELECT MAX(OBJ.OBJECT_ID) DIST_OBJ_ID,OBJ.NAME FROM OBJECTS OBJ
INNER JOIN AUTHORIZATION_OBJECTS AO
ON OBJ.AUTH_OBJECT_ID = AO.AUTH_OBJECT_ID
INNER JOIN AUTH_OBJECT_TYPES AOT
ON AOT.AUTH_OBJ_TYPE_ID = AO.AUTH_OBJ_TYPE_ID
AND AOT.CODE = ‘AUTH_OBJ_TYPE_TENANT’
GROUP BY NAME
) DISTINCTED_OBJECTS
ON OBJ.OBJECT_ID = DISTINCTED_OBJECTS.DIST_OBJ_ID
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s