ORA-00904 over DBLINK
in [Oracle Help]
|
From: Ashish on 26 Aug 2009 21:56 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(a)convsourcedb mil ,mtl_material_statuses_vl(a)convsourcedb mms ,org_organization_definitions(a)convsourcedb ood ,(SELECT sum(total_qoh) total_qoh, subinventory_code, organization_id, locator_id FROM mtl_onhand_locator_v(a)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(a)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
From: Michel Cadot on 27 Aug 2009 02:18 "Ashish" <srivastava.ashish(a)gmail.com> a �crit dans le message de news: 85d4c60e-8bf1-4761-831b-fc46b7e13842(a)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(a)convsourcedb mil | ,mtl_material_statuses_vl(a)convsourcedb mms | ,org_organization_definitions(a)convsourcedb ood | ,(SELECT sum(total_qoh) total_qoh, | subinventory_code, | organization_id, | locator_id | FROM mtl_onhand_locator_v(a)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(a)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
From: Shakespeare on 27 Aug 2009 15:12 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(a)convsourcedb mil > ,mtl_material_statuses_vl(a)convsourcedb mms > ,org_organization_definitions(a)convsourcedb ood > ,(SELECT sum(total_qoh) total_qoh, > subinventory_code, > organization_id, > locator_id > FROM mtl_onhand_locator_v(a)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(a)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
|
Pages: 1 Prev: get the sid,serial# of my connection? Next: Oracle9i Client - where is it ? .... |