This discussion is archived
11 Replies Latest reply: Feb 7, 2013 8:17 AM by TexasApexDeveloper RSS

application item vs page item in Apex3.0

steveFred Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points