BMQL Query does not return data when there is a single quotation in the value of the where condition while using JOIN (Doc ID 2914525.1)

Last updated on AUGUST 17, 2023

Applies to:

Symptoms

On : 22 D Patch 3 version, Scripting

ACTUAL BEHAVIOR
---------------
BMQL Query does not return data when there is a single quotation mark in the value of the where condition while using JOIN.

Values are returned successfully if the JOIN statement is not in place.

EXPECTED BEHAVIOR
-----------------------
It should work even when using JOIN.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1) Create two data tables with columns and values in common to be able to use the JOIN statement.
2) Add values with a single quotation in the middle. For example: adria'n5
2) Create a function to test the BMQL query.
3) Try to get one value of one table that has a single quotation mark "adria'n5".
    Example:
variable1 = "adria'n5";
records = bmql("SELECT col1 FROM table1 WHERE col1 = $variable1");
print records1; This will return the correct data "adria'n5".

4) Now, try to get the same value from two tables using INNER JOIN.
    Example:
variable1 = "adria'n5";
records = bmql("SELECT table1.col1 FROM table1 INNER JOIN table2 ON table1.col1 = table2.col1 WHERE table1.col1 = $variable1");
print records1; This will NOT return any data.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, it is not possible to use JOIN when there are data table values with a single quotation mark.

Cause

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