nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Lexical References to SQL Queries throws exception

Kanagaraja

Summary

Lexical References to SQL Queries throws exception

Content

Hello All,

We are in the process of migrating our custom reports from embedded BI Publisher in OTM to external BI Publisher 11g. While doing that we are running into issues with reports that uses lexical references in our SQL queries and it throws below error when trying to view the output in Datamodel,

"oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator"

Here is how we reference the lexical parameter in our SQL query WHERE condition,

&P_COND_SOURCE_LOCATION_PARAM

AND &P_COND_SERVICE_PROVIDER_PARAM

These two are the same variables that is being used in the PL/SQL package.

Can anyone help us understand what could be the issue here.

Thanks,

Find more posts tagged with

Accepted answers

All comments

Kanagaraja

Yes both the parameters are defined as in the pl/sql package as public variables. Here is the code snippet,

P_COND_SERVICE_PROVIDER_PARAM VARCHAR2(1000);

P_COND_SOURCE_LOCATION_PARAM VARCHAR2(1000);

IF P_SERVICE_PROVIDER IS NOT NULL THEN

P_COND_SERVICE_PROVIDER_PARAM := ' Service_Provider_Location.LOCATION_GID' || '' || REPORTS_LIBRARY.GET_FILTER_CONDITION(P_SERVICE_PROVIDER) || '';

ELSE

P_COND_SERVICE_PROVIDER_PARAM := '1=1';

END IF;

IF P_SOURCE_LOCATION IS NOT NULL THEN

P_COND_SOURCE_LOCATION_PARAM := 'SOURCE_LOCATION.LOCATION_GID' || '' || REPORTS_LIBRARY.GET_FILTER_CONDITION(P_SOURCE_LOCATION) || '';

ELSE

P_COND_SOURCE_LOCATION_PARAM := '1=1';

END IF;

Also here is the Data Model code as requested,

</group>

</group>

</group>

</dataStructure>

</nodeList>

</output>

Venkat Thota - BIP

have you defined a parameter in pl/sql package for this data model in new migrated bip environment ?

please do , if you can able to pass parameter directly in the sql queries instead of dynamically displaying where clause using pl/sql package ,

please post complete data model code , Thanks

Venkat Thota - BIP

please go through doc

https://blogs.oracle.com/xmlpublisher/lexical-reference-problems

Kanagaraja

Can anyone shed some light on what I'm missing here, I would greatly appreciate.

Thanks,

Brajesh Shukla-95078

Where these variable has been defined? Package body or spec..?

Variable defined in package specification(public) can be directly used in your sql.

For me looks like sql formation after laxical paramter bind is not correct, its forming some incorrect sql.

e.g.

Below is the report lexical paramteer

&P_COND_SOURCE_LOCATION_PARAM

AND &P_COND_SERVICE_PROVIDER_PARAM

And the new converted one in BI is below

IF P_SERVICE_PROVIDER IS NOT NULL THEN

P_COND_SERVICE_PROVIDER_PARAM := ' Service_Provider_Location.LOCATION_GID' || '' || REPORTS_LIBRARY.GET_FILTER_CONDITION(P_SERVICE_PROVIDER) || '';

ELSE

P_COND_SERVICE_PROVIDER_PARAM := '1=1';

END IF;

IF P_SOURCE_LOCATION IS NOT NULL THEN

P_COND_SOURCE_LOCATION_PARAM := 'SOURCE_LOCATION.LOCATION_GID' || '' || REPORTS_LIBRARY.GET_FILTER_CONDITION(P_SOURCE_LOCATION) || '';

ELSE

P_COND_SOURCE_LOCATION_PARAM := '1=1';

END IF;

You can see AND operator in where clause is handle between the lexical paramter in report but its missing in BI package.

This small kind of issue should be there please validate in detail.

Kanagaraja

Hello Brajesh,

Yes, it does look like the SQL formatting is incorrect. Is there a way to see the SQL's it construct in the log or some place else? The bipublisher logs I'm looking in weblogic EM just shows the invalid relational operator error nothing else.

As far as the variables it is defined in package spec. Also I have the AND operator handled in the SQl query, please see below

SELECT SHIPMENT.SHIPMENT_XID SHIPMENT_XID,

SHIPMENT.SHIPMENT_GID ,

Source_Location.LOCATION_GID SOURCELOCATIONGID1,

Service_Provider_Location.LOCATION_GID CARRLOCATIONGID1,

concat(concat(Source_Location.LOCATION_GID, ' '),Source_Location.LOCATION_NAME) SHIP_FROM,

Destination_Location.CITY DES_CITY,

Destination_Location.PROVINCE_CODE DES_PR_CODE,

Destination_Location.POSTAL_CODE DES_PO_CODE,

SHIPMENT.USER_DEFINED1_ICON_GID ICON_GID,

DATE_ACCEPTED.UPDATE_DATE UPDATE_DATE,

Service_Provider_Location.LOCATION_NAME CARRIER,

SHIPMENT.TOTAL_WEIGHT TW,

SHIPMENT.TOTAL_SHIP_UNIT_COUNT TSHUC

FROM SHIPMENT,

LOCATION Source_Location,

LOCATION Destination_Location,

SHIPMENT_STATUS DATE_ACCEPTED,

LOCATION Service_Provider_Location,

SERVPROV

WHERE (SHIPMENT.DEST_LOCATION_GID = Destination_Location.LOCATION_GID)

AND (SHIPMENT.SOURCE_LOCATION_GID = SOURCE_LOCATION.LOCATION_GID)

AND (SERVPROV.SERVPROV_GID = SHIPMENT.SERVPROV_GID)

AND ( DATE_ACCEPTED.SHIPMENT_GID(+) = SHIPMENT.SHIPMENT_GID

AND DATE_ACCEPTED.STATUS_TYPE_GID(+) = 'VAL.SECURE RESOURCES'

AND ( (DATE_ACCEPTED.STATUS_VALUE_GID(+) =

'VAL.SECURE RESOURCES_ACCEPTED')

OR (DATE_ACCEPTED.STATUS_VALUE_GID(+) =

'VAL.SECURE RESOURCES_WITHDRAWN')))

AND (Service_Provider_Location.LOCATION_GID = SERVPROV.SERVPROV_GID)

AND SHIPMENT.TRANSPORT_MODE_GID = 'TL'

AND SHIPMENT.USER_DEFINED3_ICON_GID = 'VAL.SHIPMENT_NOT_COMPLETE'

AND DATE_ACCEPTED.UPDATE_DATE IS NOT NULL

AND DATE_ACCEPTED.UPDATE_DATE > (SYSDATE - 365)

AND &P_COND_SOURCE_LOCATION_PARAM

AND Service_Provider_Location.LOCATION_GID = 'CHR'

and rownum < 5

ORDER BY Service_Provider_Location.LOCATION_NAME

Brajesh Shukla-95078

can you use without & (i.e. &P_COND_SOURCE_LOCATION_PARAM directly refer P_COND_SOURCE_LOCATION_PARAM)

Kanagaraja

I tried that too, it doesn't allow me to use the parameter without &