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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Marková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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.