ORA-00904 over DBLINK

From: Ashish <srivastava.ashish_at_gmail.com>
Date: Wed, 26 Aug 2009 18:56:13 -0700 (PDT)
Message-ID: <85d4c60e-8bf1-4761-831b-fc46b7e13842_at_k30g2000yqf.googlegroups.com>


I have a cursor which queries some tables over a DBLINK. The remote database is Oracle and package is being compiled on 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

,NULL row_organization_id
mil.segment4 Concatenated_Segments
,NULL row_item_id
,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

  WHERE 1=1
    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

Original text of this message