Forum Stats

  • 3,839,332 Users
  • 2,262,484 Discussions
  • 7,900,936 Comments

Discussions

Oracle Apex Create Record using Query

SANPAT
SANPAT Member Posts: 1,112 Silver Badge

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

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,101 Red Diamond
    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.

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,101 Red Diamond

    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
    SANPAT Member Posts: 1,112 Silver Badge

    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
    fac586 Senior Technical Architect Member Posts: 21,101 Red Diamond
    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.