Delete record with form's procedure or sql statment ?

Hello,

Could someone could help me to find a best solution in such situation?
I am not satisfied with form's working while deleting record...

One way is to create a push_button and to write a built-in procedure delete_record in it's
when-button-pressed trigger or simply use form's button 'Remove'.

But when you can't delete record, you only get a message FRM-40510 ORACLE error: Unable to DELETE record. You don't see a reason why you can't delete record.
There is one more thing: how to force a commit after record disapares (deleted) from form?
For example: there are some records in a table, some of them are related with another's table records and cannot be deleted, others can be deleted.
I press 'Remove'. Record disapare from form. Think is everything ok.
Press again 'Remove' and again 'Remove' to delete more records.
After deletion, I want to leave a form and got a message 'Do you want to save changes you have made?'. When I press Yes, I got an error FRM-40510...
Event if there was only one non deletable record, I see all deleted records are recovered.
It seems, I have to press commit after every record delete and check if there is related records to handle this error. What would be solution in such situation?

Second way is to write your own procedure in when-button-pressed trigger.
Procrdure's code below:

Declare
pragma exception_init(exc_no_del,-02292);

Begin
     'Delete from MySchema.T_Table1 where T_Table1.ID = :T_Table1.ID'
    commit;
execute_query;

exception
  when exc_no_del then Message('Cannot delete record');
End;

But why do such things when you have forms made for you?!? Is it a smart solution?

Also, there is some problem with code also
I have to write commit, otherwise, procedure doesn't delete a record.
But I got a message 'No change to save', while executing this procedure...

I have no ideas how to organize a delete in my forms :(
Could someone give me some advices?? I would be very thankful

Solveiga

            




LVL 1
SolveAsked:
Who is Participating?
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
I always use Forms functionality.
For commit - If you use Remove button then after pressing it press Save button - it is in fact commit. So if it is something wrong with deletion you will receive immidiatelly respond.
You ought to create a KEY-DELREC trigger on block. Here I post you one generated by Oracle Designer with my adding (function  MOZEM_MAZAT_REKL(0) in which I test things that cannot be tested by database). You can see what it is checking for and you can write your own:

BEGIN
IF MOZEM_MAZAT_REKL(0)=1 THEN
   RAISE Form_Trigger_Failure;
END IF;
END;

-- this part is generated by Designer - it uses Data Model (integrity, cascade delete etc.)
/* CGRI$CHECK_ON_DELETE */
/* This checks that the user is allowed to delete the current row, */
/* prior to removing it,  according to any referential integrity   */
/* rules held in the database design.                              */
BEGIN
  /* Warn user if deletion of row will cascade to others */
  IF (:REKLAM_REKL_OBJ.CGRI$USER_WARNED_ON_DELETE IS NULL) THEN
    BEGIN
      CGRI$WRN_OBJEDNAVKA(
        :REKLAM_REKL_OBJ.CGRI$USER_WARNED_ON_DELETE    /* MOD: Item used to flag if user warned on delete */
       ,:REKLAM_REKL_OBJ.ID                        );  /* IN : Item value                                 */
    EXCEPTION
      WHEN OTHERS THEN
        CGTE$OTHER_EXCEPTIONS;
    END;

    IF (:REKLAM_REKL_OBJ.CGRI$USER_WARNED_ON_DELETE IS NOT NULL) THEN
      RAISE FORM_TRIGGER_FAILURE;
    END IF;

  END IF;
END;

/* CGGN$KEY_DELREC */
/* Perform the key's standard functionality */
BEGIN
  delete_record;
  BEGIN
    CG$CHK_PACKAGE_FAILURE;
  EXCEPTION
    WHEN OTHERS THEN
      CGTE$OTHER_EXCEPTIONS;
  END;
END;

I hope this will help you.
0
 
sapnamCommented:
I agree with Henka.  Even I have a Key-delrec trigger which will check for referential integrity so that deletion is not allowed if there is an integrirty violation.

Also the No changes to save message can be avoided by setting the system.message_level to 10. As that message has a level of 5, this message will not come once the level is set to more than 5.

In When-new-form-instance you can code
:system.message_level := 10;
0
 
SolveAuthor Commented:
So, for me it is enought to create key-delrec
and put such code:

delete_record;
commit;
and handle an error if record cannot be deleted?

because a record disapears anyway and I have to do undo somehow

s.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Helena Markováprogrammer-analystCommented:
I think that you can do it.

But i would prefer this:

-- add here tests if record can be deleted
delete_record;
commit;
0
 
SolveAuthor Commented:
There is such error
'40150 - record cannot be deleted'

I got such error and for me is enought for this time
But you see what's happening:

I press 'Remove', when got message Record can not be deleted
and what to do next? The deleted record it is not in form enymore
I have to do execute_query wothout commit changes....

s.
0
 
SolveAuthor Commented:
So it seems I have to write my own procedure to check record cannot be deleted???

s.
0
 
Helena Markováprogrammer-analystCommented:
I have just tested this and record remains there:

BEGIN
   RAISE Form_Trigger_Failure;
END;
0
 
SolveAuthor Commented:
To check if there is no integrity violation manualy, would be very hard job for me, because
there is a lot of related tables in my schema ...

Or mabe you could give me a sample how to check integrity?

For know, I put such code:

delete_record;
commit;
execute_query(no validate);

s.
0
 
Helena Markováprogrammer-analystCommented:
Here is generated procedure:

/* CGTE$CHECK_CONSTRAINT_VIO */
FUNCTION CGTE$CHECK_CONSTRAINT_VIO(
   P_SERVER_ERR IN NUMBER        /* Server error message */
  ,P_SERVER_MSG IN VARCHAR2)     /* Server error number  */
RETURN BOOLEAN IS
/* Check and report Primary/Unique Key, Check Constraint and User */
/* Defined Constraint violations                                  */
  constraint_name VARCHAR2(61);  /* Violated constraint */
BEGIN
  /* Check if the error code is one we are interested in */
  IF ( NOT (P_SERVER_ERR IN  (1, 2290) OR
           (P_SERVER_ERR >= 20000 AND P_SERVER_ERR <= 20999) ) )
  THEN
    RETURN( FALSE );
  END IF;

  /* Deal with user defined errors */
  IF (P_SERVER_ERR >= 20000 AND P_SERVER_ERR <= 20999) THEN
    MESSAGE('Chyba: '||CGTE$STRIP_FIRST_ERROR(P_SERVER_MSG));
    RETURN( TRUE );
  END IF;

  /* Deal with Primary/Unique Key and Check Constraint violations */
  constraint_name := CGTE$STRIP_CONSTRAINT(P_SERVER_MSG);
  IF (P_SERVER_ERR = 1) THEN
    IF (constraint_name = 'OBJEDNAVKA_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým Id už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým &#268;íslo rekl. už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým Súvisiaca rekl. už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZIAD_PRIKAZ_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým Id už existuje');
    ELSIF (constraint_name = 'ZIAD_PRIKAZ_HL_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým *&#268;íslo rekl. už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'POZIADAVKA_POR_CISLO') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'TYP_ZIAD_KOD_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'VYSL_AKT_KOD_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým &#268;íslo rekl. už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým &#268;íslo rekl. už existuje');
    ELSIF (constraint_name = 'VYSL_AKT_KOD_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZOSTAVY_PARAMETRE_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým Id už existuje');
    ELSIF (constraint_name = 'DOKUMENTY_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým Id už existuje');
    ELSIF (constraint_name = 'PRACPREV_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRACPREV_APPUSER_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'POZIADAVKA_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'POZIADAVKA_POR_CISLO') THEN
      MESSAGE('Chyba: Riadok s rovnakým &#268;íslo požiadavky už existuje');
    ELSIF (constraint_name = 'TYP_PROD_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'TYP_PROD_KOD_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'PRODUKT_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'TYP_PROD_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'TYP_PROD_KOD_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým Produkt už existuje');
    ELSIF (constraint_name = 'TYP_ZIAD_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'TYP_ZIAD_KOD_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'OBJEDNAVKA_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým *&#268;íslo obj./rekl. už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_PK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSIF (constraint_name = 'ZAKAZNIK_CISLO_UK') THEN
      MESSAGE('Chyba: Riadok s rovnakým  už existuje');
    ELSE
      MESSAGE(P_SERVER_MSG);
    END IF;
  ELSIF (P_SERVER_ERR = 2290) THEN
    IF (constraint_name = 'CHK_DOBR_UCT_OBD_R') THEN
      MESSAGE('Chyba: Chybný rok (2003 a viac)');
      RAISE FORM_TRIGGER_FAILURE;
    ELSIF (constraint_name = 'CHK_DOBR_UCT_OBD_M') THEN
      MESSAGE('Chyba: Chybné &#269;íslo mesiaca (1-12)');
      RAISE FORM_TRIGGER_FAILURE;
    ELSIF (constraint_name = 'CHK_DOBR_UCT_OBD_R') THEN
      MESSAGE('Chyba: Chybný rok (2003 a viac)');
      RAISE FORM_TRIGGER_FAILURE;
    ELSIF (constraint_name = 'CHK_DOBR_UCT_OBD_M') THEN
      MESSAGE('Chyba: Chybné &#269;íslo mesiaca (1-12)');
      RAISE FORM_TRIGGER_FAILURE;
    ELSE
      MESSAGE(P_SERVER_MSG);
    END IF;
  END IF;
  RETURN( TRUE );
END;
0
 
SolveAuthor Commented:
And if I simly would like to organize a delete from form, how to cover deleted record if it is not possible to delete it?

delete_record;
commit;

then get a message 'Unable to DELETE record'

what' s next?

could you help?

s.
0
 
Helena Markováprogrammer-analystCommented:
In such case your user can execute_query and his/her answer to the query "Do you want to save changes" will be "No".

But I think that if you create a block with "Wizard" then all necessary triggers and procedures are generated by Forms.
0
 
SolveAuthor Commented:
Yes, I created with 'Wizard' but I cant see pre delete trigger (only if there are related data blocks).

I put execute_query(no_validate), so in that case I see my form without changes.

But still a lot if problems with this deletion :((
Sometimes I don't ness execute query, when deletion is success ... Whats when?

s.
0
 
Helena Markováprogrammer-analystCommented:
I meant your user has to execute query and  not you programmatically.
0
 
SolveAuthor Commented:
I think that is not good idea :(

Mabe I will manage thi sproblem somehow...
But still don't like like they work :)

s.
0
 
SolveAuthor Commented:
I will leave this question open for some times, mabe I'll need some help according this problem
Thank you very much for your help
s.
0
 
SolveAuthor Commented:
Hello, one more time.
Could someone explain me, why can't  I delete record by pressing button in witch when-button-pressed trigger such code is written:

delete from MySchema.MyTable where MyTable.ID = :MyBlock.ID ?

What do I need to do this delete to be done?

s.
0
 
Helena Markováprogrammer-analystCommented:
What does it mean that you cannot delete record - is there an eror there ?
0
 
SolveAuthor Commented:
No error, but record is not deleted from DB only from form
when I run form again, the record is still here

s.
0
 
Helena Markováprogrammer-analystCommented:
It seems that there is missing COMMIT or COMMIT_FORM there.
0
 
SolveAuthor Commented:
but when I write a commit, I got message - No change to save :/

s.
0
 
Helena Markováprogrammer-analystCommented:
In such case you have to set button property mouse navigable to false.
0
 
SolveAuthor Commented:
ok, going to try this

d
0
 
Helena Markováprogrammer-analystCommented:
If the button is in the control block or a block other than the one in which the record is in, make sure to set its Navigable property and Mouse Navigate property to False or Off. This ensures that the record to be deleted has input focus.
When you set these properties to True or On, Oracle Forms navigates to the button, places input focus in it, and tries to delete records in the button's block. FRM-40401 occurs because Oracle Forms has no records to delete and no changes to save.
0
 
SolveAuthor Commented:
I still get error message No changes to save....

Trying to delete record in such way:
I put this code to key-delrec trigger

delete from MySchema.MyTable where MyTable.ID = :MyBlock.ID ?
commit;

s.
0
 
Helena Markováprogrammer-analystCommented:
You can try to set :system.message_level:='10';

:system.message_level:='10';
delete from MySchema.MyTable where MyTable.ID = :MyBlock.ID;
commit;

or if it does not work use
FORMS_DDL('COMMIT');
instead of commit;

also you can use stored procedure fordeletion and pass :MyBlock.ID as parameter to it.
0
 
SolveAuthor Commented:
Ok, this worked
Forms_ddl('COMMIT');
0
 
SolveAuthor Commented:
Ok when, I assept answear and stay near second way!
Won't use forms functionality ...

s.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.