Re: ORA-00904 over DBLINK

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 27 Aug 2009 21:12:16 +0200
Message-ID: <4a96da96$0$186$e4fe514c_at_news.xs4all.nl>

Ashish schreef:
> 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

Are you compiling the package in the right schema? Did you change anything in grants etc?

Shakespeare Received on Thu Aug 27 2009 - 14:12:16 CDT