Oracle Analytics Cloud and Server

Lexical References to SQL Queries throws exception

Received Response
66
Views
8
Comments

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,

Answers

  • 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,

    <output rootName="DATA_DS" uniqueRowName="false">

    <nodeList name="data-structure">

    <dataStructure tagName="DATA_DS">

    <group name="G_2" label="G_2" source="STOPS">

    <element name="STOP_DATE" value="STOP_DATE" label="STOP_DATE" dataType="xsd:date" breakOrder="" fieldOrder="1" formatMask=""/>

    <element name="SL" value="SL" label="SL" dataType="xsd:string" breakOrder="" fieldOrder="2"/>

    </group>

    <group name="G_3" label="G_3" source="BINARY_IND">

    <element name="BINARY_IND_COUNT" value="BINARY_IND_COUNT" label="BINARY_IND_COUNT" dataType="xsd:double" breakOrder="" fieldOrder="1"/>

    </group>

    <group name="G_1" label="G_1" source="MAIN_QUERY">

    <element name="SHIPMENT_XID" value="SHIPMENT_XID" label="SHIPMENT_XID" dataType="xsd:string" breakOrder="" fieldOrder="1"/>

    <element name="SHIPMENT_GID1" value="SHIPMENT_GID1" label="SHIPMENT_GID1" dataType="xsd:string" breakOrder="" fieldOrder="2"/>

    <element name="SOURCELOCATIONGID1" value="SOURCELOCATIONGID1" label="SOURCELOCATIONGID1" dataType="xsd:string" breakOrder="" fieldOrder="3"/>

    <element name="CARRLOCATIONGID1" value="CARRLOCATIONGID1" label="CARRLOCATIONGID1" dataType="xsd:string" breakOrder="" fieldOrder="4"/>

    <element name="SHIP_FROM" value="SHIP_FROM" label="SHIP_FROM" dataType="xsd:string" breakOrder="" fieldOrder="5"/>

    <element name="DES_CITY" value="DES_CITY" label="DES_CITY" dataType="xsd:string" breakOrder="" fieldOrder="6"/>

    <element name="DES_PR_CODE" value="DES_PR_CODE" label="DES_PR_CODE" dataType="xsd:string" breakOrder="" fieldOrder="7"/>

    <element name="DES_PO_CODE" value="DES_PO_CODE" label="DES_PO_CODE" dataType="xsd:string" breakOrder="" fieldOrder="8"/>

    <element name="SHIPMENT_START_TIME" value="SHIPMENT_START_TIME" label="SHIPMENT_START_TIME" dataType="xsd:date" breakOrder="" fieldOrder="9" formatMask=""/>

    <element name="ICON_GID" value="ICON_GID" label="ICON_GID" dataType="xsd:string" breakOrder="" fieldOrder="10"/>

    <element name="UPDATE_DATE" value="UPDATE_DATE" label="UPDATE_DATE" dataType="xsd:date" breakOrder="" fieldOrder="11" formatMask=""/>

    <element name="CARRIER" value="CARRIER" label="CARRIER" dataType="xsd:string" breakOrder="" fieldOrder="12"/>

    <element name="TW" value="TW" label="TW" dataType="xsd:double" breakOrder="" fieldOrder="13"/>

    <element name="TSHUC" value="TSHUC" label="TSHUC" dataType="xsd:long" breakOrder="" fieldOrder="14"/>

    </group>

    <element name="P_COND_SERVICE_PROVIDER_PARAM" value="${plsql.TRUCKLOAD_SHIPMENT_RPT_PKG.P_COND_SERVICE_PROVIDER_PARAM}" label="P_COND_SERVICE_PROVIDER_PARAM" dataType="xsd:string" breakOrder="None" fieldOrder="0"/>

    <element name="P_COND_SOURCE_LOCATION_PARAM" value="${plsql.TRUCKLOAD_SHIPMENT_RPT_PKG.P_COND_SOURCE_LOCATION_PARAM}" label="P_COND_SOURCE_LOCATION_PARAM" dataType="xsd:string" breakOrder="None" fieldOrder="0"/>

    </dataStructure>

    </nodeList>

    </output>

  • 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

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

    Thanks,

  • 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.

  • 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

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

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