Solved

Delete record with form's procedure or sql statment ?

Posted on 2004-10-20
27
2,301 Views
Last Modified: 2013-12-12
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

            




0
Comment
Question by:Solve
  • 15
  • 11
27 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 100 total points
ID: 12378512
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
 
LVL 8

Expert Comment

by:sapnam
ID: 12387532
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
 
LVL 1

Author Comment

by:Solve
ID: 12398188
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12398234
I think that you can do it.

But i would prefer this:

-- add here tests if record can be deleted
delete_record;
commit;
0
 
LVL 1

Author Comment

by:Solve
ID: 12398271
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
 
LVL 1

Author Comment

by:Solve
ID: 12398276
So it seems I have to write my own procedure to check record cannot be deleted???

s.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12398328
I have just tested this and record remains there:

BEGIN
   RAISE Form_Trigger_Failure;
END;
0
 
LVL 1

Author Comment

by:Solve
ID: 12398346
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12398396
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
 
LVL 1

Author Comment

by:Solve
ID: 12398998
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12399047
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
 
LVL 1

Author Comment

by:Solve
ID: 12399114
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12399232
I meant your user has to execute query and  not you programmatically.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:Solve
ID: 12399247
I think that is not good idea :(

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

s.
0
 
LVL 1

Author Comment

by:Solve
ID: 12399254
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
 
LVL 1

Author Comment

by:Solve
ID: 12430799
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12430818
What does it mean that you cannot delete record - is there an eror there ?
0
 
LVL 1

Author Comment

by:Solve
ID: 12430835
No error, but record is not deleted from DB only from form
when I run form again, the record is still here

s.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12430851
It seems that there is missing COMMIT or COMMIT_FORM there.
0
 
LVL 1

Author Comment

by:Solve
ID: 12430870
but when I write a commit, I got message - No change to save :/

s.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12430900
In such case you have to set button property mouse navigable to false.
0
 
LVL 1

Author Comment

by:Solve
ID: 12430927
ok, going to try this

d
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12430929
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
 
LVL 1

Author Comment

by:Solve
ID: 12430953
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12431008
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
 
LVL 1

Author Comment

by:Solve
ID: 12431206
Ok, this worked
Forms_ddl('COMMIT');
0
 
LVL 1

Author Comment

by:Solve
ID: 12431430
Ok when, I assept answear and stay near second way!
Won't use forms functionality ...

s.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now