2 Replies Latest reply: Jan 1, 2013 3:00 AM by Osama_Mustafa RSS

    ora 1001

    949210
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      on Windows Server 2003
      ora 1001 all of a sudden while inserting data using procedures
      the insertion was going good for more than a year and a half
      create or replace
      PROCEDURE INS_REGESTRATION(
          P_NAME              IN REGISTRATION.NAME%type,
          p_TYPE              IN REGISTRATION.TYPE%type,
          P_ADDRESS1          IN REGISTRATION.ADDRESS1%type,
          P_ADDRESS2          IN REGISTRATION.ADDRESS2%type,
          P_COUNTRY           IN REGISTRATION.COUNTRY%type,
          P_STATE             IN REGISTRATION.STATE%type,
          P_CITY              IN REGISTRATION.CITY%type,
          P_PINCODE           IN REGISTRATION.PINCODE%type,
          P_LANDMARK          IN REGISTRATION.LANDMARK%type,
          P_CONTACT_NAME      IN REGISTRATION.CONTACT_NAME%type,
          P_TELEPHONE         IN REGISTRATION.TELEPHONE%type,
          P_MOBILE            IN REGISTRATION.MOBILE%type,
          P_EMAIL             IN REGISTRATION.EMAIL%type,
          P_WEBSITE           IN REGISTRATION.WEBSITE%type,
          P_COMPANY_DESC      IN REGISTRATION.COMPANY_DESC%type,
          P_CORE_COMPETANCY   IN REGISTRATION.CORE_COMPETANCY%type,
          P_UPLOAD_THUMBNAILS IN REGISTRATION.UPLOAD_THUMBNAILS%type,
          P_IAM               IN REGISTRATION.IAM%type,
          P_PASSWD            IN REGISTRATION.PASSWD%type,
          P_PLAN_ID           IN REGISTRATION.PLAN_ID%type,
          P_APPOINTMENT_ID    number,
          P_USERID       IN REGISTRATION.USERID%type,
          P_REG_ID OUT REGISTRATION.REG_ID%type,
          P_ERR_MSG OUT VARCHAR2
          /*,
          P_SEG_REG_ID         OUT SEGMENTS_REG.SEG_REG_ID%TYPE
          P_SEG_ID             IN SEGMENTS_REG.SEG_ID%TYPE,*/
        )
      AS
        P_UPLOAD_THUMB VARCHAR2(1000);
        v_rec_exist    NUMBER(3,0):=0;
      BEGIN
        SELECT COUNT(1)
        INTO v_rec_exist
        FROM REGISTRATION
        WHERE lower(NAME)=lower(P_NAME)
        AND pincode      =P_PINCODE;
        IF v_rec_exist  >=1 THEN
          P_REG_ID      :=0;
          P_ERR_MSG     :='Company already exists';
          GOTO l_end;
        END IF;
        /*select INS_REG_SEQ.NEXTVAL into P_REG_ID from dual; */
        P_REG_ID               :=FUN_PKEY('REG_ID','REGISTRATION');
        IF P_UPLOAD_THUMBNAILS IS NOT NULL THEN
          P_UPLOAD_THUMB       :=P_REG_ID||P_UPLOAD_THUMBNAILS;
        ELSE
          P_UPLOAD_THUMB:=NULL;
        END IF;
        INSERT
        INTO REGISTRATION
          (
            REG_ID,
            NAME,
            ADDRESS1,
            ADDRESS2,
            COUNTRY,
            STATE,
            CITY,
            PINCODE,
            LANDMARK,
            CONTACT_NAME,
            TELEPHONE,
            MOBILE,
            EMAIL,
            WEBSITE,
            COMPANY_DESC,
            CORE_COMPETANCY,
            UPLOAD_THUMBNAILS,
            IAM,
            TYPE,
            PASSWD,
            PLAN_ID,
            REG_DATE,
            USERID
          )
          VALUES
          (
            P_REG_ID,
            P_NAME,
            P_ADDRESS1,
            P_ADDRESS2,
            P_COUNTRY,
            P_STATE,
            P_CITY,
            P_PINCODE,
            P_LANDMARK,
            P_CONTACT_NAME,
            P_TELEPHONE,
            P_MOBILE,
            P_EMAIL,
            P_WEBSITE,
            P_COMPANY_DESC,
            P_CORE_COMPETANCY,
            P_UPLOAD_THUMB,
            P_IAM,
            P_TYPE,
            P_PASSWD,
            P_PLAN_ID,
            sysdate,
            P_USERID
          );
          
          if P_APPOINTMENT_ID is not null then
          
             insert into APPOINTMENT_REG(APP_REG_ID,
                       REG_ID,
                       APP_SCHEDULE_ID)
             values(fun_pkey('APP_REG_ID','APPOINTMENT_REG'),
                    P_REG_ID,
                    P_APPOINTMENT_ID);
          
          end if;
          
          
          
        P_ERR_MSG :='NO ERROR';
                   <<l_end>> NULL;
      EXCEPTION
      WHEN OTHERS THEN
        P_REG_ID:=0;
        DELETE FROM SEGMENTS_REG WHERE REG_ID=P_REG_ID;
        DELETE FROM PRODUCTS WHERE REG_ID=P_REG_ID;
        DELETE FROM REGISTRATION WHERE REG_ID=P_REG_ID;
        IF P_REG_ID  =0 THEN
          P_ERR_MSG :=SQLERRM;
        END IF;
      END;
      this is the main insert procedure being used
      NAME                                               TYPE        VALUE                                                                                                
      -------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
      open_cursors                                       integer     410                                                                                                  
      session_cached_cursors                             integer     20                                                                                                   
      Edited by: 946207 on Jan 1, 2013 12:27 PM

      Edited by: 946207 on Jan 1, 2013 12:28 PM