5 Replies Latest reply: Apr 9, 2013 7:59 AM by user134954 RSS

    Saving item values to table and confirm by table ID

    user134954
      Hi there,

      [Apex 4.2.1] Oracle 11g.

      I have build an interactive Report which is using some page items. Now I am able to write these Items into a table by using a button with a dynamic action:
      BEGIN
        INSERT INTO teststable
          (ID
          ,TESTVALUE
          )
        VALUES
          (teststable_sequence.NEXTVAL
          ,:P35_TEST
         );
      Now I want to confirm this like:
      Your current selection has been saved with id  'Select ID from teststable where .....'
      Is there a way to tell the user the ID which has just been created? Remeber there are multiple users online at the same time.

      Thx in advance
      Thorsten
        • 1. Re: Saving item values to table and confirm by table ID
          VC
          Hello,
          This is sql question and not apex, next time post in SQl forum and you will get a quick answer

          answer is to use returning into clause

          See http://www.oracle-base.com/articles/misc/dml-returning-into-clause.php
          • 2. Re: Saving item values to table and confirm by table ID
            user134954
            Well this is an apex question:

            I have tried this in sql-developer but in apex it will not work.

            Apex will not translate "SET SERVEROUTPUT ON" and throws an error.

            ORA-06550: line 3, column 5: PL/SQL: ORA-00922: missing or invalid option ORA-06550: line 3, column 1: PL/SQL: SQL Statement ignored (Go to error)
            • 3. Re: Saving item values to table and confirm by table ID
              user134954
              OK, now I know I need to use

              apex_application.g_print_success_message

              but this will not work either:
              DECLARE
                l_id testschema.testtable.id%TYPE;
              BEGIN
                INSERT INTO testschema.testtable
                  (ID
                  ,Testvalue
                  )
                VALUES
                  (testschema.testtable_sequence.NEXTVAL
                  ,:P35_TEST
                 )
              RETURNING id INTO l_id;
              
              COMMIT;
              apex_application.g_print_success_message := l_id
              END;
              Do you know why?
              • 4. Re: Saving item values to table and confirm by table ID
                VC
                Thorsten wrote:
                OK, now I know I need to use

                apex_application.g_print_success_message

                but this will not work either:
                DECLARE
                l_id testschema.testtable.id%TYPE;
                BEGIN
                INSERT INTO testschema.testtable
                (ID
                ,Testvalue
                )
                VALUES
                (testschema.testtable_sequence.NEXTVAL
                ,:P35_TEST
                )
                RETURNING id INTO l_id;
                
                COMMIT;
                apex_application.g_print_success_message := l_id
                END;
                Do you know why?
                It only works when the page is rendered

                Create a hidden item on your page or use if existing. eg P35_ID

                Edit your dynamic action that has this process

                amend the return clause as:
                RETURNING id INTO :P35_ID;
                Enter P35_ID into Page Items to Return

                Create a true action as:

                Action: Execute JavaScript Code
                Code: alert('Your current selection has been saved with id '+$v('P35_ID'));

                Please note that you can amend this to show more user friendly message instead of an alert
                • 5. Re: Saving item values to table and confirm by table ID
                  user134954
                  Thank you

                  Edited by: Thorsten on 09.04.2013 05:59