10 Replies Latest reply on Feb 3, 2017 6:05 PM by franklin.jr

    Oracle APEX 5.1 interactive grid - how to add a pseudocolumn

    franklin.jr

      I need to add a pseudocolumn on an interactive grid, so I need to know which parameters must be set to make it work. This pseudocolumn is based on the following instruction: row_number() over (partition by column group by column). I tried to add this pseudocolumn in my select list of columns, however it is causing error while saving because this content is not related to any database column at the table. Previous attempts led to an ORA-30483 (window functions are not allowed here) error. Thanks in advance.

        • 1. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
          Patrick Wolf-Oracle

          Hi Franklin,

           

          that's the perfect use case for the "Query Only" (in group Source) flag which can be set on the column level of an Interactive Grid. From the online help

           

          Specify whether to exclude the column from DML operations.

          If set to Yes, Application Express will not utilize the column when executing Interactive Grid - Automatic Row Processing (DML). For example, you should exclude columns whose definitions include concatenations, inner selects, function calls, or a column in an updateable view which is based on an expression.

          All columns which need to be included in any INSERT or UPDATE statements must have this option set to No.

          Note: Columns of type Display Only are also included in the Automatic Row Processing unless this option is set to Yes.

          Hope that helps

          Patrick

          • 2. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
            franklin.jr

            Dear Patrick, I am so sorry, I marked your answer as correct (sure it is, and will still be), however I forgot further testing and now I have other errors Please assume that "Database Column" field has the instruction "(row_number() over (partition by column group by column) as virtual_column)" as this text does not fit below. It causes ORA-00972 (identifier is too long) while rendering APEX page. What am I missing? I also tried to create this virtual column at the table, via alter table add..., to no avail. Thanks for helping, and sorry again.

             

            • 3. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
              Scott Wesley

              You need to provide a column alias

               

              select

              row_number() over (partition by column group by column) as column_alias

              from my_table

              • 4. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                franklin.jr

                Dear friends, please let me tell you in which context it is happening.

                 

                I have two tables, a master (primary) and a detail (items from primary table); you can think of it as a shopping cart with its items. My problem is in the detail table. It has sequences for both primary key (id) and its items, so I won't be performing a max + 1 for each ID. I simply take sequence1.nextval for ID, and sequence2.nextval for its related items.

                 

                However, end uses will need to see number items (1, 2, 3...) for that ID - and this is the problem: I tried to create a virtual column for detail table, and this virtual column is not working. I tried to create a virtual column on detail table, but I received ORA-30483 in response. So I tried to create this APEX virtual column on an interactive grid, but it raises ORA-00972.

                 

                Here comes my text for "Database column":

                 

                (select row_number() over (partition by my_column order by my_column) from my_table) as my_virtual_column

                 

                The following errors occurred while editing this APEX page.

                 

                1. Changed SQL query in which this editable interactive grid is based - I added the following column:

                "select ..., row_number() over (partition by my_column order by my_column) as my_virtual_column from my_table where ..."

                 

                ... it seemed to work at first, but it caused the following error after some editing and saving new data (not to mention how slow it became):

                 

                Process ' - Save Interactive Grid Data' raised 'ORA-02014' while performing row locking. This error can occur when the process issues a SELECT FOR UPDATE on a complex view with an instead-of trigger, to lock the row. Set the process attribute 'Lock Row' to No or PL/SQL Code.

                 

                2. I changed my process as required above, and tried to make some editing on my interactive grid and save data again. Then:

                Process ' - Save Interactive Grid Data' raised 'ORA-01733' while executing a DML command. This error can occur if a column is based on an aggregation or SQL expression. Set column attribute 'Query Only' to Yes to exclude the column from the INSERT and UPDATE statement.

                 

                I was surprised, my column was already query only.

                 

                3. So I did my last attempt: change its source from "database column" to "none". What am I missing?

                Process ' - Save Interactive Grid Data' raised 'ORA-01733' while executing a DML command. This error can occur if a column is based on an aggregation or SQL expression. Set column attribute 'Query Only' to Yes to exclude the column from the INSERT and UPDATE statement.

                 

                Sorry about my newbie questions but I am learning from myself while working.

                 

                Thanks in advance.

                • 5. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                  franklin.jr

                  I think it is fair to share the solution with the community once we find it.

                   

                  I had a look at the " Save Interactive Grid Data" process and I found out its target type was "Region source". I just changed it to "Table / View", informed the table name (given that my original query was based on only one table), and it worked. I think it was automatically generated by the "Editable Interactive Grid" process.

                   

                  If you all don't mind, could you please explain me why this solution worked? (Sure it is obvious after we know it.) I always thought the opposite - that I shouldn't touch anything that was created by the wizard.

                   

                  Thanks in advance.

                  • 6. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                    Patrick Wolf-Oracle

                    Hi Franklin,

                     

                    if you set the 'Target Type' = Region Source, the 'Save Interactive Grid Data' process takes the SQL statement specified in the region source, and wraps it into an INSERT/UPDATE/DELETE statement, depending on the row operation to perform. Normally that works quite well because the SQL engine will automatically eliminate columns which are not used in the INSERT or UPDATE statement itself. That's why it's so important to set the Query Only flag, because that flag is used to determine if a column should be part of the INSERT/UPDATE statement. In some situations the database SQL engine might not be clever enough and you end up with the above error.

                     

                    If you specify 'Target Type' = 'Table/View', we will ignore the original SQL statement which was used to populate the Interactive Grid, and instead use it to generate the INSERT/UPDATE/DELETE statements. So in your case statement, it will never contain information about the ROW_NUMBER() ...

                     

                    If you want to find out a little bit more what's done in the background, just run the page in debug mode and the debug output will show you which SQL and DML statements are executed.

                     

                    Regards

                    Patrick

                    1 person found this helpful
                    • 7. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                      franklin.jr

                      So thankful for your help, but now I receive the error below while trying to insert a new row. Column is "query only" already. Given that I don't want to annoy anyone with an unsolved problem, I will give up at this moment and return later. For now I will be using sequences.

                       

                      "Process ' - Save Interactive Grid Data' raised 'ORA-01733' while executing a DML command. This error can occur if a column is based on an aggregation or SQL expression. Set column attribute 'Query Only' to Yes to exclude the column from the INSERT and UPDATE statement.

                      • 8. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                        Patrick Wolf-Oracle

                        Hi Franklin,

                         

                        have you tried to run your page in debug mode to check how the INSERT statement looks like if you save changes. I think this would help to find out why you get this error. There is always the chance that this is a bug in our code, so it would help if you could try it again and share your details. Or if possible, setup a test case on apex.oracle.com.

                         

                        Regards

                        Patrick

                        • 9. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                          franklin.jr

                          Patrick Wolf-Oracle escreveu:

                           

                          Hi Franklin,

                           

                          have you tried to run your page in debug mode to check how the INSERT statement looks like if you save changes. I think this would help to find out why you get this error. There is always the chance that this is a bug in our code, so it would help if you could try it again and share your details. Or if possible, setup a test case on apex.oracle.com.

                           

                          Regards

                          Patrick

                          Thanks for being supportive, Patrick. I will try it and update this thread soon.

                          • 10. Re: Oracle APEX 5.1 interactive grid - how to add a pseudocolumn
                            franklin.jr

                            Debugging my INSERT statement, result comes below.

                             

                            insert into

                            (select "ROWID" "APEX$ROWID",

                                    "(columns)"

                               from (select (columns),

                                            row_number() over (partition by primary_key order by primary_key) as primary_key_v

                                       from my_table

                                      where primary_key = :P3_VALUE) q)

                                     ("COLUMNS")

                            values ("columns") returning "ROWID" into :apex$rc1

                             

                            In situation above, I included "row_number()..." as part of my SQL Query for my editable interactive grid. From above, it seems clear that row_number()... is being interpreted as "database column", so the error is "virtual column not allowed here".

                             

                            But if I create a new column which comes from SQL Expression, and define it as row_number()..., error is "window functions are not allowed".

                             

                            Soon I will create an environment which reproduces this situation.