表-> ORA-00942

Question

我有一个json字符串参数。我想要创建一个函数，它返回一个带有数据的表。

我创建了一个类型：

CREATE OR REPLACE TYPE AttachmentResponseType AS OBJECT (
 savePath              VARCHAR (255) ,
 originalFileName      VARCHAR (255) ,
 fileSize              number(20) );

我创建了一个列表

CREATE OR REPLACE TYPE JSON_DOC_JUKEBOX_TBL IS TABLE OF AttachmentResponseType;

和一个函数

CREATE OR REPLACE FUNCTION JSONSTRING_TO_JB_TYPE (p_json in varchar2) return JSON_DOC_JUKEBOX_TBL as
resTBL JSON_DOC_JUKEBOX_TBL;
JSON_VAR varchar2(3000);

begin
JSON_VAR := p_json;
with x as (SELECT t.savePath, t.originalFileName, t.fileSize FROM JSON_TABLE(
                JSON_VAR,
                '$' 
                COLUMNS(
                    NESTED '$.AttachmentResponseType[*]'COLUMNS (
                    savePath varchar2(255),
                    originalFileName varchar2(255),
                    fileSize number(20)
                    )
                )
            ) as t) select AttachmentResponseType(x.savePath, x.originalFileName, x.fileSize) bulk collect INTO resTBL from x;
            commit;
    return resTBL;
end;

我试着测试它，我有一个具有相同列的tst表。

    declare
    test JSON_DOC_JUKEBOX_TBL;
begin
    test := JSONSTRING_TO_JB_TYPE('{"AttachmentResponseType" : [{
             "savePath" : "dsadsad/dsadas.xaxaxa.pfg",
             "originalFileName" : "xaxaxa.pfg",
             "fileSize" : "12"},
             {
             "savePath" : "dsadsad/1111g",
             "originalFileName" : "1.pfg",
             "fileSize" : "1112"}]}');
             
    INSERT INTO tst
    (savePath, originalFileName, fileSize) values
    (SELECT savePath, originalFileName, fileSize
    FROM test);
    commit;
end;

测试突出显示为“未定义表”，当我运行时，例外情况是:ORA-00942。

我做错了什么吗？我应该以某种方式初始化“测试”表吗？

Answer 1

与其使用多个TYPE和一个FUNCTION来插入记录，不如直接使用INSERT INTO...SELECT...

见下面的例子：

CREATE TABLE tst
(
    savePath            VARCHAR (255),
    originalFileName    VARCHAR (255),
    fileSize            NUMBER (20)
);

INSERT INTO tst (savepath, originalfilename, filesize)
    SELECT savepath, originalfilename, filesize
      FROM JSON_TABLE (
               '{"AttachmentResponseType" : [{
             "savePath" : "dsadsad/dsadas.xaxaxa.pfg",
             "originalFileName" : "xaxaxa.pfg",
             "fileSize" : "12"},
             {
             "savePath" : "dsadsad/1111g",
             "originalFileName" : "1.pfg",
             "fileSize" : "1112"}]}',
               '$'
               COLUMNS (
                   NESTED '$.AttachmentResponseType[*]'
                       COLUMNS (savePath VARCHAR2 (255),
                                originalFileName VARCHAR2 (255),
                                fileSize NUMBER (20))));