'I have a plsql procedure which gives a table type output. I need to get all the rows in json format using a select statement but it is returning null

This is what I tried. I have a plsql procedure that gives a table type output. So I created a plsql function to call the procedure and convert the output to a clob (tried VARCHAR2 as well) which contains the json formatted table rows. I tested the function using a plsql script and it is returning the expected result but when I'm trying to call the function using a select statement, I'm getting a null value every time. Please help.

Procedure signature is like procedure(p1 IN, p2 IN, ..., x OUT table) Function is like

func(p1 IN, p2 IN, ... ) return CLOB 
AS res CLOB;
   t table;
BEGIN
proc(p1, p2,....,t)
    FOR cursor1 IN (SELECT * FROM TABLE(CAST(t AS table)))
          LOOP
           
            res := res || '{ ' || 
                        '"column1" : ' || '"' || cursor1.column1 || '"' || ',' ||
                        '"column2" : ' || '"' || cursor1.column2 || '"' || ',' ||
                        '"column3" : ' || '"' || cursor1.column3 || '"' || ',' ||
                        '"column4" : ' || '"' || cursor1.column4 || '"' || ',' ||
                    '}' || ',';               
            
          END LOOP;
    return res;
END
sqlplsql


Solution 1:[1]

Did you try json objects of oracle?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kemal ?slim

Related Questions