我正在嘗試將 JSON 陣列保存為資料庫行：
INSERT INTO log (
"uuid",
"date",
"msg",
"level"
)
WITH t ( log ) AS (
SELECT
JSON_QUERY('[{"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-17", "msg":"aaaa", "level": "debug" },
{"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-17", "msg":"bbbb", "level": "debug" }]'
, '$')
FROM
dual
)
SELECT
"uuid",
"date",
"msg",
"level"
FROM
t
CROSS JOIN
JSON_TABLE ( log, '$'
COLUMNS (
NESTED PATH '$[*]'
COLUMNS (
"uuid" VARCHAR2 ( 36 ) PATH '$.uuid',
"date" DATE PATH '$.date',
"msg" VARCHAR2 ( 1024 ) PATH '$.msg',
"level" VARCHAR2 ( 5 ) PATH '$.level'
)
)
)
當我嘗試使用%type：
NESTED PATH '$[*]'
COLUMNS (
"uuid" VARCHAR2 ( 36 ) PATH '$.uuid',
"date" my_table.date%type PATH '$.date',
"msg" VARCHAR2 ( 1024 ) PATH '$.msg',
"level" VARCHAR2 ( 5 ) PATH '$.level'
)
我收到一個錯誤：
SQL Error: ORA-40484: invalid data type for JSON_TABLE column
40484. 00000 - "invalid data type for JSON_TABLE column"
*Cause: A column in the provided JSON_TABLE had an unsupported data type.
*Action: Provide a supported data type.
即使列中的型別也是date.
該型別char(36)也不起作用，所以我必須使用VARCHAR2(36).
%type在這種情況下可以使用嗎？
uj5u.com熱心網友回復：
該JSON_TABLE檔案提供了 JSON 查詢列子句的語法：
按照JSON_value_return_type檔案鏈接告訴您允許的資料型別：
鑒于此，不，您不能使用%TYPE或CHAR因為JSON_TABLE語法不允許。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/324353.html
上一篇：將值寫入多個JSON物件