3 Replies Latest reply on Feb 18, 2020 6:13 PM by persona4-77

    Strange behaviour for an insertion procedure

    persona4-77

      Hello everybody,

       

      I'm maintaining an APEX 4.2 application and after adding some features to the page, one procedure began to run uncorrectly.

       

      The goal of this page is to create an Event for the current master directory, and if the current master directory has children.  You must create the same Event for all the selected child directory.

       

       

       

       

      CREATE OR REPLACE PACKAGE BODY "directory_pkg" AS

       

       

       

       

      PROCEDURE do(p_in IN VARCHAR2) IS

        BEGIN

          NULL;

        END do;

       

       

      PROCEDURE add_directories(p_ids_event         id_tab,

                                      p_id_dir         NUMBER,

                                      p_descr        VARCHAR2) IS

        

      l_id number;

        BEGIN

        

        

          do(add_events(p_id =>        l_id,

                             p_id_dir         => p_id_dir,

                             p_descr         => p_descr));

                                                   

        

          FOR i IN 1 .. p_ids_event.count LOOP

          

            apex_debug.error(' ---> sub events ' || i || ' = ' || p_ids_event (i));

                                                                                                                                           

            do(add_events(p_id         => l_id,

                             p_id_dir         => p_ids_event(i) ,

                             p_descr         => p_descr));

                                                     

          

          END LOOP;

       

       

        EXCEPTION

          WHEN OTHERS THEN

             dbms_output.put_line(sqlerrm);

        END add_directories;

       

       

       

       

        FUNCTION add_events (p_id         IN OUT number,

                             p_id_dir         IN number,

                             p_descr        IN varchar2)

          RETURN VARCHAR2 IS

        BEGIN

       

       

          INSERT INTO events

            (idirectory_id,  descr)

          VALUES

            (directory_id,

             descr)

          RETURNING id INTO p_id;

          RETURN 'MD5';

        END ajouter_fnc;

       

       

      END directory_pkg;

       

      The problem is that the insert is creating a second record in the same directory.  Instead of inserting a record for 0001 and 0002, it creates 2 for 0001.  In my real code, the value for the dbms_output.put_line is the correct value.

       

      I don't know if you can have any idea of what could cause this ?

       

      I'll try to set a test example, the problem is that it's in 19.2 instead of 4.2.

       

      Workspace: personna4-77

      Username: test

      Environment: https://apex.oracle.com/pls/apex/

      password : Test1234

       

      I must recreate the pages before you can test it, I only did the minimal for now.  I'll update it next week.

        • 1. Re: Strange behaviour for an insertion procedure
          InoL
          In my real code, the value for the dbms_output.put_line is the correct value.

           

          How do you know? dbms_output doesn't do anything in Apex.

          How/when are you calling your procedure?

          Did you run the page in debug mode? Put more debug messages in your code if needed.

           

          BTW: I don't understand what the use of the "do" procedure is: "do(add_events..."?

          1 person found this helpful
          • 2. Re: Strange behaviour for an insertion procedure
            persona4-77

            Hello,

             

            I don't know either about the "do" procedure, but all our packages are used like that.  But it's the first time it does something like this.

             

            I run the page in debug mode by enabling it directly in the procedure  apex_debug.enable;  That's why I know the value in p_ids_event ( i )  just before the call of the add.  I added all the possible debug code I was able to do.  In both packages.

             

            I just finished to implement the algorythm in the event page.  When you create an event for the directory One, and the list of directories are over 1 and is not 1, you must create an event for the other directories.  It seems to work well.

             

            I don't get it why it's not working exactly like that in my real APEX 4.2 environment.  I looked at the dynamic actions, and I don't think they are submitting it twice, but it must be the case since the basic code here is working like it was intendend to.

             

            I tried to import the whole application, but I get a timeout from the website.  It seems importing a huge APEX 4.2 application in 19.2 is not working well.

             

            I tried to disactivate all the Dynamic Actions that weren't necessary to use the page and I still got the problem.  The second insert is not providing the id that is in the collection. But the code here in 19.2 is doing exactly what I want to do.  But that application is much more complex because we did a responsive application, so we got a very custom javascript code running behind.  We catch the events to know if the forms had changed and popup a warning window is showed. This is so weird.

            • 3. Re: Strange behaviour for an insertion procedure
              persona4-77

              I found it, it was simply because an edit function was called on the INSERT request and it shouldn't have.

               

              Thank you all for your help !