asked on

How do I access the data returned from Oracle's 'LISTAGG' function?

I have a simple query that is returning a list of record id's. This is my first go using the LISTAGG function, so I'm unsure how to reference the data directly. My question is two fold and should yield two answers. First, how do I save the comma delimited list as a PHP variable? Second, can I add the query into another query as the 'where' to delete all records with the id's returned from the LISTAGG query? If so (to either or), how? Thanks in advance. :)

My query using LISTAGG:

$sql = sprintf("
	SELECT LISTAGG(LINK_ID, ',')
		WITHIN GROUP (ORDER BY LINK_ID)
	FROM LINK_TABLE
	WHERE PAGE_ID = '%s'
	",
	$PAGE_ID
);
$links = $readDB->runQuery($sql);
header('Content-type: application/json');
echo json_encode($links);

Open in new window

The query above yields the following:
[{"LISTAGG(LINK_ID,',')WITHINGROUP(ORDERBYLINK_ID)":"2044,2048,2052,2054,2056,2063,2065,2068,2070,2079,2081,2082"
}]

This is the query that I need the data for, and would like to integrate the aforementioned query into:

$sql = sprintf("
    DELETE FROM 
	OVERRIDES_TABLE
	WHERE LINK_ID IN ($LINK_ID)
);
$overrides = $writeDB->runQuery($sql);

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sean Stuber

another option, if you need to do something with the list other than issue the delete, build a collection.

select collect(link_id) from link_table where page_id = '%s'

i don't know how you store a collection in a PHP variable though, but assuming you
create and populate a variable called "your_collection"
you can use the collection variable in your sql

delete from overrides_table where link_id in (select * from table(your_collection))

Sean Stuber

and one last option, you can use a function like str2tbl (search on EE or asktom.oracle.com)
to split the LISTAGG results into a collection, this will assume you have authority to create the function and associated collection type

delete from overrides_table where link_id in (select * from table(str2tbl($LINK_ID)))

brianmfalls

ASKER

Thanks! I should have figured that out on my own... Perhaps I should get some coffee??