Home » SQL & PL/SQL » SQL & PL/SQL » ORA 1410
ORA 1410 [message #268690] Wed, 19 September 2007 06:48 Go to next message
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 #268695 is a reply to message #268690] Wed, 19 September 2007 07:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
I am facing ORA 1410
Where?
Did you write any sql or pl/sql block?
(oR) some where else?

By
Vamsi
Re: ORA 1410 [message #268696 is a reply to message #268690] Wed, 19 September 2007 07:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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 #269048 is a reply to message #268690] Thu, 20 September 2007 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>So any suggestion for my case please
Avoid using ROWID in your application code.
Re: ORA 1410 [message #269049 is a reply to message #269046] Thu, 20 September 2007 10:07 Go to previous messageGo to next message
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 #269114 is a reply to message #269049] Thu, 20 September 2007 13:37 Go to previous messageGo to next message
Naif
Messages: 12
Registered: August 2007
Location: KSA
Junior Member
First of all I appreciate your quick response and cooperative and team works
Laughing

Regarding to COMMIT every 5000 record to avoid UNDO Table space to get full since we have huge number of transactions in this database.

And Mr. anacedent suggest to avoid using ROWID in code and really I kike this suggestion because I am using ROWID at the beginning of my code, anyway I will improve my code by doing the following
1- Rewrite all SQL statement that use ROWID by using request ID and DISTINCT keyword
2- Improve error logging to catch in which line/statement error raised.
All this I will do it next week and I will update you gentlemen’s
Cool
Re: ORA 1410 [message #269147 is a reply to message #269114] Thu, 20 September 2007 16:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #290588 is a reply to message #290587] Sun, 30 December 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is at line 42.

A good link to be read from top to bottom: OraFAQ Forum Guide

Regards
Michel

[Updated on: Sun, 30 December 2007 02:51]

Report message to a moderator

Re: ORA 1410 [message #290594 is a reply to message #268690] Sun, 30 December 2007 05:54 Go to previous messageGo to next message
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
Re: ORA 1410 [message #290603 is a reply to message #290594] Sun, 30 December 2007 09:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://en.wikipedia.org/wiki/The_Hitchhiker%27s_Guide_to_the_Galaxy_%28book%29

You still don't read or don't want to follow the guidelines.
So... I confirm the error is at line 42.

Regards
Michel
Previous Topic: Create table for other users
Next Topic: how to fetch a row by row using sql
Goto Forum:
  


Current Time: Sun Dec 04 06:11:44 CST 2016

Total time taken to generate the page: 0.10418 seconds