Forum Stats

  • 3,734,283 Users
  • 2,246,937 Discussions
  • 7,857,218 Comments

Discussions

Can I retrieve Table Value and set it into Application Item?

User9999
User9999 Member Posts: 27 Green Ribbon

Hello,

I have created a table, in which the state (of processes e.g. 'REQUESTED') and corresponding page is stored with the user.

Now everytime a user logs in, I want him to be redirected to the corresponding page depending on the stored state in the table.

to retrieve the status of a form from the logged in user, I did the following:

- created application item 'STATE_OF_FORM'.

- created application process to set the value into the application item

this takes the status from the table and should set it in the application_item.

Unfortunately it doesn't work as hoped, I think the error is in the syntax:

--this is the code to set stored table status into the status application item

declare
v_state varchar2(50);
begin
select fp.STATE into v_state
FROM PROCESSSTEPS_FORM fp
left outer join USERS u on fp.UUID=u.UUID
where u.username=:APP_USER;
Apex_Util.Set_Session_State('STATE_OF_FORM', v_state);
--exception handling needed otherwise the error 'ORA-01403: no data found' will
exception be raised
when no_data_found then
NULL;
end;

What do I do wrong?

This then I would like to use to redirect the user to the corresponding page with this PL/SQL Statement:

declare
v_state varchar2(50);
v_page number;
begin
 select fp.STATE, fp.PAGE into v_state, v_page from PROCESSSTEPS_FINALPAPERS fp where fp.AUDIT_USER=:APP_USER;
 IF v_state =V('STATE_OF_FORM')
 THEN
SELECT APEX_PAGE.GET_URL (
      p_page  => v_page )
      INTO v_page
   FROM DUAL;
   end if;
end;

But I always get the error , when I try to get to the correspoding page.

wwv_flow.branch_func_returning_url_error

Does the problem lie in my syntax?

Do I work correctly with the Application item and the process?

I am new to APEX and do not quite understand the problem here.

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond
    edited Apr 14, 2021 9:35AM

    Please update your community profile with a recognisable username instead of "User_9AZHT".

    Unfortunately it doesn't work as hoped

    Why not? What do you expect to happen?

    I think the error is in the syntax

    No, if there was a syntax error the code would not run at all. In fact, APEX probably would not accept it during development.

    --this is the code to set stored table status into the status application item

    Where is this code located?

    declare
    v_state varchar2(50);
    begin
    select fp.STATE into v_state
    FROM PROCESSSTEPS_FORM fp
    left outer join USERS u on fp.UUID=u.UUID
    where u.username=:APP_USER;
    Apex_Util.Set_Session_State('STATE_OF_FORM', v_state);
    --exception handling needed otherwise the error 'ORA-01403: no data found' will
    exception be raised
    when no_data_found then
    NULL;
    end;
    

    What do I do wrong?

    I think you have the outer join the wrong way round. Is it the case that all authenticated users will correspond to a row in the users table, but that they might not always be linked to a step in processsteps_form? If so you need to reverse the join:

    select
        fp.state
    from
        users u
          left outer join processsteps_form fp
            on u.uuid = fp.uuid
    where
        u.username = :app_user
    

    Note that an application item can be set using only this query by an Application Computation of type SQL Query (return single value).

    This then I would like to use to redirect the user to the corresponding page with this PL/SQL Statement:

    declare
    v_state varchar2(50);
    v_page number;
    begin
     select fp.STATE, fp.PAGE into v_state, v_page from PROCESSSTEPS_FINALPAPERS fp where fp.AUDIT_USER=:APP_USER;
     IF v_state =V('STATE_OF_FORM')
     THEN
    SELECT APEX_PAGE.GET_URL (
       p_page => v_page )
       INTO v_page
      FROM DUAL;
      end if;
    end;
    

    And where is this code located?

    Do not use select ... into ... from dual to execute PL/SQL functions from within a PL/SQL block. They can be used as expressions in the block wherever it is valid to do so:

    ...
    v_page_url := apex_page.get_url(...);
    ...
    

    But I always get the error , when I try to get to the correspoding page.

    wwv_flow.branch_func_returning_url_error

    Does the problem lie in my syntax?

    No. A syntax error would be picked up in the APEX Builder at design time. Again, this is a design, logic, or implementation issue. There is at least one of the latter here: apex_page.get_url returns a varchar2 result, and your code is trying to assign to a number variable.

    Do I work correctly with the Application item and the process?

    You are probably making the common novice mistake of trying to do too much using procedural code. The key to good APEX development is utilising the built-in declarative features so you don't have to write unnecessary code:


  • User9999
    User9999 Member Posts: 27 Green Ribbon

    Why not? What do you expect to happen?

    I want the user to be redirected to the corresponding page depending on his status of the form (which is stored in Table PROCESSES_FORM with the COLUMNS "USERID", "PAGE","STATE")

    For that I have created the application item : 'STATE_OF_FORM'

    and the application process to set the state, retrieved from the table , where the state is stored for the logged in user

    I the created a page with the button 'GoToStatus' with the behaviour submit and created a branching process for that, with the action

    'Function Returning an URL (Redirect)' with the following pl/sql code:

    declare
    v_state varchar2(50);
    v_stored_state varchar2(50);
    v_page number;
    
    begin
    v_state:= V('STATE_OF_FORM');
    select fp.STATE, fp.PAGE into v_stored_state, v_page from PROCESSSTEPS_FORM fp where fp.AUDIT_USER=:APP_USER;
    
    IF v_stored_state =v_state
    THEN
    SELECT APEX_PAGE.GET_URL (
    p_page => v_page)
    INTO v_page;
    
    FROM DUAL;
    end if;
    
    end;
    

    Where is this code located?

    declare
    v_state varchar2(50);
    begin
    select fp.STATE into v_state
    FROM USERS u
    left outer join PROCESSSTEPS_FORM fp on fp.UUID=u.UUID
    where u.username=:APP_USER;
    Apex_Util.Set_Session_State('STATE_OF_FORM', v_state);
    --exception handling needed otherwise the error 'ORA-01403: no data found' will
    exception be raised
    when no_data_found then
    NULL;
    end;
    

    The code is located in the application process

    I think you have the join the wrong way round. Is it the case that all authenticated users will correspond to a row in the users table, but that they might not always be linked to a step in processsteps_form? If so you need to reverse the join:

    You are right here, I've changed that.

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

    The code is located in the application process

    And what execution point does that process run at?

  • User9999
    User9999 Member Posts: 27 Green Ribbon

    after authentication

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

    As I said above, I would replace the Application Process with an Application Computation, as that reduces the required code to only the query:

    select
        fp.state
    from
        users u
          left outer join processsteps_form fp
            on u.uuid = fp.uuid
    where
        u.username = :app_user
    

    In the branch function body, the variable type problem identified above and the lack of a return value must be addressed:

    declare
    
      branch_page processsteps_form.page%type;
    
    begin
    
      /*
        Get the target page depending on the user's form state
      */
      select
          fp.page
      into
          branch_page
      from
          processsteps_form fp
      where
          fp.audit_user = :app_user
      and fp.state = :state_of_form;
    
      return apex_page.get_url(p_page => branch_page);
    
    exception
    
      when no_data_found
      then
        /*
          Use the current page as the default branch URL
        */
        return apex_page.get_url();
    
    end;
    


  • User9999
    User9999 Member Posts: 27 Green Ribbon

    Unfortunately the computation does not work accordingly, I tried the code in SQL Command.

    Everything seemed fine, but I always get the application computation error when trying to log in.

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,676 Black Diamond

    Unfortunately the computation does not work accordingly, I tried the code in SQL Command.

    Everything seemed fine, but I always get the application computation error when trying to log in.

    What error? We can't see your screen, nor can we test any code we suggest because we do not have access to your database objects and data. If you have a question about an error, you must post the relevant code, the full error message, and the situation in which it occurred. Post this information as text not screenshots so that it is fully accessible, and format code using the appropriate features in the forum editor.

  • User9999
    User9999 Member Posts: 27 Green Ribbon
    edited Apr 15, 2021 10:56AM

    I don't know what exactly the problem was with the computation but now it seems to work.

    However, in the branching process I get the following error when I click on the 'GoToLastStatus' button

    • wwv_flow.branch_func_returning_url_error

    Looking closer at the debug information I get: APEX - Error computing item default value for page item

  • User9999
    User9999 Member Posts: 27 Green Ribbon

    Ok I got it now, the problem was it returned more than one page because the user had several pages bound to the state. This would be different under normal conditions, as one page would be returned for each user.


    So thank you very much for your help!!

Sign In or Register to comment.