My Oracle Support Banner

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 DECEMBER 01, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 19.9.0.0.0 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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