I'm trying to get the search_condition from check_ddls with read-only access in Oracle, and need to group these rows with a ; delimiter.
I've tried using LIST_AGG, but it seems that Oracle doesn’t support aggregation for LONG data types, and there is no easy way to convert a LONG to a string. However, if I could, somehow, group these bytes, and then convert them to a string using Java, that would be a great solution. But I haven’t found a suitable approach yet.
I was trying to convert LONG many times.
You need to use PL/SQL to convert short (< 4000 bytes) LONGs in the dictionary to varchar2. PL/SQL recognizes the assignment of a LONG to a PL/SQL varchar2 variable and does the conversion for you. Unfortunately you can't pass a LONG as a parameter so you have to have your procedure select the original row itself (redundantly). So you can do something like this:
CREATE OR REPLACE FUNCTION f_all_constraints_search_condition(in_owner IN varchar2, in_constraint_name IN varchar2)
RETURN varchar2
AS
var_search_condition varchar2(4000);
var_search_condition_long long;
BEGIN
SELECT search_condition
INTO var_search_condition_long
FROM all_constraints
WHERE owner = in_owner
AND constraint_name = in_constraint_name;
var_search_condition := var_search_condition_long; -- Oracle will convert the LONG here
RETURN var_search_condition;
END;
/
Now use it however you need. I'm not sure it's a good idea to listagg something like this or how meaningful it would be, but it works:
SELECT owner,table_name,LISTAGG(f_all_constraints_search_condition(owner,constraint_name),';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY constraint_name) constraints
FROM all_constraints c
WHERE search_condition IS NOT NULL -- just for testing
GROUP BY owner,table_name
If you ever need to work with a long that is > 4000, you would need to convert them to a CLOB (which requires rather complex use of dbms_sql), but of course then you wouldn't be aggregating them (but you can search them). For certain dictionary fields like this one it is highly unlikely that the value will be >4KB, so the above or something like it is a workable solution. You can also adapt this into a pipeline function with a view around it that replaces the original view entirely.
Update: if you don't have create procedure privs, in 19c you can use inline functions using the WITH clause (normally used for CTEs). Submit the following as one complete statement, which should work as long as your client supports it (older clients may not like the ; character, as that normally indicates separation between individual statements):
WITH FUNCTION f_all_constraints_search_condition(in_owner IN varchar2, in_constraint_name IN varchar2)
RETURN varchar2
AS
var_search_condition varchar2(4000);
var_search_condition_long long;
BEGIN
SELECT search_condition
INTO var_search_condition_long
FROM all_constraints
WHERE owner = in_owner
AND constraint_name = in_constraint_name;
var_search_condition := var_search_condition_long; -- Oracle will convert the LONG here
RETURN var_search_condition;
END;
SELECT owner,table_name,LISTAGG(f_all_constraints_search_condition(owner,constraint_name),';' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY constraint_name) constraints
FROM all_constraints c
WHERE search_condition IS NOT NULL -- just for testing
GROUP BY owner,table_name