LISTAGG inside a LISTAGG

I used some online converter to convert my view in MySQL to oracle and the result was this:

CREATE  VIEW actor_info
AS
SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT ||(c.name, ': ',
        (SELECT GROUP_CONCAT(f.title FROM dual ORDER BY f.title SEPARATOR FROM dual ', ')
                    FROM film f
                    INNER JOIN film_category fc
                      ON f.film_id = fc.film_id
                    INNER JOIN film_actor fa
                      ON f.film_id = fa.film_id
                    WHERE fc.category_id = c.category_id
                    AND fa.actor_id = a.actor_id
                 )
             )
             ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM actor a
LEFT JOIN film_actor fa
  ON a.actor_id = fa.actor_id
LEFT JOIN film_category fc
  ON fa.film_id = fc.film_id
LEFT JOIN category c
  ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;

The converter didn't convert too well so I had to modify the query and end up with something like this:

    CREATE  VIEW actor_info
AS
SELECT
a.actor_id,
a.first_name,
LISTAGG(c.name || ': ',
        (SELECT LISTAGG(f.title, ', ') within group (order by f.title)
                    FROM film f
                    INNER JOIN film_category fc
                      ON f.film_id = fc.film_id
                    INNER JOIN film_actor fa
                      ON f.film_id = fa.film_id
                    WHERE fc.category_id = c.category_id
                    AND fa.actor_id = a.actor_id
                 ) 
             ) WITHIN group (order by c.name)
AS film_info
FROM actor a
LEFT JOIN film_actor fa
  ON a.actor_id = fa.actor_id
LEFT JOIN film_category fc
  ON fa.film_id = fc.film_id
LEFT JOIN category c
  ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name, c.name;

But I'm still getting multiple errors like I can't use distinct in the first listagg or that argument should be a constant or a function of expression in GROUP BY. I'm out of ideas to try to solve this, any suggestions on what the error is or another way to do this view?

Oracle's listagg() doesn't support distinct internally, which is a bit of a pain. The other error you're getting, ORA-30497, is because you've accidentally made the second listagg() call the delimiter for the first one.

It's a little hard to tell without sample data and expected results, but it appears you're looking for something like:

SELECT a.actor_id, a.first_name, a.last_name,
  LISTAGG(c.name || ': ' || (
    SELECT LISTAGG(f.title, ', ') WITHIN GROUP (ORDER BY f.title)
    FROM film f
    INNER JOIN film_actor fa
    ON fa.film_id = f.film_id
    INNER JOIN film_category fc
    ON f.film_id = fc.film_id
    WHERE fc.category_id = c.category_id -- from main query
    AND fa.actor_id = a.actor_id -- from main query
), '; ') WITHIN GROUP (ORDER BY c.name)
AS film_info
FROM actor a
LEFT JOIN (
  SELECT DISTINCT fa.actor_id, c.category_id, c.name
  FROM film_actor fa
  LEFT JOIN film_category fc
    ON fc.film_id = fa.film_id
  LEFT JOIN category c
  ON c.category_id = fc.category_id
) c
ON c.actor_id = a.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name;

The 'distinct' part is achieved with the inline view, and the inner listagg then only needs to find all films for that category/actor.