Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Apex Create Record using Query

SANPATJun 30 2022

Dear Friends
We had created a Form in Oracle Apex using wizard . In the form there is a field name as DOCNO. for which written a script . from the interactive report whenever we open the form and press apply button it always trigger and the docno changes to next. where as we want only during insert it should create not during modify. kindly suggest.
PROCESS -> After Submit
BEGIN
select nvl(max(DOCNO),0)+1 into:P102_DOCNO FROM Department;
:P102_ADD_DATE:=TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
:P102_CREATED_BY:=V('APP_USER');
END;

Sanjay
This post has been answered by fac586 on Jun 30 2022
Jump to Answer

Comments

fac586

The solution to the APEX problem is to use server-side conditions to control which processes are executed. These conditions would usually be When Button Pressed or REQUEST-based.
However there is a separate (and probably more important) issue in that the proposed code is unsafe in a multi-user environment.

select nvl(max(DOCNO),0)+1 into:P102_DOCNO FROM Department;

This query can return the same result for multiple users.

SANPAT

Dear fac586
We are creating the Docno During the save . I need once the docno is created with insert , the second time if we are updating the same record it should not update docno with new number.
image.pngimage.pngSanjay

fac586
Answer

Please do not attempt to describe problems by posting screenshots—especially of information that can be more effectively and concisely expressed as text (such as code, error messages, sample data etc). Screenshots are inaccessible to visually impaired forum users and search engines; waste bandwidth for those using mobile devices; and any relevant code or information therein cannot be copied in order to rapidly reproduce or debug problems. Additionally (as here) the images are frequently cropped so as to omit context and relevant information.
We are creating the Docno During the save . I need once the docno is created with insert , the second time if we are updating the same record it should not update docno with new number.
As previously posted, the solution to this is to use server-side conditions to control which processes are executed. These conditions would usually be When Button Pressed or REQUEST-based.
However there remains the issue that this code

select nvl(max(DOCNO),0)+1 into:P102_DOCNO FROM Department;

will result in errors or data integrity violations when used in a multi-user environment.

Marked as Answer by SANPAT · Jul 5 2022
1 - 3

Post Details

Added on Jun 30 2022
#apex, #apex-discussions, #oracle-apex
3 comments
374 views