ORA-01410: invalid ROWID

[dbadmirer]

Hi Gurus,

I am calling a stored procedure from one of my applications. Now the problem is i am getting this error. When i re run the application, the error disappears. I am unable to find out the reason behind this.

The error will be like this

ORA-01410: invalid ROWID

Database driver error...
Function Name : Fetch
SQL Stmt : SELECT STG_MODS_EO_ACFT_COMPLETE.EO_TYPE, STG_MODS_EO_ACFT_COMPLETE.EO_NUMBER, STG_MODS_EO_ACFT_COMPLETE.EO_REV_NUMBER, STG_MODS_EO_ACFT_COMPLETE.LATEST_REV_IND, STG_MODS_EO_ACFT_COMPLETE.EO_GROUP_NUMBER, STG_MODS_EO_ACFT_COMPLETE.MOD_REF_PROPOSAL_8, STG_MODS_EO_ACFT_COMPLETE.EFFECTED_ACFT_REG FROM STG_MODS_EO_ACFT_COMPLETE
Oracle Fatal Error
Database driver error...
Function Name : Fetch
SQL Stmt : SELECT STG_MODS_EO_ACFT_COMPLETE.EO_TYPE, STG_MODS_EO_ACFT_COMPLETE.EO_NUMBER, Oracle Fatal Error.

Here is the procedure ...

CREATE OR REPLACE PROCEDURE Explode_Sn_Temp
IS
ctr NUMBER(10):= 0;
flag BOOLEAN;
CURSOR serial_file (inp_ser_pn VARCHAR2)
IS
SELECT ser_pn, ser_serial_no,siid
FROM STG_SERIAL_NUMBER
WHERE ser_pn = inp_ser_pn;
CURSOR include_pn
IS
SELECT *
FROM STG_MODS_EO_APPL_SN_RANGE
WHERE component_pre_mod_pn IS NOT NULL;

CURSOR exclude_sn (
eo_no IN VARCHAR2,
eo_rev IN VARCHAR2,
eo_group IN VARCHAR2,
pn_no IN VARCHAR2
)
IS
SELECT component_pre_mod_pn, exclude_sn_range_from, exclude_sn_range_to
FROM STG_MODS_EO_APPL_SN_RANGE
WHERE eo_number = eo_no
AND eo_rev_number = eo_rev
AND eo_group_number = eo_group
AND component_pre_mod_pn = pn_no;
BEGIN
FOR x IN include_pn /* Start loop 1 */
LOOP
FOR y IN serial_file (x.component_pre_mod_pn)
LOOP /* Start loop 2 */
IF ( REPLACE(LPAD(y.ser_serial_no,15),' ',0) >= REPLACE(LPAD(x.include_sn_range_from,15),' ',0) AND
REPLACE(LPAD(y.ser_serial_no,15),' ',0) <= REPLACE(LPAD(x.include_sn_range_to,15),' ',0)
) OR (x.include_sn_range_from IS NULL OR x.include_sn_range_TO IS NULL)
THEN
flag :=FALSE;
FOR z IN exclude_sn (x.eo_number,
x.eo_rev_number,
x.eo_group_number,
x.component_pre_mod_pn
)
LOOP
IF ( REPLACE(LPAD(y.ser_serial_no,15),' ',0) BETWEEN REPLACE(LPAD(z.exclude_sn_range_from ,15),' ',0) AND
REPLACE(LPAD(z.exclude_sn_range_to,15),' ',0) ) OR ( z.exclude_sn_range_from IS NULL AND z.exclude_sn_range_to IS NULL)
THEN
flag :=TRUE;
END IF;
END LOOP;
IF flag = FALSE
THEN
INSERT INTO STG_MODS_EO_SN_EXPLODED
VALUES (x.eo_number, x.eo_rev_number,
x.eo_group_number, x.latest_rev_ind,
x.engine_pn, x.module_pn,
x.component_pre_mod_pn,
x.component_post_mod_pn, y.ser_serial_no,y.siid);
ctr := ctr + 1;
IF ctr >= 5000
THEN
COMMIT;
ctr := 0;
END IF;
END IF;
END IF;
END LOOP; /* End loop 2 */
END LOOP; /* End loop 1 */
COMMIT;
DELETE FROM STG_MODS_EO_SN_EXPLODED WHERE ROWID NOT IN (SELECT ROWID FROM STG_MODS_EO_SN_EXPLODED A WHERE A.ROWID =
(SELECT MAX(b.ROWID) FROM STG_MODS_EO_SN_EXPLODED b WHERE A.eo_number= b.eo_number
AND A.eo_rev_number = b.eo_rev_number AND A.siid = b.siid));
COMMIT;

END;

[topgun]

sir...

try using an alias when fetching rowid...

ex> select a.rowid r_id from table_name a

then use the r_id to your purpose....

hope this helps...