Blog

The procedure below receives a SYS_RefCursor as a parameter and constructs a simple JSON in Oracle 11g:

    procedure get_json_fnc(P_RS_i Sys_Refcursor) is 
        P_RS SYS_REFCURSOR;
        L_COLS NUMBER;
        L_DESC DBMS_SQL.DESC_TAB;
        L_CURS INTEGER;
        L_VALUE VARCHAR2(4000);
        
        l_count number := 0;
        
    BEGIN
        P_RS := P_RS_i;
        L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(P_RS);
        DBMS_SQL.DESCRIBE_COLUMNS(C => L_CURS, COL_CNT => L_COLS,
            DESC_T => L_DESC);
    
        FOR i IN 1..L_COLS LOOP
            DBMS_SQL.DEFINE_COLUMN(L_CURS, i, L_VALUE, 4000);
        END LOOP;
    
        dbms_output.put_line('[');
    
        WHILE DBMS_SQL.FETCH_ROWS(L_CURS) > 0 LOOP
            l_count := l_count + 1;
            
            if(DBMS_SQL.LAST_ROW_COUNT<>1)then
              dbms_output.put(',');
            end if;            
            
            DBMS_OUTPUT.PUT_LINE('{');
            FOR i IN 1..L_COLS LOOP
                DBMS_SQL.COLUMN_VALUE(L_CURS, i, L_VALUE);
                IF(l_desc(i).col_type not in (2,8))then
                    L_VALUE := '"' || L_VALUE || '"';
                end if;
                DBMS_OUTPUT.PUT_LINE('"' || l_desc(i).col_name || '": ' || L_VALUE);
                if(i < L_COLS)then
                    DBMS_OUTPUT.PUT(',');
                end if;
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('}');
        END LOOP;
        
        dbms_output.put_line(']');
    
        DBMS_SQL.CLOSE_CURSOR(L_CURS);
    END;