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>

Hi,

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


		,NULL    row_organization_id

		,mil.segment1||'.'||mil.segment2||'.'||mil.segment3||'.'||

mil.segment4 Concatenated_Segments


		,NULL			row_item_id

		,mil.description

		,mil.inventory_location_type	--no longer used

		,mil.picking_order

		,mil.location_maximum_units

		,mil.subinventory_code

   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;

ORGANIZATION_ID

---

            216

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.

Thanks,
Ashish Received on Wed Aug 26 2009 - 20:56:13 CDT