Oracle PL/SQL JSON函數出現 ORA-40474: JSON 資料中包含無效的 UTF-8 位元組序列

 當 Oracle DB Big5 ZHT16MSWIN950 使用 Oracle JSON函數 ORA-40474: JSON 資料中包含無效的 UTF-8 位元組序列，處理方式資料編碼轉換完成後再轉回

with qa as (

select 'TableA' as TT,'DataA' as cc,Convert('中文資料A', 'UTF8' , 'ZHT16MSWIN950') CDA,Convert('中文資料A之A', 'UTF8' , 'ZHT16MSWIN950') CDB from dual
union
select 'TableA' as TT,'DataB' as cc,Convert('中文資料B', 'UTF8' , 'ZHT16MSWIN950') CDA,Convert('中文資料B之B', 'UTF8' , 'ZHT16MSWIN950') CDB from dual)

SELECT replace(Convert(

json_object('Table' value r.TT,

'Data' value (

SELECT json_arrayagg(json_object('CC' value cc, 'CDA' value CDA, 'CDB' value CDB)) FROM qa c WHERE c.TT=r.TT) )

, 'ZHT16MSWIN950', 'UTF8'),'^','') CJSON 

FROM  qa r group by r.TT;

產生結果

{"Table":"TableA","Data":[{"CC":"DataA","CDA":"中文資料A","CDB":"中文資料A之A"},{"CC":"DataB","CDA":"中文資料B","CDB":"中文資料B之B"}]}

Oracle 單筆紀錄欄位與欄位取最大GREATEST ，取最小LEASE

 Oracle 使用 GREATEST(值[欄位]1, 值[欄位]2, ... 值[欄位]_n) 可以取得最大值，反之取最小LEASE

Oracle PL SQL 中使用: 與 & 變數

 Oracle PL SQL 中使用: 與 & 變數

with qq as (select 'A' col1,1 col2 from dual

union all select 'A' col1,2 col2 from dual

union all select 'B' col1,1 col2 from dual

union all select 'B' col1,2 col2 from dual

union all select 'C' col1,1 col2 from dual

union all select 'C' col1,2 col2 from dual)

select * from qq  where col1=:col1

單一變數放 字串A 時 select * from qq  where col1=:col1 結果為

COL1,COL2

A,1

A,2

單一變數放數值 2 時 select * from qq  where col2=:col2 結果為

COL1,COL2

A,2

B,2

C,2

當變數字串要在清單'A','B'時 select * from qq where col1 in (&col1) 

COL1,COL2

A,1

A,2

B,1

B,2

當變數數值要在清單1,2時 select * from qq where col2 in (&col2) 

COL1,COL2

A,1

A,2

B,1

B,2

C,1

C,2

Oracle 將資料列合併成一筆(xml_agg 同)

 方法一 SYS_CONNECT_BY_PATH

WITH QA AS

     (SELECT 'Row1' DROW, 'user1' EMP, 100 NUM  FROM DUAL

      UNION ALL

      SELECT 'Row2' DROW, 'user1' EMP, 90 NUM   FROM DUAL

       UNION ALL

      SELECT 'Row3' DROW, 'user1' EMP, 90 NUM   FROM DUAL

      UNION ALL

      SELECT 'Row4' DROW, 'user1' EMP, 80 NUM   FROM DUAL),

      QB AS (SELECT  EMP,NUM, COUNT(*) OVER (PARTITION BY  EMP  ) CNT,  ROW_NUMBER() OVER (PARTITION BY EMP  ORDER BY NUM)  SEQ    FROM QA )

           SELECT  EMP, SUBSTR(SYS_CONNECT_BY_PATH( NUM, ','), 2) COMBINE FROM QB

WHERE SEQ = CNT START WITH SEQ = 1 CONNECT BY PRIOR SEQ + 1 = SEQ AND PRIOR  EMP=EMP;     

方法二 Listagg

WITH QA AS 

     (SELECT 'Row1' DROW, 'user1' EMP, 100 NUM  FROM DUAL 

      UNION ALL 

      SELECT 'Row2' DROW, 'user1' EMP, 90 NUM   FROM DUAL 

       UNION ALL 

      SELECT 'Row3' DROW, 'user1' EMP, 90 NUM   FROM DUAL 

      UNION ALL 

      SELECT 'Row4' DROW, 'user1' EMP, 80 NUM   FROM DUAL) 

 SELECT  EMP,  LISTAGG(NUM, ',') WITHIN GROUP (ORDER BY NUM) AS  COMBINE FROM QA  group by EMP