ORA-02289: sequence does not exist 1

[Fixles]

Hi,

I am trying to run the following SQL Insert

INSERT INTO CALL_PROGRESS_DETAIL 

(CLIENT_ID,
FAULT_REFERENCE, 
PROGRESS_DETAIL_ID, 
PROGRESS_DATE, 
PROGRESSED_BY_WHOM, 
PROGRESS_ACTION_DATE_AND_TIME, 
PROG_CAT_ID)

VALUES ('6', PROGRESS_DEATIL_ID.NEXTVAL,  '136426', SYSDATE, 'JAMESF', SYSDATE, '705');

The problem i am having is that i need to insert the next number in the sequence into progress_detail_id.

Anyone got any ideas how i can get this to work?

Cheers James

 

[lewisp]

Try

[tt]INSERT INTO CALL_PROGRESS_DETAIL
(CLIENT_ID,
FAULT_REFERENCE,
PROGRESS_DETAIL_ID,
PROGRESS_DATE,
PROGRESSED_BY_WHOM,
PROGRESS_ACTION_DATE_AND_TIME,
PROG_CAT_ID )
SELECT '6',
PROGRESS_DEATIL_ID.NEXTVAL,
'136426',
SYSDATE,
'JAMESF',
SYSDATE,
'705'
FROM SYS.DUAL; [/tt]

 

[avjoshi]

Fixles,

Your insert statement should work provided that sequence exists and you have access to it.

Can you post result of following query?

select owner,object_name,object_type from all_objects where object_name = 'PROGRESS_DEATIL_ID';

Anand

 

[Fixles]

LEWISP, Still getting ORA-02289: sequence does not exist tried replacing FROM SYS.DUAL with ALL_SEQUENCES but still no joy.

AVJOSHI, i ran the statment but no records were retrieved. I did however look through this table and found SEQ_PROGRESS_DETAIL_ID.

Cheers James

 

[avjoshi]

James,

Well then I think we are closer to the solution.

Try,

INSERT INTO CALL_PROGRESS_DETAIL
(CLIENT_ID,
FAULT_REFERENCE,
PROGRESS_DETAIL_ID,
PROGRESS_DATE,
PROGRESSED_BY_WHOM,
PROGRESS_ACTION_DATE_AND_TIME,
PROG_CAT_ID)
VALUES ('6', SEQ_PROGRESS_DETAIL_ID.NEXTVAL, '136426', SYSDATE, 'JAMESF', SYSDATE, '705');


Anand

 

[Fixles]

set the following error

ORA-20002: Parent does not exist in "INBOUND_HELPDESK_CALL". Cannot create child in "CALL_PROGRESS_DETAIL".
ORA-06512: at "FG_HELPDESK.TIB_CALL_PROGRESS_DETAIL", line 105
ORA-04088: error during execution of trigger 'FG_HELPDESK.TIB_CALL_PROGRESS_DETAIL'

The fault_reference is the primary key and the record 136426 does exist in INBOUND_HELPDESK_CALL

Cheers, James

 

[avjoshi]

It appears from your error, that you have master-detail relationship between INBOUND_HELPDESK_CALL and CALL_PROGRESS_DETAIL; INBOUND_HELPDESK_CALL being master table and CALL_PROGRESS_DETAIL detail. You most likely have foreign key constraint on CALL_PROGRESS_DETAIL referring to INBOUND_HELPDESK_CALL.

It appears as though whichever column in CALL_PROGRESS_DETAIL that has the foreign key constraint is getting wrong value.

I am sorry if I am vague, but without looking at your code this best I could come up with.


Anand

 

[Fixles]

TRIGGER "FG_HELPDESK"."TIB_CALL_PROGRESS_DETAIL" BEFORE INSERT ON "FG_HELPDESK"."CALL_PROGRESS_DETAIL" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

    --  Declaration of InsertChildParentExist constraint for the parent "INBOUND_HELPDESK_CALL"
    cursor cpk1_call_progress_detail(var_client_id integer,
                                     var_fault_reference integer) is
       select 1
       from   INBOUND_HELPDESK_CALL
       where  CLIENT_ID = var_client_id
        and   FAULT_REFERENCE = var_fault_reference
        and   var_client_id is not null
        and   var_fault_reference is not null;

    --  Declaration of InsertChildParentExist constraint for the parent "PROGRESS_CATEGORY"
    cursor cpk2_call_progress_detail(var_prog_cat_id integer) is
       select 1
       from   PROGRESS_CATEGORY
       where  PROG_CAT_ID = var_prog_cat_id
        and   var_prog_cat_id is not null;

    --  Declaration of InsertChildParentExist constraint for the parent "CALL_AUTHORISED_BY"
    cursor cpk3_call_progress_detail(var_authorisers_name varchar,
                                     var_client_id integer) is
       select 1
       from   CALL_AUTHORISED_BY
       where  AUTHORISERS_NAME = var_authorisers_name
        and   CLIENT_ID = var_client_id
        and   var_authorisers_name is not null
        and   var_client_id is not null;

    --  Declaration of InsertChildParentExist constraint for the parent "ABORT_CALL_CATEGORY"
    cursor cpk4_call_progress_detail(var_call_category_id integer) is
       select 1
       from   ABORT_CALL_CATEGORY
       where  CALL_CATEGORY_ID = var_call_category_id
        and   var_call_category_id is not null;

begin

    --  Parent "INBOUND_HELPDESK_CALL" must exist when inserting a child in "CALL_PROGRESS_DETAIL"
    if :new.CLIENT_ID is not null and
       :new.FAULT_REFERENCE is not null then
       open  cpk1_call_progress_detail(:new.CLIENT_ID,
                                       :new.FAULT_REFERENCE);
       fetch cpk1_call_progress_detail into dummy;
       found := cpk1_call_progress_detail%FOUND;
       close cpk1_call_progress_detail;
       if not found then
          errno  := -20002;
          errmsg := 'Parent does not exist in "INBOUND_HELPDESK_CALL". Cannot create child in "CALL_PROGRESS_DETAIL".';
          raise integrity_error;
       end if;
    end if;

    --  Parent "PROGRESS_CATEGORY" must exist when inserting a child in "CALL_PROGRESS_DETAIL"
    if :new.PROG_CAT_ID is not null then
       open  cpk2_call_progress_detail(:new.PROG_CAT_ID);
       fetch cpk2_call_progress_detail into dummy;
       found := cpk2_call_progress_detail%FOUND;
       close cpk2_call_progress_detail;
       if not found then
          errno  := -20002;
          errmsg := 'Parent does not exist in "PROGRESS_CATEGORY". Cannot create child in "CALL_PROGRESS_DETAIL".';
          raise integrity_error;
       end if;
    end if;

    --  Parent "CALL_AUTHORISED_BY" must exist when inserting a child in "CALL_PROGRESS_DETAIL"
    if :new.AUTHORISERS_NAME is not null and
       :new.CLIENT_ID is not null then
       open  cpk3_call_progress_detail(:new.AUTHORISERS_NAME,
                                       :new.CLIENT_ID);
       fetch cpk3_call_progress_detail into dummy;
       found := cpk3_call_progress_detail%FOUND;
       close cpk3_call_progress_detail;
       if not found then
          errno  := -20002;
          errmsg := 'Parent does not exist in "CALL_AUTHORISED_BY". Cannot create child in "CALL_PROGRESS_DETAIL".';
          raise integrity_error;
       end if;
    end if;

    --  Parent "ABORT_CALL_CATEGORY" must exist when inserting a child in "CALL_PROGRESS_DETAIL"
    if :new.CALL_CATEGORY_ID is not null then
       open  cpk4_call_progress_detail(:new.CALL_CATEGORY_ID);
       fetch cpk4_call_progress_detail into dummy;
       found := cpk4_call_progress_detail%FOUND;
       close cpk4_call_progress_detail;
       if not found then
          errno  := -20002;
          errmsg := 'Parent does not exist in "ABORT_CALL_CATEGORY". Cannot create child in "CALL_PROGRESS_DETAIL".';
          raise integrity_error;
       end if;
    end if;

  :NEW.PROGRESS_DATE := SYSDATE;

--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;

this is the trigger its talking about.

Cheers, James

 

[avjoshi]

Looks like the client_id value ('6' in your case) doesn't exist in INBOUND_HELPDESK_CALL.

By the way client_id in your trigger is treated as integer whereas, your select statement value has quotation marks arround it. You don't need them.

Anand

 

[Fixles]

The field client_id exists in Inbound_helpdesk_Call and the value 6 exists.

Any other ideas??

 

[avjoshi]

your lookup condition is

-- Declaration of InsertChildParentExist constraint for the parent "INBOUND_HELPDESK_CALL"
cursor cpk1_call_progress_detail(var_client_id integer,
var_fault_reference integer) is
select 1
from INBOUND_HELPDESK_CALL
where CLIENT_ID = var_client_id
and FAULT_REFERENCE = var_fault_reference
and var_client_id is not null
and var_fault_reference is not null;


and the values you pass to this cursor are,
open cpk1_call_progress_detailnew.CLIENT_ID,
:new.FAULT_REFERENCE);


if you look at your insert statement,

INSERT INTO CALL_PROGRESS_DETAIL
(CLIENT_ID,
FAULT_REFERENCE,
PROGRESS_DETAIL_ID,
PROGRESS_DATE,
PROGRESSED_BY_WHOM,
PROGRESS_ACTION_DATE_AND_TIME,
PROG_CAT_ID)
VALUES ('6', SEQ_PROGRESS_DETAIL_ID.NEXTVAL, '136426', SYSDATE, 'JAMESF', SYSDATE, '705');


Here the value for FAULT_REFERENCE is SEQ_PROGRESS_DETAIL_ID.NEXTVAL

Since you are comparing new sequence value with FAULT_REFERENCE in INBOUND_HELPDESK_CALL it will not match.
Are you sure you have values in insert statement ordered correctly?


Anand