Developer Community

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

BIP reporting: getting 'component must be declared'

user-p191wOct 17 2023

I want to pass a human-readable param into my report, and use that to look up the corresponding ship-from location id. This will allow us to run the report once for each location.

So I added a P_ORGANIZATION_CODE param to the data model, and a WITH block to look up the location.

WITH ShipFromLocation As (
   SELECT SHIP_FROM_LOCATION_ID 
   FROM WSH_DELIVERY_DETAILS 
   WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM WSH_SHIPPING_PARAMETERS WHERE ORGANIZATION_CODE = :p_organization_code) and ROWNUM=1
)

I will then use NVL() to check if we got that param, and if we did I will filter by the ship-from location.

The WITH block works fine when I set up a new data model with just that param and do a select against it.

WITH ShipFromLocation As (
  SELECT SHIP_FROM_LOCATION_ID 
  FROM WSH_DELIVERY_DETAILS 
  WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM WSH_SHIPPING_PARAMETERS WHERE ORGANIZATION_CODE = :p_organization_code) and ROWNUM=1
)
SELECT * FROM ShipFromLocation

SHIP_FROM_LOCATION_ID

300000008801401

But when I add this param to my existing data model, select the Data tab and click on View, I get this:

ORA-06550: line 2, column 19: PLS-00302: component 'P_ORGANIZATION_CODE' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored

It is not referring to the WITH block. If I update that to

WHERE ORGANIZATION_CODE = :p_organization_codeZZZ

the result is the same. There is no other reference to P_ORGANIZATION_CODE in the query.