ORA-00904 over DBLINK
Date: Wed, 26 Aug 2009 18:56:13 -0700 (PDT)
I have a cursor which queries some tables over a DBLINK. The remote database is Oracle 18.104.22.168 and package is being compiled on 10.2.0.3 Since this morning I am getting the below error message while trying to compile the package:
112/10 PL/SQL: ORA-00904: "MIL"."ORGANIZATION_ID": invalid identifier
However, when I run the cursor query in a different SQL session, it fetches data. The cursor definition is
CURSOR curLocators(pOrganizationId NUMBER) IS
SELECT ood.organization_code organization_code
,mil.inventory_location_type --no longer used
FROM mtl_item_locations_at_convsourcedb mil ,mtl_material_statuses_vl_at_convsourcedb mms ,org_organization_definitions_at_convsourcedb ood ,(SELECT sum(total_qoh) total_qoh, subinventory_code, organization_id, locator_id FROM mtl_onhand_locator_v_at_convsourcedb GROUP BY locator_id, subinventory_code, organization_id ) mol
AND NVL(mil.status_id,1) = mms.status_id(+)
AND ood.organization_id = mil.organization_id AND mms.status_code = 'Active' AND mil.enabled_flag = 'Y' AND mol.organization_id = mil.organization_id AND mol.locator_id = mil.inventory_location_id AND mil.organization_id = pOrganizationId;
The column definetely exists in the base table SQL> select organization_id from mtl_item_locations_at_convsourcedb where rownum=1;
I tried to comment out the where clause which is throwing the error, but the package compilation fails with another Where clause error.
All this was working absolutely fine till last evening and I have not changed the cursor query since then.
How can I rectify this?
Any inputs would be greatly appreciated.
Ashish Received on Wed Aug 26 2009 - 20:56:13 CDT