11 Replies Latest reply: Feb 7, 2013 10:17 AM by TexasApexDeveloper RSS

    application item vs page item in Apex3.0

    steveFred
      I'm trying to save the user's ID to a table when they update a record. The client is using Apex 3.0

      I have something that works when the user initially logs on.
      On the log on page there is a page process that save the usernames to a page item:

      declare
      v varchar2(255) := null;
      c owa_cookie.cookie;
      begin
      c := owa_cookie.get('LOGIN_USERNAME_COOKIE');
      :P3_TEMP_USER := c.vals(1);
      exception when others then null;
      end;

      I have an item on the update page ( page 3) called P3_USER whose source is the column on the table where the user name is stored
      Then there is a page computation on this same page that sets the value of P3_USER equal to P3_TEMP_USER when submitted.

      This works when the user logs on and updates the first record. However if they update a second record ( without logging off and back on)
      it puts the name of the item in the column rather than the actually user name

      I've read posts about application page versus item page , so thinking that this needs to be stored at an application level to be saved, but
      I don't see anything in Apex 3.0. for creating an application item. Maybe this was introduced after 3.0

      Is there any other way to accomplish this?
        • 1. Re: application item vs page item in Apex3.0
          TexasApexDeveloper
          Do it in a table trigger...
          reate or replace trigger sample_trigger
          before insert on sample_table
          for each row
          begin    
              select sample_table_seq.nextval into :new.id from dual;
              :new.time_stamp     := systimestamp;
              $IF $$APEX $THEN
                  :new.user_name         := nvl(v('APP_USER'),user);
              $ELSE
                  :new.user_name         := user;
              $END
          end;
          This will allow you to cover data being inserted by an APEX app or someone using sqlplus/toad/sql Developer.. (the $IF $$APEX $THEN.. )

          It can be enhanced to also handle updates to the table too!!

          Thank you,

          Tony Miller
          Ruckersville, VA
          • 2. Re: application item vs page item in Apex3.0
            steveFred
            this is a table in the database, correct, not a part of Apex
            not understanding how Apex interacts with this
            • 3. Re: application item vs page item in Apex3.0
              TexasApexDeveloper
              This code runs in the database, when ever you INSERT a row into the named table.. You REALLY should read up on the database before trying to build APEX applications.. It will help you to understand database development concepts..

              Also you REALLY should ook at updating your instance of APEX, since 3.0 is NOT a supported product at this point in time..

              Thank you,

              Tony Miller
              Ruckersville, VA
              • 4. Re: application item vs page item in Apex3.0
                steveFred
                thanks

                Apex 3.0 is what the client has, its not my choice. I have no control over what they use. I have worked with databases for 20 years, Db2 mostly, in legacy systems. This client wants me to convert a legacy system to Oracle

                Edited by: steveFred on Feb 6, 2013 7:36 AM
                • 5. Re: application item vs page item in Apex3.0
                  TexasApexDeveloper
                  Understood to a point.. Since you are a CONSULTANT to them, you could try and SUGGEST that thy look at upgrading their software to a more current version.. Just a suggestion, so that they can get more BANG for their buck..

                  Thank you,

                  Tony Miller
                  Ruckersville, VA
                  • 6. Re: application item vs page item in Apex3.0
                    Kofi
                    True Steve, I feel your pain with regards to what the client is using but Tony has a very valid point about upgrading their version Apex.
                    You might be able to convince them to upgrade by doing some nice demos, and more importantly, reminding them by moving their legacy system to Apex 3, they'd be in a certain sense be moving from one Legacy system to another.
                    Kofi
                    • 7. Re: application item vs page item in Apex3.0
                      steveFred
                      I understand how to create a trigger, I dont understand the syntax

                      In this case the trigger is an update, not an insert, when the user updates any field on the record, I want to add the timestamp and the user ID
                      I took your insert example and tried to create an update trigger.
                      the columns are called updatedby and updatedate
                      If I do this:

                      create or replace
                      trigger add_user_name
                      before update of updatedby on Employee
                      begin
                      $IF $$APEX $THEN
                      :updatedby := nvl(v('APP_USER'),user);
                      $ELSE
                      :updatedby := user;
                      $END
                      end;

                      the trigger runs without error, and says it is valid. But when I update a record in the application, nothing happens.
                      I've tried before update and after update

                      If I try to create the trigger use the updatedate column ie
                      :updatedate := systimestamp;

                      I get a bind variable error message and can't create the trigger

                      I've seen other examples online that use a set columnName = value statement, like I would usually do in an update, but I can't get that to work either, always get syntax errors when I try
                      • 8. Re: application item vs page item in Apex3.0
                        steveFred
                        what I did initial was not using triggers

                        I created an item for timestamp, no problem , I can always get systimestamp

                        I created something that worked at captured the user name the first time they updated a record:


                        declare
                        v varchar2(255) := null;
                        c owa_cookie.cookie;
                        begin
                        c := owa_cookie.get('LOGIN_USERNAME_COOKIE');
                        :P3_TEMP_USER := c.vals(1);
                        exception when others then null;
                        end;


                        but if they update a second record, the value is gone
                        isn't there some way to save this value in an item ? Ive read about application items vs page items, but doesnt seem that Apex 3.0 has application items
                        • 9. Re: application item vs page item in Apex3.0
                          TexasApexDeveloper
                          If you look at the example given, you DO NOT use bind variables, you use the :NEW.<COLUMN_NAME> syntax to allow you to UPDATE the system time column, so your code would be:
                          :NEW.Updated_by  := nvl(v('APP_USER'),user);
                          and
                          
                          :NEW.Updated_by  := user;
                          Thank you,

                          Tony Miller
                          Ruckersville, VA
                          • 10. Re: application item vs page item in Apex3.0
                            steveFred
                            if I do this:

                            begin
                            $IF $$APEX $THEN
                            :NEW.Updated_by := nvl(v('APP_USER'),user);
                            $ELSE
                            :NEW.Update_by := user;
                            $END
                            end;


                            I get an error message: PLS-00049: bad bind variable 'NEW.Update_by' pointing to the $ELSE portion

                            If I take out the if /else and just do this:

                            begin
                            :NEW.Updatedby := nvl(v('APP_USER'),user);
                            end;

                            it works

                            I can live with that, I understand you're point about Apex users vs sql developer, etc

                            thanks for your help
                            • 11. Re: application item vs page item in Apex3.0
                              TexasApexDeveloper
                              I believe it MIGHT be a fact of the database you are using..

                              Also, next time when you close a thread, PLEASE assign points to those who have helped..

                              Thank you,

                              Tony Miller
                              Ruckersville, VA