How to use one CLOB output as column in another CLOB in Stored Procedure?

Question

I am just writing small part of stored procedure, Consider following as StoredProc

mainQuery CLOB := 'select name, no from main_table od ';

subQuery CLOB := 'select LISTAGG(address, '||''','''||') within group (ORDER BY address) from sub_table sb where sb.id = od.id';

Here, I want output of subQuery clob in mainQuery clob as a column with sub as alias name. I very newbie to plsql.

Start by writing the overall query you want to get, then turn it to clob as is and replace dynamic parts with actual object names. Or use static SQL if object names and column names are static — astentx, Aug 18 '21 at 16:29
Can you please write the query for above, it would be more helpful. Thank you so much — Xio Xim Jin, Aug 18 '21 at 16:47
I cannot write query for you because I do not know what you want to achieve. — astentx, Aug 18 '21 at 16:48
I want results of subquery clob as column in mainquery clob. — Xio Xim Jin, Aug 18 '21 at 16:59

score 0 · Answer 1 · answered Aug 18 '21 at 18:17

I am not sure if I understand what you are trying to achieve here, but you could try

    DECLARE
        mainQuery CLOB;
    BEGIN
        select 
        LISTAGG(address, '||''','''||') within group (ORDER BY address) 
        INTO mainQuery
        from sub_table sb where sb.id = od.id;
        dbms_output.put_line(mainQuery);
    END;

How to use one CLOB output as column in another CLOB in Stored Procedure?

1 Answers1