Oracle Apex Create Record using Query

SANPAT

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

fac586

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.

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.

Sanjay

fac586

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.