Re: ORA-00904 over DBLINK

"Ashish" <srivastava.ashish_at_gmail.com> a écrit dans le message de news: 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

Database link between 8.1.7 and 10.2.0 is not supported. See Metalink note 207303.1

Regards
Michel Received on Thu Aug 27 2009 - 01:18:26 CDT