Forum Stats

  • 3,750,345 Users
  • 2,250,159 Discussions
  • 7,866,944 Comments

Discussions

How to create an apex collection to store and loop through the values

NewApexCoder
NewApexCoder Member Posts: 256
edited May 30, 2013 3:36PM in APEX Discussions
I couldn't find any simple example to do this
Apex 4.2

I need to create an apex collection. I have a query that returns multiple roles for a user. I need to check to see if one of my item values is within the collection but am not sure how to do so. So far I have:

begin
APEX_COLLECTION.CREATE_COLLECTION (
p_collection_name => 'ALL_ROLES');

select count(granted_role) into count from dba_role_privs where upper(grantee) = upper(:APP_USER);

end;

How would I store the query results in the collection?
Tagged:

Best Answer

  • ascheffer
    ascheffer Member Posts: 1,903 Gold Trophy
    Accepted Answer
    Even shorter
    declare
      v_count number;
    begin
      if :P32_SUBMISSION_DATE is null
      then
        return false;
      end if;
      select count(*) into v_count
      from dba_role_privs 
      where upper(grantee) = upper(:APP_USER)
      and  granted_role in ('SURVEY_SUID','SURVEY_JOB_SUID');
    --
      return v_count > 0;
    end;
    Note that with an aggregrate function like count you will never get a no_data_found exception

Answers

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,796 Gold Crown
    Sample thread from THIS forum: 2181927

    Thank you,

    Tony Miller
    Ruckersville, VA
  • NewApexCoder
    NewApexCoder Member Posts: 256
    So I've tried to create the pl/sql process, but am still getting errors. Not sure where or what to fix

    declare
    is_viewable boolean;
    count number;
    l_query varchar2(2000);
    i_counter number(10) :=1;
    begin
    l_query := 'select granted_role from dba_role_privs where upper(grantee) = upper(:APP_USER)';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
    p_collection_name => 'ALL_ROLES',
    p_query => l_query,
    p_generate_md5 => 'YES');


    select count(granted_role) into count from dba_role_privs where upper(grantee) = upper(:APP_USER);


    IF :P32_SUBMISSION_DATE IS NOT NULL THEN
    FOR x IN 1..count
    IF l_query = 'SURVEY_SUID' or 'SURVEY_JOB_SUID' THEN
    is_viewable := true;
    ELSE
    is_viewable := false;
    ENDIF;
    END LOOP;
    ELSE
    is_viewable := false;
    END IF;
    return is_viewable;
    end;
  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,796 Gold Crown
    Trying to understand your code and a better suggestion:
    declare
    is_viewable boolean;
    v_count number;
    l_query varchar2(2000);
    i_counter number(10) :=1;
    begin
    
    --l_query := 'select granted_role from dba_role_privs where upper(grantee) = upper(:APP_USER)';
    
    --why do you need a collection, this is a simple select count(1) question
    
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
    p_collection_name => 'ALL_ROLES',
    p_query => l_query,
    p_generate_md5 => 'YES');
    
    -- You create a collection, but where are you using it?
    
    -- select count(granted_role) into count from dba_role_privs where upper(grantee) = upper(:APP_USER);
    -- discard the above code
    
    -- If you want to see  how many elements in a collection
    
    -- determine # of elements in a collection
    http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_collection.htm#CJAFFEAH
    
    v_count := APEX_COLLECTION.COLLECTION_MEMBER_COUNT ('ALL_ROLES');
    
    
    IF :P32_SUBMISSION_DATE IS NOT NULL THEN
    
      begin
    
      select count(granted_role) into v_count
         from dba_role_privs 
      where upper(grantee) = upper(:APP_USER) and
        granted_role in ('SURVEY_SUID','SURVEY_JOB_SUID');
    
      exception
      when no_rows_found then
        v_count := 0;
    
      end;
    
      if v_count > 0 then
        is_viewable := true;
    
      else
        is_viewable := false;
    
      endif;
    
    else
    
      is_viewable := false;
    
    endif;
    
    
    return is_viewable;
    end;
    Thank you,

    Tony Miller
    Ruckersville, VA
    TexasApexDeveloper
  • ascheffer
    ascheffer Member Posts: 1,903 Gold Trophy
    Accepted Answer
    Even shorter
    declare
      v_count number;
    begin
      if :P32_SUBMISSION_DATE is null
      then
        return false;
      end if;
      select count(*) into v_count
      from dba_role_privs 
      where upper(grantee) = upper(:APP_USER)
      and  granted_role in ('SURVEY_SUID','SURVEY_JOB_SUID');
    --
      return v_count > 0;
    end;
    Note that with an aggregrate function like count you will never get a no_data_found exception
  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,796 Gold Crown
    Yes, shorter.. My compliments....

    Thank you,

    Tony Miller
    Ruckersville, VA
  • NewApexCoder
    NewApexCoder Member Posts: 256
    I am grateful for all the feedback, however, I think my original question was missed. Let me try to explain furthermore:

    I have several users that have several different roles assigned to them. I was trying to make a process to see if the selected user had either "this" role or "that" role. Here is part of my process below:


    DECLARE
    is_read_only boolean;
    u_role varchar2(200);

    BEGIN
    SELECT GRANTED_ROLE INTO u_role FROM DBA_ROLE_PRIVS WHERE UPPER(GRANTEE) = UPPER(:APP_USER);



    /* INCOMPLETE STATUS */
    IF u_role = 'SURVEY' or u_role = 'JOB' THEN
    is_read_only := false;
    ELSE
    is_read_only := true;



    Return is_read_only;
    END;

    However, this gives an error within apex(4.2). Because the application user can have more than one role assigned to them, a mysql fetch error is returned. Not possible to store multiple values in a single variable. This is why I needed a table or collection to store all the roles associated to a particular user. Once they are stored, I can use an IF statement to see if one of the users role is stored in that table. This is where I'm having trouble.
  • InoL
    InoL Member Posts: 9,206 Gold Crown
    This is why I needed a table or collection to store all the roles associated to a particular user.
    But you already have that table: DBA_ROLE_PRIVS.
    I can use an IF statement to see if one of the users role is stored in that table.
    The previous solutions are just doing that. Wrap ascheffer's solution in a function (with slight modifications) and you can do:
    if user_has_role(:APP_USER, 'SURVEY') 
    or user_has_role(:APP_USER, 'JOB') 
    then
      ...
    InoL
  • NewApexCoder
    NewApexCoder Member Posts: 256
    in ascheffer's example, all I see him returning is the count, correct? I'm not sure how this checks whether or not the users role is either JOB or SURVEY
  • InoL
    InoL Member Posts: 9,206 Gold Crown
    No, his function is returning true or false.
  • NewApexCoder
    NewApexCoder Member Posts: 256
    Oh ok, I think the variable name was throwing me off (v_count) but I applied his code snippet and used the v_count in an IF statement and so far things are working properly. i.e.

    psqudo:
    IF v_count > 0 then the user has one of those roles
    ELSE the user doesn't

    Thanks for your help. Clearly I'm still learning PL/SQL.
This discussion has been closed.