IndahnyaBerbagi

I needed this to hand off a canned query that can be used by many users without the need to constantly update the date parameters of the query. For instance “Show me sales totals for the previous month!“ or ” What was the total number of transactions last year?”

You could just type something like

Date >= to_date('11-01-2009', 'MM-DD-YYYY')

But that need users to change every month or year… boooo.

The solutions are not too pretty, but they work, And if you have a better way I would love to hear.. Googling proved no avail, and I had to dig this combination from various pieces in an O’Reilly Oracle book.

Without further delay..
Oracle SQL Query using start and end of Last Month as Dates
Basic Use

SELECT TRUNC(ADD_MONTHS(SYSDATE, -1),'MM') , LAST_DAY(ADD_MONTHS(SYSDATE,-1)) FROM TABLE

Sample Use
In this example we will get the sales total by agent and region for last month.

/*
* This query will retrieve all session summary records that occurred in the previous month
* Do not adjust the date fields, it should calculate correctly based on today's date.
*
* @Author Eddie Webb
*
*/

/* add TRUNC(ADD_MONTHS(SYSDATE, -1),'MM') , LAST_DAY(ADD_MONTHS(SYSDATE,-1)) to select to test dates */

SELECT AGENT_NAME, REGION, SALE_DATE, SUM(SALE_VALUE) total FROM TRS.SALES


WHERE
/* first day of previous month*/
/* adjust the -1 to adjust months back */
/* default: TRUNC(ADD_MONTHS(SYSDATE, -1),'MM') */
SALE_DAY >= TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')

AND
/*last day of last month*/
/* adjust the -1 to adjust months back */
/* default: LAST_DAY(ADD_MONTHS(SYSDATE, -1)) */
SALE_DAY <= LAST_DAY(ADD_MONTHS(SYSDATE, -1)) /* group sums by Client IDs Requestor, then system */ GROUP BY ROLLUP( AGENT_NAME, AGENT_NAME) How’s that work? If we focus on the basic use above you’ll notice two manipulations. The first one is the first day of the month. TRUNC(ADD_MONTHS(SYSDATE, -1),'MM') Start with today’s date using sysdate (3/27) and subtracts one month (2/27). We then Truncate the result using MM for the numerical value of the month(2). This will represent the first day of last month (2/1). The second manipulation requires the use of LAST_DAY instead of TRUNC. LAST_DAY(ADD_MONTHS(SYSDATE, -1)) Start with today’s date using sysdate (3/27) and subtracts one month (2/28). We then obtain the LAST_DAY (2/28). Note: no, there isnt a FIRST_DAY function or I would have used it. No, there isn’t a SUBTRACT_MONTHS function “” “” “”. Instead you can pass positive or negative numbers to ADD_MONTHS. 0 will get the dates for the current month. Oracle SQL Query using start and end of Last Year as Dates OK same basic premises here but extended. Basic Use SELECT TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY'), LAST_DAY(ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY'), 11)) FROM TABLE Sample Use In this example we will get the sales total by agent and region for last month. /* * This query will retrieve all session summary records that occurred in the previous YEAR * and provide a total count by client * Do not adjust the date fields, it should calculate correctly based on today's date. * * @Author Eddie Webb * */ SELECT REQUESTOR_ID, SYSTEM_ID, SUM(SESSION_COUNT) total FROM EBR.SESSION_SUMM WHERE /* first day of previous YEAR*/ SERVICE_DAY >= TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY')


AND
/*last day of last YEAR*/
SERVICE_DAY <= LAST_DAY(ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY'), 11))


/* group sums by Client IDs Requestor, then system */
GROUP BY ROLLUP( REQUESTOR_ID, SYSTEM_ID)

How’s that work?

If we focus on the basic use above you’ll notice two manipulations.
The first one is the first day of the year, also very simialr to the first day of last month, but different.

TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY')

Start with today’s date using sysdate (3/27/09) and subtracts 12 months (3/27/08). We then Truncate the result using SYYYY for the numerical value of the year(2008). Again, because of TRUNC’s behavior it will default to January 1st, 12:00 am of the truncated year. This will represent the first day of last year (1/1/2008).
The second manipulation is ugly at best, and depends on calculating the first day of the year.

LAST_DAY(ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY'), 11))

OR

ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY'), 12)

Again we start with today’s date using sysdate (3/27/09) and subtracts 12 months (3/27/08). We then Truncate the result using SYYYY for the numerical value of the year(2008). Again, because of TRUNC’s behavior it will default to January 1st, 12:00 am of the truncated year. This will represent the first day of last year (1/1/2008).

Now we go a step further by adding 11 months to the first day of last year (12/1/2008) and finally get the last day of that month using LAST_DAY (12/31/2008 12:00 am)

More Notes: Don’t rely on 365 days because some years have 366 (leap years) all years however have 12 months.
You may instead consider adding 12 months to 1/1/2008, and remove the LAST_DAY function will give you 1/1/2009 12:00 am, which may be your goal… (moments after 12/31/08 11:59 pm)

Applies to:

Goal

Solution

The business requirements appear to be straight-forward but it is not easy to implement in forms
due to the fact that COMMIT_FORM built-in is restricted e.g coding a POST-RECORD trigger as follows:

if :SYSTEM.RECORD_STATUS in ( 'CHANGED', 'INSERT' ) then
if Disp.YesNo ( 'Save ?' ) then
commit_form;
else
raise form_trigger_failure;
end if;
end if;

This does not work because COMMIT_FORM is a restricted built-in and cannot be called in a
POST-RECORD trigger.

A solution is to use a timer so the commit_form is executed outside of triggers like WHEN-VALIDATE-RECORD, POST-RECORD in which the built-in is restricted.

Example Code as follows (This simple example works in a Master Detail form based on demo tables DEPT and EMP)

1. Create
an alert called MYCOMMIT
Properties:

Message: Do you want to save the changes?
Alert Style: Stop
Button 1 Label : Yes
Button 2 Label: No

2. Code a WHEN-VALIDATE-RECORD trigger at form level

DECLARE
timer_id Timer;
/* set timer to a very lower value - number is in milliseconds */
timer_period NUMBER(5) := 60;
alert_button NUMBER;
BEGIN
IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
/* Display custom alert */
alert_button := SHOW_ALERT('mycommit');
IF alert_button = ALERT_BUTTON1 THEN
/* User has clicked 'Yes' to save changes, start timer when timer expires the commit_form will
execute outside of the WHEN-VALIDATE-RECORD trigger in the WHEN-TIMER-EXPIRED trigger */
timer_id := CREATE_TIMER('emp_timer', timer_period, NO_REPEAT);
END IF;
IF alert_button = ALERT_BUTTON2 THEN
/* User has clicked 'No', therefore raise form trigger failure so cursor remains in the current
record / item */
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
END;

commit_form;

A.) This note documents how to change the 'Do you want to save the changes you
have made?' alert that is displayed when exiting an application when there is
uncommited changes on the form.

By default, when exiting a form, Forms checks the status of the form, and
if there are uncommited changes, will display the alert asking if the user
wants to commit the data. Then, depending on the choice the user makes, forms
will commit or rollback the changes and exit the form, or do nothing if cancel
is chosen.

In order to customise this behaviour it is necessary to perform this
functionality yourself. The following steps are necessary to implement the
customised box:-

1: When exiting form, Check the form status.

2: If the status is changed, then display the customised alert.

3. Depending upon the user input either:-
a: commit the changes and exit the form.
b: Exit the form without saving the changes.
c: Do nothing if the user has canceled exiting the form.

The Code
--------

Create an alert ASK-COMMIT with the following attributes:-

Message: 'There are unsaved changes. Save them?'
button1: 'YES'
button2: 'CANCEL'
button3: 'NO'
Alert Style: 'CAUTION'

Then, in a KEY-EXIT trigger at form level, enter the following code:-
---------------------------------------------------------------------------

declare
button_val number;
begin
-- Perform validation at item level to update :SYSTEM.FORM_STATUS.
validate(ITEM_SCOPE);
-- Check the record status
if(:SYSTEM.FORM_STATUS = 'CHANGED') then
button_val := show_alert('ask_commit');
if button_val = ALERT_BUTTON1 then
-- If user chooses to commit changes...
commit_form;
exit_form;
elsif button_val = ALERT_BUTTON2 then
-- Do nothing if user cancels...
null;
elsif button_val = ALERT_BUTTON3 then
-- Exit without committing...
Exit_form(No_commit);
end if;
else
-- There have been no changes, just exit...
exit_form;
end if;
end;

----------------------------------------------------------------------------

Notes
-----

The validate(ITEM_SCOPE) line at the start is a useful bit of code that
updates the :SYSTEM.FORM_STATUS, :SYSTEM.BLOCK_STATUS or :SYSTEM.RECORD_STATUS.

In the example code above, if a user has entered some data and then tried to
exit without navigating out of the field, The form status will still be
'QUERY'. The validate built-in ensures the form status will be 'CHANGED'.

This code is generic and can be used in other places such as before issuing a
CALL_FORM in a WHEN-BUTTON-PRESSED trigger in order to get a user to
commit/rollback changes before navigating to a new form.
----------------------------------------------------------------------------


B.) HOW TO SUPPRESS "DO YOU WANT TO SAVE CHANGES" PROMPT IN MASTER-DETAIL?


Problem Description:
====================

A form is run that has a master and detail block. The user may navigate to
the detail block and make a change and then navigate back to the master block
and try to go to the next master record.

Oracle Forms 4.x or higher recognizes that the detail block has been changed and prompts
the user:

Do you want to save the changes you have made?

SQL*Forms 3.0 recognizes that the detail block has been changed and prompts
the user:

Do you want to commit the changes you have made?


Problem Explanation:
====================

How can you suppress this message/prompt? The user wants to automatically go
down to the next master record regardless of any changes made in the detail
block.



Solution Description:
=====================

Oracle Forms 4.x or higher:
---------------------------

Modify the CLEAR_ALL_MASTER_DETAILS procedure body. Change the existing
CLEAR_BLOCK(ASK_COMMIT) to CLEAR_BLOCK(DO_COMMIT).

SQL*Forms 3.0:
--------------
Modify the procedure CLEAR_DETAILS. Change the existing CLEAR_BLOCK(OPT) to
CLEAR_BLOCK(DO_COMMIT).

**Note: This is the quicker way to change the code. Trigger KEY-DELREC calls
clear_masterblockname_details(FALSE, NO_COMMIT) which in turn executes
clear_details('detailblockname', 'masterblockname', opt). If you modify the
clear_details procedure, it will always do clear_block(do_commit) since it is
hardcoded. The better way to change the functionality would be to change all
the trigger text to do clear_masterblockname_details(TRUE, NO_COMMIT) instead
of (TRUE, ASK_COMMIT) in triggers key-clrblk, key-clrrec, key-crerec,
key-down, key-entqry, key-exeqry, key-nxtrec, key-nxtset, key-prevrec,
key-scrdown, key-scrup and key-up. If you modify the procedure clear_details
to do clear_block(no_commit) then you just need to change the code in the
procedure.

Solution Explanation:
=====================

When you create a master-detail relationship form, certain triggers and
procedures are automatically created for you. To change the default
functionality, you need to change the clear_block statement.

The default parameter for CLEAR_BLOCK is ASK_COMMIT.

The options for clear_block are:

o ask_commit - Oracle Forms prompts the operator to commit the
changes during clear_block processing.
o do_commit - Oracle Forms validates the changes, performs a commit,
and flushes the current block without prompting the
operator.
o no_commit - Oracle Forms validates the changes and flushes the
current block without performing a commit or prompting
the operator.
o no_validate - Oracle Forms flushes the current block without
validating the changes, committing the changes, or
prompting the operator.


C.)HOW TO GET FROM NORMAL TO EXECUTE QUERY MODE WITHOUT BEING PROMPTED BY FORMS?

Problem Description:
====================

You are receving the following message from Oracle Forms Runtime when you try
to get into Enter Query mode or use Execute Query:

'Do you want to commit the changes you have made?'


Problem Explanation:
====================

How do I get into Query mode from normal processing mode to perform an Enter
Query or Execute Query without being prompted with this message? In normal
processing mode you can insert and update data in a block.

Solution Description:
====================

Use the Oracle Forms CLEAR_BLOCK built-in with commit mode DO_COMMIT,
NO_COMMIT or NO_VALIDATE before calling ENTER_QUERY or EXECUTE_QUERY.

For Example:
------------

You want to put the user in Enter Query mode or Execute a Query by pressing a
button. You have two buttons on your form: ENTER_QUERY_BUTTON and
EXEC_QUERY_BUTTON.

Create the following When-Button-Pressed triggers:

Item Level When-Button-Pressed trigger on ENTER_QUERY_BUTTON:
BEGIN
CLEAR_BLOCK(NO_COMMIT);
ENTER_QUERY;
END;

If you wish to save a value in one of the items in the block to include it as
query criterion for the EXECUTE_QUERY, also create a Pre-Query trigger.

Item Level When-Button-Pressed trigger on EXEC_QUERY_BUTTON:
BEGIN
/* Save the form item value in a global variable to use in */
/* Pre-Query trigger. */
:GLOBAL.item_name := :block.fieldname;

CLEAR_BLOCK(NO_COMMIT);
EXECUTE_QUERY;
END;

Block Level Pre-Query trigger:
BEGIN
:block.fieldname := :GLOBAL.item_name;
END;


Make sure that the Mouse Navigate property = FALSE for both buttons, otherwise
FRM-40202 will occur when the button is pressed.


Solution Explanation:
====================

Specifying NO_COMMIT or NO_VALIDATE with the CLEAR_BLOCK built-in causes
Oracle Forms to flush the current block without performing a commit
or prompting the operator.

Please be aware that any changes made to the data in the block will be cleared
and NOT SAVED to the database.


.

Locking Within SQL*Forms 3.0


SQL*Forms performs implicit locks on database blocks so that the users need
not code their own routines for data consistency. SQL*Forms also provides
the ON-LOCK trigger and LOCK_RECORD packaged procedure to allow further
control of the locks performed within the application.

With RDBMS V6.0 and higher, row level locking is now performed. Whole
table locks are no longer necessary to guarantee data integrity. It is
possible to lock an entire table (using the LOCK TABLE SQL command) but
this is usually undesirable since other users will not be able to update
any records within the table until the lock is released, either through
a COMMIT or a ROLLBACK.

SQL*Forms Use of Locking
~~~~~~~~~~~~~~~~~~~~~~~~

By default, data queried into a database block is not locked, unless an
ENTER_QUERY(FOR_UPDATE) or EXECUTE_QUERY(FOR_UPDATE) is used. The data
displayed is a snapshot of the data in the database at the time of the
query. The actual data can be changed "underneath" the user. The values
of the records obviously do not change as they are being viewed. Only
a subsequent requery will show any data that has been changed by other
users.

Let's say we have two users on the system, A and B. Both query the same
record on the screen. If user A types a character into any of database
fields, a

SELECT { all database fields in the block }
FROM { table which the block is based on }
WHERE rowid = { current row }
FOR UPDATE OF { all database fields in the block }

is performed. The FOR UPDATE option attempts to lock the record selected
so that no other users may change the information at the same time. If
the record is already locked, then the following error occurs:

Attempting to reserve record for update or delete (^C to cancel)...

or on some Unix platforms:

Could not reserve record (3 tries). Continue? ( Yes ) ( No )

Another "locking" issue that can occur is if user A and B query the same
record and user A modifies it and commits the change. User B, has a snapshot
of the "old" data on the screen. Obviously, B should not be allowed to
change the data since B has an "outdated" representation of the data. If
user B attempts to update the record, the following error message results:

FRM-40654: Record changed by another user. Re-query to see change.

No modification of the record can be done at this point until the record is
requeried to show the new changed data.

To review: when a user updates a record, SQL*Forms issues a SELECT ...
FOR UPDATE to determine if the record is currently locked. If it is not,
then SQL*Forms compares the values on the form to the values currently
in the database. If they are equal, then the record has not been changed
"underneath" the user. If they are not equal, then another user has committed
a change.



Locking Facilities Within SQL*Forms 3.0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The ON-LOCK trigger fires whenever SQL*Forms would normally attempt to
lock a row. The default functionality for this trigger is LOCK_RECORD;
Users may modify the ON-LOCK trigger to :

* bypass lock attempts on single user systems.

* lock the entire table by issuing the LOCK TABLE command.

* lock other rows in different tables as necessary.

The LOCK_RECORD packaged procedure "attempts to lock the row in the
database that corresponds to the current record." Other situations which
will cause a lock to be performed:

* Modification of database field on queried data

* ENTER_QUERY(FOR_UPDATE), EXECUTE_QUERY(FOR_UPDATE)

* DELETE_RECORD

Note that in block mode, the edit is allowed but SQL*Forms will place
an exclusive lock on the row during record validation. The ENTER_QUERY
(FOR_UPDATE) and EXECUTE_QUERY(FOR_UPDATE) lock only the records that have
actually been fetched (SQL*Forms queries in batches of records when they
are needed as specified by the ARRAY SIZE). The rest of the records can be
locked by either scrolling through the records or performing a LAST_RECORD
command. Note that an ENTER_QUERY(ALL_RECORDS,FOR_UPDATE) will fetch and
lock all the records satisfying the query conditions immediately.

Locks are released when:

* A commit performs successfully

* A full rollback (rollback to savepoint does NOT)

* The form aborts.

Note that unlike version 5, a CLEAR_RECORD or CLEAR_BLOCK does not
release any of the locks that may have been issued.


Using PL/SQL for greater control:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A common request of users is to be able to tell whether a row has been
locked or not, since the "Record Locked" message cannot be trapped via
ON-ERROR/MESSAGE triggers. The below PL/SQL block (written by Steve
Muench) takes advantage of the PL/SQL PRAGMA EXCEPTION which allows the user
to define their own exception based on the ORACLE error code. The -54
below results when an attempt is made to lock an already locked record.


DECLARE
dummy CHAR(1);
could_not_obtain_lock EXCEPTION;
PRAGMA EXCEPTION_INIT (could_not_obtain_lock,-54);
BEGIN
SELECT 'X' INTO dummy
FROM emp
WHERE rowid= :emp.rowid
FOR UPDATE OF empno NOWAIT;
MESSAGE('Locked the Current Row Successfully');
EXCEPTION
WHEN could_not_obtain_lock THEN
MESSAGE('Record is Already Locked. Please Try Later ...');
RAISE form_trigger_failure;
END;

The PL/SQL User's Guide and Reference contains information on user defined
exceptions using EXCEPTION_INIT.

Possibly Unexpected Results With Locking
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If the same record is queried and modified from two blocks on the same form
(therefore the same user) only the change on the block with the highest
sequence number in the block definition will be committed to the database.

a) a block level PRE-INSERT/UPDATE/DELETE modifying a database field in
a previous block. What happens is that during a commit, SQL*Forms
processes information block by block in the same order as in the
block definition screen. One or many SQL statements are produced for
each database block that has at least one database field changed.
When it finishes the SQL statement for the current block it moves
on to the next block. The PRE- triggers also fire on a block by block
basis and thus it is possible to modify a field using a PRE- trigger
on a previously processed block. In other words, the SQL statement
produced may be inaccurate. For example, a form has two database
blocks ONE and TWO that has an update ( :one.field='X') and an insert
pending respectively. When a COMMIT is performed, the PRE-UPDATE is
fired for block ONE and the SQL Statement is produced.

UPDATE one SET column=:one.field WHERE rowid= { current rowid }
^
= 'X' (the actual value)

Then SQL*Forms moves on to the next block and fires its PRE-INSERT.
If the PRE-INSERT contains code like:

:one.field := 'Y';

Then the error FRM-40654 will appear if the user attempts to update
block ONE after the COMMIT. The reason is that by the time PRE-INSERT
fires, block ONE's SQL Statement and block status have already been
set. What actually gets committed is the 'X' value even though 'Y'
shows up on the screen. If the user subsequently attempts to modify
block ONE, a

SELECT { fields in block ONE }
FROM one
WHERE rowid= { the rowid }
FOR UPDATE OF {fields in block ONE }

will be successful (since the record is no longer locked after the
commit). However, SQL*Forms will give the error after it determines
that the representation within the database ('X') does not match with
the current field value. It is not recommended to change fields on
different blocks in PRE-INSERT/DELETE/UPDATE triggers.

b) Another situation can arise with number precision. This can
probably best be described by an example. If table "x" contains a
column of type NUMBER (5,2). Then, create a form accessing that
field and type in a number with greater precision, (e.g. 10.243).
Commit the information and then attempt to edit the field. FRM-40654
results since the value that gets stored to the database (10.24)
is not equal to the value on the form (10.243).

c) A more complicated situation is documented in PCR # 93159.
Two users A and B query up the same record. User A modifies and
commits record. User B attempts to modify but gets correctly the
FRM-40654 error. However, if user A attempts to modify his/her
record, the error "Attempting to Reserve Record for Update/Delete"
occurs. The reason is that when User B attempts to modify the
record, a SELECT ... FOR UPDATE is issued first. B locks the
record successfully since A has already committed the change.
However, SQL*Forms then compares the field values and finds that
the representation within the database (modified by A) is different
from B's representation, thus the "Record has been changed" error.
But, the lock is NOT released by B. Thus if A attempts to remodify
the record he/she had just modified, they will be locked out!

d) A strange situation can also occur by clearing a lock in a called form
on an unsuspecting record in the calling form. Given that the calling
form is named FORM_A and the called form is named FORM_B, user A may
make a change in one of the database fields and CALL FORM_B before
validating the field (and therefore letting forms recognize the
change). The lock created by the first keystroke in that field is
still preserved. Note that a commit in FORM_B will now clear that
lock. User A now has misrepresented data on the screen. If a user
B modifies the same record, whoever commits last will have their
change committed. The workaround is to let SQL*Forms be aware that
a change has been made since SQL*Forms does not force validation on
CALL's and SET_INPUT_FOCUS(MENU)'s. Before the CALL, an ENTER; or
some navigation packaged procedure can be invoked to force validation.
The user will then receive the correct message in FORM_B on commit:

FRM-40403: A calling form has un-posted changes. Commit not allowed.

DECLARE
errm VARCHAR2(800);
errcode NUMBER := ERROR_CODE;
errtxt VARCHAR2(800) := ERROR_TEXT;
errtyp VARCHAR2(3) := ERROR_TYPE;

-- dbmserrcode NUMBER; --:= DBMS_ERROR_CODE;
dbmserrtext VARCHAR2(200) := DBMS_ERROR_TEXT;

v_alert_bttn NUMBER;
BEGIN
--FRM-40202: Field must be entered.
--FRM-40400: Transaction complete: %d records applied and saved.
--FRM-40401: No changes to save.
--FRM-40405: No changes to apply.
--FRM-40501: ORACLE error: unable to reserve record for update or delete.
--FRM-40508: ORACLE error: unable to INSERT record.
--FRM-40509: ORACLE error: unable to UPDATE record.
--FRM-40600: Record has already been inserted.
--FRM-40654: Record has been updated by another user. Re-query to see change.
--FRM-40656: Update cannot be made due to prior rollback. Clear the record.
--FRM-40700: No such trigger: %s.
--FRM-40800: User exit %s does not exist.
--FRM-41050: You cannot update this record.
--FRM-41830: List of Values contains no entries.
--FRM-50016:Legal character are 0-9- +E.

Set_Alert_Property ('ALERT2', TITLE, 'Error');
-- Error Insert and Update
IF errcode IN (40501, 40508, 40509, 40600, 40656) THEN
Set_Alert_Property ('ALERT2', Alert_Message_Text,
errtyp ||'-'||TO_CHAR(errcode)||': '||errtxt || CHR(10) ||
DBMS_ERROR_TEXT);
v_alert_bttn := Show_Alert ('ALERT2');
ELSIF errcode IN (40654, 40700,40800,40401,41050,40202,40405,40400) THEN
-- ignore fnd user exit n invalid trigger message
null;
ELSIF errcode IN (41830) THEN
Set_Alert_Property ('ALERT2', Alert_Message_Text,
'List of Values contains no entries.');
v_alert_bttn := Show_Alert ('ALERT2');
ELSE
APP_STANDARD.EVENT('ON-ERROR');
END IF;
END;