oracle pl/sql

Posted on 2007-08-01
Last Modified: 2013-12-12
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
Question by:faizershez
    LVL 28

    Accepted Solution

    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.

    LVL 12

    Assisted Solution

    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.
    LVL 2

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    .csv files 5 62
    how to tune this sql query 61 74
    Need help with Oracle syntax 4 31
    Pl/SQL Query 31 35
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    758 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

    12 Experts available now in Live!

    Get 1:1 Help Now