Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

oracle pl/sql

i have a master detail form build in oracle forms 6i version
in detail canvas i have a field name call sequence that user will input the sequentialy
if the user enter sequence duplicate how to fire the trigger before saving
3 Solutions
Naveen KumarProduction Manager / Application Support ManagerCommented:
i think you can use when validate record trigger to do that validation.

inside the pl/sql code of this trigger, you  can use next_record, first_record, last_record
builtin to navigate to the next record and till all the records in the multi record block are

or i think you can even do this in pre-commit ...

navigate to the multi record block by using go_block('detail');
then use the similar logic as above.

I am not sure which is restricted builtin and which is unrestricted now because i worked on
forms few years back.

i'm not sure if go_block, next_record, first_record, ... are allowed in validate triggers.

you don't want to commit every record so you can read the sequence from the database, do you?

instead of navigating through all records you could store the sequences locally in a pl/sql table and check at every entry if value exists.
Procedures like next_record, go_record etc. are "restricted procedures", which cannot be used in validate triggers.

You should either use a PL/SQL table as proposed by jwahl, or have Forms check for duplicates automatically. In the detail block, set the "Enforce primary key" property to Yes. Then set the "Primary key" property to Yes for each primary key item. (I suppose that sequence is part of the primary key.) This solution is not perfect, however, because duplicates will only be checked upon commiting, not immediately as the user enters a sequence number.

Another possible solution, if you can apply it to your data, is to change program logic so that the sequence is generated by the form, not entered by user.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now