Query Using JSON_TABLE Returns Wrong Result On 19.9, While Same Query Works Fine On 19.8 (Doc ID 2733257.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On : 19.9.0.0.0 version, XML Database

ACTUAL BEHAVIOR
---------------
JSON_TABLE Wrong Result When All Columns Are Selected On 19.9 RU

14:56:37 SQL> -- Testquery 2 Selecting all required fields, does not work. All fields are returning null
14:56:37 SQL> SELECT *
14:56:37 2 FROM JSON_TABLE((SELECT BLOB_data FROM t_blob_test) FORMAT JSON,
14:56:37 3 '$'
14:56:37 4 COLUMNS(PERSNR VARCHAR2 PATH '$.LegacyAccountNumber__c',
14:56:37 5 SFDC_NR VARCHAR2 PATH '$.AccountNumber__c',
14:56:37 6 CHECKED_LAST_NAME VARCHAR2 PATH '$.LastName__c',
.
.
PERSNR SFDC_NR CHECKED_LAST_NAME CHECKED_FIRST_NAME CHECKED_MIDDLE_NAME COUNTRY STREET POSTCODE CITY EMAIL LAST_MODIFIED_BY SALUTATION TITLE ACC_STATUS LANGUAGE SALES_CHANNEL SALES_CHANNEL_TYPE ACCOUNT_OWNER TELEPHONE_NUMBER FAX_CODE ACCOUNT_TYPE CREDIT_TERM DATE_CREATED LEGITIMATION_EXECUTED LEGITIMATION_DATE COMPLIANCE_BLOCKED SALES_SUB_AREA SALES_AREA SALES_BRANCH MOBILE_NUMBER COMPANY_MOBILE_NUMBER EMAIL_2 CREFO_NR CUST_ACTIVATION_DAT SUFFIX VERBUND_UNTERN_KZ NACE_CODE RISK_RATING_CODE RATING_VERFAHREN LAST_CREDIT_CHECK CAPITAL_LIMIT1 NEXT_CREDIT_CHECK FREELANCER ACCOUNT_NAME COMPANY_REG_NO AMTSGERICHT TAX_REGISTRATION LEGAL_FORM FOUNDATION_DATE WEBSITE BMW_GK_NR INT_CUSTOMER TAXABILITY COMPANY_MAIL TYPE MP_NR COMP_NAME1 COMP_NAME2

4:56:37 SQL> --SQL Test 3 Selecting fewer fields works again:

14:56:37 SQL> SELECT *
14:56:37 2 FROM JSON_TABLE((SELECT BLOB_data FROM t_blob_test) FORMAT JSON,
14:56:37 3 '$'
14:56:37 4 COLUMNS(--PERSNR VARCHAR2 PATH '$.LegacyAccountNumber__c',
14:56:37 5 SFDC_NR VARCHAR2 PATH '$.AccountNumber__c',
14:56:37 6 --CHECKED_LAST_NAME VARCHAR2 PATH '$.LastName__c',
14:56:37 7 --CHECKED_FIRST_NAME VARCHAR2 PATH '$.FirstName__c',
14:56:37 8 --CHECKED_MIDDLE_NAME VARCHAR2 PATH '$.MiddleName__c',
14:56:37 9 --COUNTRY VARCHAR2 PATH '$.PostalCountryCode__c',
.
.
14:56:37 74 MP_NR VARCHAR2 PATH '$.MarketPartnerNo__c',
14:56:37 75 COMP_NAME1 VARCHAR2 PATH '$.CompanyName1__c',
14:56:37 76 COMP_NAME2 VARCHAR2 PATH '$.CompanyName2__c'));

SFDC_NR ACCOUNT_TYPE CREDIT_TERM DATE_CREATED LEGITIMATION_EXECUTED LEGITIMATION_DATE COMPLIANCE_BLOCKED SALES_SUB_AREA SALES_AREA SALES_BRANCH MOBILE_NUMBER COMPANY_MOBILE_NUMBER EMAIL_2 CREFO_NR CUST_ACTIVATION_DAT SUFFIX VERBUND_UNTERN_KZ NACE_CODE RISK_RATING_CODE RATING_VERFAHREN LAST_CREDIT_CHECK CAPITAL_LIMIT1 NEXT_CREDIT_CHECK FREELANCER ACCOUNT_NAME COMPANY_REG_NO AMTSGERICHT TAX_REGISTRATION LEGAL_FORM FOUNDATION_DATE WEBSITE BMW_GK_NR INT_CUSTOMER TAXABILITY COMPANY_MAIL TYPE MP_NR COMP_NAME1 COMP_NAME2
258121 test test test test test test test test test test test test test test......

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.