This discussion is archived
2 Replies Latest reply: Jan 1, 2013 1:00 AM by Osama_Mustafa RSS

ora 1001

949210 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points