sql—从动态查询打印json结构

sqyvllje  于 2021-07-26  发布在  Java
关注(0)|答案(0)|浏览(323)

我想创建一个函数,返回一个包含json内容的clob。
但首先我要打印结果(请参阅查询代码)以查看其外观。
结果必须是一个json,它将包含1条记录的信息,这个记录选择查询必须动态创建。
所以我尝试下一个查询,来解决这个问题。但我犯了个错误。
查询

declare
    v_stmt VARCHAR2(1000);
    l_sep varchar2(1) := chr(10);
    l_tab varchar2(1) := chr(9);
    up_column VARCHAR2(50);
    low_column VARCHAR2(50);
    cur_asc SYS_REFCURSOR;
    Vcdogcur SYS_REFCURSOR;
    id_ccd number;
    first boolean := true;
begin
    id_ccd := 174;
    v_stmt := 'select ';

    open cur_asc for 
        SELECT column_name as up_column
              ,lower(column_name) as low_column
        FROM USER_TAB_COLUMNS
        WHERE table_name = upper('my_table');
    loop 
        FETCH cur_asc
        INTO  up_column, low_column;
        EXIT WHEN cur_asc%NOTFOUND;
        if not first then
            v_stmt := v_stmt || ',';
        end if;
        first := false;
        v_stmt := v_stmt || l_sep || l_tab ||up_column || ' as ' || low_column;
    END LOOP;
    --v_stmt := v_stmt || l_sep || 'from my_table' || l_sep ||'where id = ' || id_ccd || ';';
    v_stmt := v_stmt || l_sep || 'from my_table' || l_sep ||'where id = ' || id_ccd;
    DBMS_OUTPUT.PUT_LINE('salida: ' || v_stmt);
    CLOSE cur_asc;

    --v_stmt := v_stmt || l_sep || 'from my_table' || l_sep ||'where id = ' || id_ccd;
    --DBMS_OUTPUT.PUT_LINE('salida: ' || v_stmt);

    open Vcdogcur for v_stmt;
        APEX_JSON.initialize_clob_output;
        APEX_JSON.open_object;
        APEX_JSON.write('my_table', Vcdogcur);
        APEX_JSON.close_object;
        DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
    CLOSE Vcdogcur;
end;
/

dbms输出

salida: select 
    CREATED as created,
    CREATED_BY as created_by,
    UPDATED as updated,
    UPDATED_BY as updated_by,
    ID as id,
    TEXT as text,
    PART as part,
    CODE as code,
    DATE as date,
    PERCENTAGE as percentage
from my_table
where id = 174
{
"my_table":[
{
"CREATED":"2020-06-04T08:10:09.000000000Z"
,"CREATED_BY":"APEX_APP"
,"UPDATED":"2020-06-04T10:10:09.295632000Z"
,"UPDATED_BY":"APEX_APP"
,"ID":174
,"TEXT":"ALL"
,"PART":2770
,"CODE":2212
,"DATE":"2017-01-01T00:00:00.000000000Z"
,"PERCENTAGE":-4.98
}
]
}

错误

Informe de error -
ORA-01001: cursor no válido
ORA-06512: en línea 44
01001. 00000 -  "invalid cursor"

* Cause:
* Action:

有人能帮我解决这个问题吗?
致以最诚挚的问候

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题