OraFAQ Forum: SQL & PL/SQL » "No data found" stops the loop action inside PL/SQL block

Home » SQL & PL/SQL » SQL & PL/SQL » "No data found" stops the loop action inside PL/SQL block (Oracle 11.2.0.1.0 on OEL 5.7 (VM))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

"No data found" stops the loop action inside PL/SQL block [message #588994]

Mon, 01 July 2013 04:20

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi all,

I am trying to create an anonymous PL/SQL block to output privilege information for each of the users listed in DBA_USERS
In a loop. This is my block so far (not finished):

Code: [Select all] [Show/ hide]

declare
v_usr varchar2(30);
v_out_header varchar2(100);
v_output varchar2(100);
v_qry varchar2(150);
--
cursor uc is select username from dba_users order by 1;
begin
  v_out_header := '***User-Role Privilege report***'||chr(10)||'-----------------------------------';
  dbms_output.put_line(v_out_header);
  open uc;
  loop
  fetch uc into v_usr;
  exit when uc%notfound;
  
select 'username: '|| username||' , '||'profile: '||profile into v_output 
from dba_users where username=v_usr;
dbms_output.put_line(v_output);
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '
--||chr(10)
);

v_output := '';
v_qry := 'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in
(select role from dba_roles) and grantee ='||'''' ||v_usr||'''' ;
execute immediate v_qry into v_output;
--
dbms_output.put_line(v_output);
dbms_output.put_line('------------------------------'||chr(10));
end loop;
close uc;
exception
  when no_data_found
  then dbms_output.put_line('no_data_found');
end;

The output is as follows:

Code: [Select all] [Show/ hide]

***User-Role Privilege report***
-----------------------------------
username: ANDREY , profile: DEFAULT
SYSTEM privileges granted directly to the user(not through ROLE) : 
no_data_found

A problem I am encountering is that for some users I have no direct privileges that are not granted through roles,
And when I have the expression v_qry (which is basically "'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in
(select role from dba_roles) and grantee ='||'''' ||v_usr||''''") not initialized with values because the select statement retrieved 0 results,
I have the process interfered by the no_data_found error/exception.

Questions:
Any ideas on how I can preferrably simply, avoid/overcome my problem?
Some way to make the loop go on in spite of no data found? maybe something similar to NVL?

Thanks in advance.

Regards,
Andrey

Edit: added brackets to the topic ("No data found"....)

[Updated on: Mon, 01 July 2013 04:23]

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #588995 is a reply to message #588994]

Mon, 01 July 2013 04:24

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Enclose the offending code into its own BEGIN-EXCEPTION-END block and handle the exception there. Here's an example:

Code: [Select all] [Show/ hide]

declare
  blabla
begin
  loop
    begin              --> add this
      select that returns no_data_found
    exception
      when no_data_found then null;
    end;
  end loop;
end;

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #588996 is a reply to message #588995]

Mon, 01 July 2013 04:49

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

You don't need dynamic sql for this

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #588997 is a reply to message #588996]

Mon, 01 July 2013 04:55

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Also the query that's throwing no_data_found can return multiple rows - which will give the too_many_rows exception the way you've coded it.
Use a for loop instead and then you don't need to worry about either error.

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589009 is a reply to message #588997]

Mon, 01 July 2013 07:23

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

cookiemonster wrote on Mon, 01 July 2013 12:49

You don't need dynamic sql for this

cookiemonster wrote on Mon, 01 July 2013 12:55

Also the query that's throwing no_data_found can return multiple rows - which will give the too_many_rows exception the way you've coded it.
Use a for loop instead and then you don't need to worry about either error.

Thanks cookiemonster & littlefoot for your replies.

How can I use a for loop and avoid dynamic SQL for displaying the multiple privileges for each user?
I've given it some thought but couldn't think of a way... What do I use as the range for the "for" loop?

Regards,
Andrey

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589014 is a reply to message #589009]

Mon, 01 July 2013 07:28

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Andrey_R wrote on Mon, 01 July 2013 13:23

How can I use a for loop and avoid dynamic SQL for displaying the multiple privileges for each user?

Take the query you have - remove execute immediate and extraneous quotes. There's nothing remotely dynamic about that query.

Andrey_R wrote on Mon, 01 July 2013 13:23

What do I use as the range for the "for" loop?

Range? You realise that you can use for loops with select statments? If not I suggest a read of the documentation.

Code: [Select all] [Show/ hide]

FOR rec IN (select grantee ||','|| privilege as output
            from DBA_SYS_PRIVS 
            where grantee not in (select role from dba_roles) 
            and grantee = v_usr) LOOP

It really is that simple.

[EDIT: removed some quotes]

[Updated on: Mon, 01 July 2013 07:29]

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589015 is a reply to message #589009]

Mon, 01 July 2013 07:30

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Think a little bit, you don't need any PL/SQL at all, just one SQL statement.

Regards
Michel

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589040 is a reply to message #589015]

Mon, 01 July 2013 10:28

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

I've amended my code to this:

Code: [Select all] [Show/ hide]

declare
v_usr varchar2(30);
v_out_header varchar2(100);
v_output varchar2(100);
v_qry varchar2(150);
--
cursor uc is select username from dba_users order by 1;
begin
  v_out_header := '***User-Role Privilege report***'||chr(10)||'-----------------------------------';
  dbms_output.put_line(v_out_header);
  open uc;
  loop
  fetch uc into v_usr;
  exit when uc%notfound;
  
select 'username: '|| username||' , '||'profile: '||profile into v_output 
from dba_users where username=v_usr;
dbms_output.put_line(v_output);
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '
--||chr(10)
);

v_output := '';
begin
for grantee in (select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in
(select role from dba_roles) and grantee =v_usr) loop
--
--
dbms_output.put_line(v_output);
end loop;
end;
--
dbms_output.put_line(v_output);
dbms_output.put_line('------------------------------'||chr(10));
end loop;
close uc;
exception
  when no_data_found
  then dbms_output.put_line('no_data_found');
end;

However, it is failing because v_output is not being initialized with anything in this part.

I can't seem to understand how to catch the contents of my user-relevant(defined by current v_usr value, by the outer loop) privileges.

Can you give me another hint on a technique to do it?

Thanks & Best Regards,
Andrey

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589042 is a reply to message #589040]

Mon, 01 July 2013 11:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Can you give me another hint on a technique to do it?

Michel Cadot wrote on Mon, 01 July 2013 14:30

Think a little bit, you don't need any PL/SQL at all, just one SQL statement.

Regards
Michel

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589055 is a reply to message #589042]

Mon, 01 July 2013 13:26

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

To be fair Michel - he's going to struggle to get the format he wants with pure sql, and he should learn where he's going wrong even if sql is better.

@Andrey_R - You really should read the documentation on cursor for loops. If you did then you would know how to access the for loop record variables. This is basic PL/SQL.

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589060 is a reply to message #589055]

Mon, 01 July 2013 14:17

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I admit but he could first get the data, then we can provide the format. Smile

Smile

Regards
Michel

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589102 is a reply to message #589060]

Tue, 02 July 2013 04:20

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi Michel & cookiemonster,
I've read a little about how it's done in PL/SQL and I've amended my code accordingly to this:

Code: [Select all] [Show/ hide]

declare
v_usr varchar2(30);
v_out_header varchar2(200);
v_output varchar2(100);
v_qry varchar2(150);
--
cursor uc is select username from dba_users where username not in ('SYS','SYSTEM') order by 1;
begin
  v_out_header := ' -----------------------------------'||chr(10)||'/****User-Role Privilege report****/'||chr(10)||'-----------------------------------'||chr(10);
  dbms_output.put_line(v_out_header);
  open uc;
  loop
  fetch uc into v_usr;
  exit when uc%notfound;
  
select chr(10)||'username: '|| username||' , '||'profile: '||profile into v_output 
from dba_users where username=v_usr;
dbms_output.put_line(v_output);
--direct system privs 
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '||chr(10)||'-------------------------------------------------------------------');
--  
begin
for item  in (select privilege direct_sys_priv from DBA_SYS_PRIVS where  grantee =v_usr) loop
--
--
dbms_output.put_line(item.direct_sys_priv);
end loop;
end;
--
--role system privs 
dbms_output.put_line(chr(10)||'SYSTEM privileges granted through ROLES : '||chr(10)||'-------------------------------------------------------------------');

  
begin
  for item  in (select 'Role: "'||grantee||'", Privilege: ' ||privilege role_sys_priv from DBA_SYS_PRIVS where grantee in
(select granted_role from DBA_ROLE_PRIVS where grantee = v_usr) order by role_sys_priv) loop

--
--
dbms_output.put_line(item.role_sys_priv);
end loop;
end;
--
--
dbms_output.put_line('------------------------------'||chr(10));
end loop;
close uc;
exception
  when no_data_found
  then dbms_output.put_line('no_data_found');
end;

It seems to work fine now.
I will do the same for non system privs.
I also think that there is a possibility for roles to be granted to roles,
And I need to think how to represent it in my code.

Now, regarding the possibility of using only SQL - is it possible to loop in a SQL query at all (without begin..loop...end loop....end...) ?
I didn't find documentation for this anywhere, only for PL/SQL...

Regards,
Andrey

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589103 is a reply to message #589102]

Tue, 02 July 2013 04:38

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The SQL query with join IS a loop; remember NESTED LOOPS operation (among others).

Regards
Michel

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589109 is a reply to message #589103]

Tue, 02 July 2013 05:53

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can shorten your code quite a bit - the cursor loop at the start should be a for loop.
The subsequent select does not need to exist - the cursor can select profile.
You don't need any of the variables.
You don't need the exception handler.
You don't need to wrap each for loop in a begin end - it's pointless unless you're going to add an exception handler.

Report message to a moderator

Re: No data found stops the loop action inside PL/SQL block [message #589119 is a reply to message #589109]

Tue, 02 July 2013 07:44

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

cookiemonster wrote on Tue, 02 July 2013 13:53

You can shorten your code quite a bit - the cursor loop at the start should be a for loop.
The subsequent select does not need to exist - the cursor can select profile.
You don't need any of the variables.
You don't need the exception handler.
You don't need to wrap each for loop in a begin end - it's pointless unless you're going to add an exception handler.

Thanks for the tip!

This is my code so far:

Code: [Select all] [Show/ hide]

--
begin
    dbms_output.put_line( ' -----------------------------------'||chr(10)||'/****User-Role Privilege report****/'||chr(10)||'-----------------------------------'||chr(10));
  
  for item_uname in (select username,chr(10)||'username: '|| username||' , '||'profile: '||profile user_prof_info from dba_users where username not in ('SYS','SYSTEM') order by 1)
    loop --outer loop start
dbms_output.put_line(item_uname.user_prof_info);
--
--
--direct system privs 
dbms_output.put_line('SYSTEM privileges granted directly to the user(not through ROLE) : '||chr(10)||'-------------------------------------------------------------------');
--
for item_drct_sys_priv  in (select privilege direct_sys_priv from DBA_SYS_PRIVS where  grantee =item_uname.username) 
  loop --inner loop start1
--
--
dbms_output.put_line(item_drct_sys_priv.direct_sys_priv);
end loop;--inner loop end
--
--role system privs 
dbms_output.put_line(chr(10)||'SYSTEM privileges granted through ROLES : '||chr(10)||'-------------------------------------------------------------------');
--
for item_role_sys_priv  in (select 'Role: "'||grantee||'", Privilege: ' ||privilege role_sys_priv from DBA_SYS_PRIVS where grantee in
(select granted_role from DBA_ROLE_PRIVS where grantee = item_uname.username) order by role_sys_priv) 
loop --inner loop start2
dbms_output.put_line(item_role_sys_priv.role_sys_priv);
end loop;--inner loop end2
dbms_output.put_line('----------------------------**END OF INFO**-------------------------------'||chr(10));
end loop;--outer loop end
--
end;

Regards,
Andrey

[Updated on: Tue, 02 July 2013 07:44]

Report message to a moderator

Previous Topic:	How to Understand the patterns.
Next Topic:	"WHEN OTHERS breaks the Atomicity of a procedure call" ???

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 04 17:23:12 CDT 2025