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

NewApexCoder

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?

ascheffer

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

Sample thread from THIS forum: 2181927

Thank you,

Tony Miller
Ruckersville, VA

NewApexCoder

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

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

ascheffer

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

Yes, shorter.. My compliments....

Thank you,

Tony Miller
Ruckersville, VA

NewApexCoder

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

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
  ...

NewApexCoder

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

No, his function is returning true or false.

NewApexCoder

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.