Home » SQL & PL/SQL » SQL & PL/SQL » ORA 1410
ORA 1410 [message #268690] |
Wed, 19 September 2007 06:48  |
Naif
Messages: 12 Registered: August 2007 Location: KSA
|
Junior Member |
|
|
Dears
I am facing ORA 1410 and i check the metal link and i found it is bug in oracle
any one face same problem and how he solve it .
thanks
|
|
|
|
Re: ORA 1410 [message #268696 is a reply to message #268690] |
Wed, 19 September 2007 07:08   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Looking at the error message ORA-01410 invalid ROWID
Cause: A ROWID was entered incorrectly.
ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F.
A typical ROWID format is '000001F8.0001.0006'.
Action: Check the format, then enter the ROWID using the correct format.
ROWID format: block ID, row in block, file ID.
I wouldn't immediately jump to the conclusion that this was caused by a bug in oracle. The leading causes seem to be corrupt indexes and user error.
Do you have a bug reference number? If so (and if you've found that this is a bug from Metalink, you must have seen a Bug no) then that should tell you how to fix the problem.
Otherwise, you're going to have to give us more details to work with.
[fixed linesize]
[Updated on: Wed, 19 September 2007 08:09] Report message to a moderator
|
|
|
Re: ORA 1410 [message #268716 is a reply to message #268690] |
Wed, 19 September 2007 07:55   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Are you by any chance doing an UPDATE or DELETE through a databasel ink from v7 to 8i, 9i or 10g or vice versa?
|
|
|
Re: ORA 1410 [message #269046 is a reply to message #268716] |
Thu, 20 September 2007 09:51   |
Naif
Messages: 12 Registered: August 2007 Location: KSA
|
Junior Member |
|
|
Dears
thanks for reply
actually I have two tables InTab and OutTab and external system access my database and upload the huge number of records to InTab using SQLLoader and then execute one stored procedure in my database to process all records in InTab and apply all business rules then move it to OutTab in one for loop curser and once it is finish that request external system execute anther stored procedure to remove that records from InTab and COMMIT every 5000 records.
However ORA-01410 raised in stored procedure that move records from InTab to OutTab and I log it in error table,
finally external system access my database and insert(using SQLLoader) records in InTab and execute procedure in concurrent sessions to speed up processing requests and FYI I analysis these tables (InTab/OutTab) and it’s indexes in regular bases e.g every week.
So any suggestion for my case please
Thanks
|
|
|
|
Re: ORA 1410 [message #269049 is a reply to message #269046] |
Thu, 20 September 2007 10:07   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you are loading and removing large numbers of records, then your stats are useless unless you do them just after a load or remove operation.
ie stats gathered before a load process may well be worse than useless after the load process.
Does your error loging let you know at which line this error is raised? If not, go and improve your error logging until you do know where the error happens.
We can't help you unless we know what the code that raised the error message is (and that isn't a request to dump 100k of source code on us)
On a related note: why do a row by row delete and commit when you could just delete all the records at once, which would be quicker?
|
|
|
|
Re: ORA 1410 [message #269147 is a reply to message #269114] |
Thu, 20 September 2007 16:50   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Using ROWID in your code isn't a bad practice - it's the fastest way to access data. All you need to understand is that the rowid is not constant for the life of your data (move table, imp/exp etc all change the rowids). Processing your data based on it's physical co-location at the block level is more efficient (less IO and locking typically) rather than something like request ID that could be scattered throughout your segment's blocks.
|
|
|
Re: ORA 1410 [message #290511 is a reply to message #269147] |
Sat, 29 December 2007 05:17   |
Naif
Messages: 12 Registered: August 2007 Location: KSA
|
Junior Member |
|
|
Dears
still I am facing ORA 1410 and i did not use ROWID in code and i rebuild all indexes but still same error
FYI
* the error raised after moving some records from inTab to outTab
* i user for loop cursor for moving and i delete from inTab after i COMMIT outTab
any suggestion please.
|
|
|
Re: ORA 1410 [message #290517 is a reply to message #290511] |
Sat, 29 December 2007 05:55   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You didn't post your code so we can't tell you why exactly happened but if you rebuild indexes while other ones use this procedure (cursor loop) then you'll likely get this error.
Regards
Michel
[Updated on: Sat, 29 December 2007 05:56] Report message to a moderator
|
|
|
Re: ORA 1410 [message #290587 is a reply to message #268690] |
Sun, 30 December 2007 02:43   |
Naif
Messages: 12 Registered: August 2007 Location: KSA
|
Junior Member |
|
|
dears
mycode as follow:
CREATE OR REPLACE PROCEDURE PROCESS_WEB_TEMP(iREQUEST_ID IN NUMBER,
oResultCode OUT VARCHAR2) IS
tmpMSISDN VARCHAR2(20);
tempCustLang VARCHAR2(5);
tempCusType VARCHAR2(10);
tempMsgBdy sms.push_message.msg_body%TYPE;
araMsgBdy sms.push_message.msg_body%TYPE;
engMsgBdy sms.push_message.msg_body%TYPE;
tempCount1 NUMBER;
tempCount2 NUMBER;
tempSrvType VARCHAR2(30);
tempProcname VARCHAR2(50);
tempMsgType sms.webtemp.msg_type%TYPE;
tempGrpId sms.webtemp.group_id%TYPE;
tempUsrId sms.webtemp.user_id%TYPE;
tmpMSG_TYPE sms.webtemp.msg_type%TYPE;
MsgCount NUMBER;
TempUSER_FULL_NAME VARCHAR2(250);
-- to PLSQL log errors
tmpErrorCode NUMBER;
tmpErrorMsg VARCHAR2(255);
-- for exception Tracing
trace NUMBER;
BEGIN
/* Initialize variables */
tempSrvType := 'Campaign';
tempCount1 := 0;
tempProcname := 'PROCESS_WEB_TEMP';
oResultCode := '0'; -- Initialize '0' success
-- for trace
trace := 1;
SELECT MSG_TYPE,group_id,USER_ID
INTO tempMsgType,tempGrpId,tempUsrId
FROM WEBTEMP
WHERE REQUEST_ID = iREQUEST_ID
AND ROWNUM = 1;
/*--get message type for this Camping messages
SELECT MSG_TYPE INTO tempMsgType FROM WEBTEMP WHERE ROWNUM = 1;
-- get group ID for this for this Camping messages
SELECT group_id INTO tempGrpId FROM WEBTEMP WHERE ROWNUM = 1;
-- get user ID for this Camping messages
SELECT USER_ID INTO tempUsrId FROM WEBTEMP WHERE ROWNUM = 1;*/
-- for trace
trace := 2;
-- get to total of messages in this Camping messages
SELECT COUNT(*)
INTO MsgCount
FROM sms.webtemp
WHERE REQUEST_ID = iREQUEST_ID;
-- for trace
trace := 3;
-- for trace
trace := 4;
BEGIN
-- for trace
trace := 5;
-- Get user name of who send this message
SELECT USER_FULL_NAME
INTO TempUSER_FULL_NAME
FROM SMS.GMG_USERS
WHERE ID = tempUsrId;
EXCEPTION
WHEN OTHERS THEN
TempUSER_FULL_NAME := 'Name Not Available';
END;
-- for trace
trace := 6;
/* Log starting of procedure*/
INSERT INTO staging.process_log
(pl_date,pl_description,sw_component)
VALUES
(SYSDATE,
'Process_Web_Temp Started |' || 'User:' || tempUsrId || ' | ' ||
'Group:' || tempGrpId || ' | ' || 'Total Msg:' || MsgCount,
'Process Web_Temp');
COMMIT;
/* get message body based on the message type */
--get arabic Message
BEGIN
-- for trace
trace := 7;
SELECT msg_body
INTO araMsgBdy
FROM PUSH_MESSAGE
WHERE upper(msg_type) = upper(tempMsgType)
AND LANGUAGE = '8'
AND GROUP_ID = tempGrpId;
EXCEPTION
WHEN OTHERS THEN
araMsgBdy := NULL;
END;
--get english Message
BEGIN
-- for trace
trace := 8;
SELECT msg_body
INTO engMsgBdy
FROM PUSH_MESSAGE
WHERE upper(msg_type) = upper(tempMsgType)
AND LANGUAGE = '0'
AND GROUP_ID = tempGrpId;
EXCEPTION
WHEN OTHERS THEN
engMsgBdy := NULL;
END;
-- for trace
trace := 9;
/*********************************************
If user try to send message to customer with
body ISNULL for ARABIC and ENGLISH we will not
process this request since both are null
**********************************************/
IF araMsgBdy IS NULL AND engMsgBdy IS NULL THEN
oResultCode := '4';
--Log error
INSERT INTO staging.process_log
(pl_date,pl_description,sw_component)
VALUES
(SYSDATE,
'Process_Web_Temp Stoped AR/EN Msg is NULL|' || 'User:' || tempUsrId ||
' | ' || 'Group:' || tempGrpId || ' | ' || 'Total Msg:' || MsgCount,
'Process Web_Temp');
COMMIT;
RETURN;
END IF;
tempCount2 := 0;
/* Select all record from WEBTEMP in Cursor for loop */
FOR WEBTEMP_Rec IN (SELECT *
FROM SMS.WEBTEMP
WHERE REQUEST_ID = iREQUEST_ID) LOOP
-- for trace
trace := 10;
--Generate correct MSISDN format
tmpMSISDN := setMsisdn(WEBTEMP_Rec.Msisdn);
/*******Check basic customer information and type *******/
BEGIN
-- for trace
trace := 11;
--Fetch basic customer information
SELECT decode(a.LANGUAGE,
'0',
'0',
'1',
'0',
'2',
'0',
'3',
'0',
'4',
'0',
'8',
'8',
'0'),
concat(c.custtype,c.custsubtype)
INTO tempCustLang,tempCusType
FROM staging.cust_profile a,staging.network b,staging.base_info c
WHERE a.msisdn = tmpMSISDN
AND b.access_number = tmpMSISDN
AND b.disconnection_date = '99999999'
AND b.custnumber = c.custnumber;
EXCEPTION
WHEN OTHERS THEN
tempCusType := '****';
tempCustLang := '8';
tempMsgBdy := araMsgBdy;
END;
-- for trace
trace := 12;
-- check Arabic and English message templates availability
-- and Check which message is null Arabic or English
IF engMsgBdy IS NULL THEN
tempCustLang := '8';
ELSIF araMsgBdy IS NULL THEN
tempCustLang := '0';
END IF;
/*Check is there profile for this customer in IDB */
-- Insert WEBTEMP_Record into WEBOUT table
-- IF tempCusType = '****' AND SUBSTR(tempCusType,3,4) <> 'PU' THEN
-- we remove PU condetion @ 11 Feb 07 because its N/A
IF tempCusType = '****' THEN
IF tempCustLang = '0' THEN
tempMsgBdy := engMsgBdy;
ELSE
tempMsgBdy := araMsgBdy;
END IF;
INSERT INTO SMS.WEBOUT
(MSISDN,
LANGUAGE,
TYPE,
MSGBODY,
CUSTTYPE,
DELTIME,
SERVICETYPE,
REQUEST_ID)
VALUES
('966' || ltrim(tmpMSISDN,'0'),
tempCustLang,
WEBTEMP_Rec.Msg_Type,
tempMsgBdy,
tempCusType,
SYSDATE,
tempSrvType,
WEBTEMP_Rec.Request_Id);
ELSE
--the profile of this customer is exsite in IDB
--so we have to apply all rules
--Check if this custtype is supposed to receive this message type (filteration rules )
BEGIN
-- for trace
trace := 13;
IF SUBSTR(tempCusType,3,4) <> 'PU' THEN
SELECT COUNT(*)
INTO tempCount1
FROM PUSH_MESSAGE_BLOCKED
WHERE upper(MSG_type) = upper(WEBTEMP_Rec.Msg_Type)
AND substr(custtype_ID,1,4) = tempCusType;
IF tempCount1 = 0 THEN
-- no record found! so not blocked to receive this message type
-- Insert WEBTEMP_Record into WEBOUT table
--Get Customer langusge
-- and Select message Body based on customer profile(langusge)
IF tempCustLang = '0' THEN
tempMsgBdy := engMsgBdy;
ELSE
tempMsgBdy := araMsgBdy;
END IF;
INSERT INTO SMS.WEBOUT
(MSISDN,
LANGUAGE,
TYPE,
MSGBODY,
CUSTTYPE,
DELTIME,
SERVICETYPE,
REQUEST_ID)
VALUES
('966' || ltrim(tmpMSISDN,'0'),
tempCustLang,
WEBTEMP_Rec.Msg_Type,
tempMsgBdy,
tempCusType,
SYSDATE,
tempSrvType,
WEBTEMP_Rec.Request_Id);
ELSE
--This Customer Should not receive this message type
--WEBTEMP_Record error
INSERT INTO icm.error_log
(error_date,msisdn,ERROR_CODE,sw_component,Group_Id)
VALUES
(SYSDATE,tmpMSISDN,'119',tempProcname,WEBTEMP_Rec.Group_Id);
END IF;
ELSE
--This Customer Should not receive this message type
--WEBTEMP_Record error
INSERT INTO icm.error_log
(error_date,msisdn,ERROR_CODE,sw_component,Group_Id)
VALUES
(SYSDATE,tmpMSISDN,'119',tempProcname,WEBTEMP_Rec.Group_Id);
END IF;
EXCEPTION
WHEN OTHERS THEN
--log PLSQL error in GMG_Code_Errors table
tmpErrorCode := SQLCODE;
tmpErrorMsg := SQLERRM;
INSERT INTO rpt.Code_Errors
(ERROR_CODE,Error_Msg,Error_Date,Procedure_Name)
VALUES
(tmpErrorCode,
tmpErrorMsg || ' , ' || 'TraceID:= ' || trace,
SYSDATE,
tempProcname);
-- general exception
INSERT INTO icm.error_log
(error_date,msisdn,ERROR_CODE,sw_component,Group_Id)
VALUES
(SYSDATE,tmpMSISDN,'199',tempProcname,WEBTEMP_Rec.Group_Id);
END;
-- COMMIT every 5000 rows
tempCount2 := tempCount2 + 1;
IF MOD(tempCount2,5000) = 0 THEN
COMMIT;
END IF;
END IF;
END LOOP;
-- Loop delete all records of this request after Processing
LOOP
-- for trace
trace := 15;
--do the delete 4999 in each iteration
DELETE FROM SMS.WEBTEMP
WHERE REQUEST_ID = iREQUEST_ID
AND rownum < 5000;
EXIT WHEN SQL%ROWCOUNT < 4999;
COMMIT;
END LOOP;
COMMIT; -- commit the last delete
/* Log Ending of procedure*/
INSERT INTO staging.process_log
(pl_date,pl_description,sw_component)
VALUES
(SYSDATE,
'Process_Web_Temp End |' || 'User:' || tempUsrId || ' | ' || 'Group:' ||
tempGrpId || ' | ' || 'Total Msg:' || MsgCount,
'Process Web_Temp');
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- return Error code to GMG
oResultCode := '1';
--log PLSQL error in GMG_Code_Errors table
tmpErrorCode := SQLCODE;
tmpErrorMsg := SQLERRM;
INSERT INTO rpt.Code_Errors
(ERROR_CODE,Error_Msg,Error_Date,Procedure_Name)
VALUES
(tmpErrorCode,
tmpErrorMsg || ' , ' || 'TraceID:= ' || trace,
SYSDATE,
tempProcname);
INSERT INTO icm.error_log
(error_date,msisdn,ERROR_CODE,sw_component,Group_Id)
VALUES
(SYSDATE,tmpMSISDN,'194',tempProcname,tempGrpId);
COMMIT;
WHEN too_many_rows THEN
-- return Error code to GMG
oResultCode := '2';
--log PLSQL error in GMG_Code_Errors table
tmpErrorCode := SQLCODE;
tmpErrorMsg := SQLERRM;
INSERT INTO rpt.Code_Errors
(ERROR_CODE,Error_Msg,Error_Date,Procedure_Name)
VALUES
(tmpErrorCode,
tmpErrorMsg || ' , ' || 'TraceID:= ' || trace,
SYSDATE,
tempProcname);
INSERT INTO icm.error_log
(error_date,msisdn,ERROR_CODE,sw_component,Group_Id)
VALUES
(SYSDATE,tmpMSISDN,'195',tempProcname,tempGrpId);
COMMIT;
WHEN OTHERS THEN
-- general exception
-- return Error code to GMG
-- DBMS_OUTPUT.Put_Line('MSISDN = ' || tmpMSISDN);
oResultCode := '3';
--log PLSQL error in GMG_Code_Errors table
tmpErrorCode := SQLCODE;
tmpErrorMsg := SQLERRM;
INSERT INTO rpt.Code_Errors
(ERROR_CODE,Error_Msg,Error_Date,Procedure_Name)
VALUES
(tmpErrorCode,
tmpErrorMsg || ' , ' || 'TraceID:= ' || trace,
SYSDATE,
tempProcname);
INSERT INTO icm.error_log
(error_date,msisdn,ERROR_CODE,sw_component,Group_Id)
VALUES
(SYSDATE,tmpMSISDN,'199',tempProcname,tempGrpId);
COMMIT;
END PROCESS_WEB_TEMP;
/
[EDITED by LF: added [code] tags]
[Updated on: Sun, 30 December 2007 03:52] by Moderator Report message to a moderator
|
|
|
|
Re: ORA 1410 [message #290594 is a reply to message #268690] |
Sun, 30 December 2007 05:54   |
Naif
Messages: 12 Registered: August 2007 Location: KSA
|
Junior Member |
|
|
dear
line 42 is commented (lines from 41 to 48 are commented)
/*--get message type for this Camping messages
SELECT MSG_TYPE INTO tempMsgType FROM WEBTEMP WHERE ROWNUM = 1;
-- get group ID for this for this Camping messages
SELECT group_id INTO tempGrpId FROM WEBTEMP WHERE ROWNUM = 1;
-- get user ID for this Camping messages
SELECT USER_ID INTO tempUsrId FROM WEBTEMP WHERE ROWNUM = 1;*/
anyway i use ROWNUM in previous query line 35 to avoide TOO_MANY_ROWS exception it is impact and cause ora-1410
thanks
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 15:33:37 CST 2025
|