Home » SQL & PL/SQL » SQL & PL/SQL » Query on PL/SQL logic
Query on PL/SQL logic [message #434178] Mon, 07 December 2009 10:16 Go to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member
Hi All,

I have written a procedure and passing a user name and multiple roles through parameters. Role parameter is varray type. Now we are fetching all the existing roles for the user using the below query:

select role_app.role_name
from dba_role_privs role_privs, role_app_tab role_app
where role_privs.granted_role = role_app.role_name
and role_privs.grantee = <user name>
and role_app.application_name = <application_name>

Now we need to implement logic where we will compare the new roles( passing as a parameter, multiple rols(array))with the existing roles (fetched by above query(loop)).
If roles are changed, means if new roles doesn't match with the existing roles, we need to revoke all the existing roles and assign the new roles(passing by parameter) to the user.

Can you please let me know how I can implement this logic?

Attaching the procedure which I have written.


Thanks,
Astik
Re: Query on PL/SQL logic [message #434181 is a reply to message #434178] Mon, 07 December 2009 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Mon, 07 December 2009 10:25]

Report message to a moderator

Re: Query on PL/SQL logic [message #434182 is a reply to message #434178] Mon, 07 December 2009 10:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure you posted this question last week.....
Re: Query on PL/SQL logic [message #434184 is a reply to message #434178] Mon, 07 December 2009 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
with the existing roles (fetched by above query(loop)).

Do not loop, bulk collect into an array and then use multiset operators to find the differences.

Regards
Michel
Re: Query on PL/SQL logic [message #434243 is a reply to message #434184] Tue, 08 December 2009 01:55 Go to previous messageGo to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member

Thanks Michel for your answer.
Can I use 'Multiset Intersection' to find the differences?
Will you please give some hints how I can find the differences.


Thanks,
Astik
Re: Query on PL/SQL logic [message #434250 is a reply to message #434243] Tue, 08 December 2009 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace type integer_tab is table of integer;
  2  /

Type created.

SQL> declare
  2    v1 integer_tab := integer_tab(1,2,3,4,5);
  3    v2 integer_tab := integer_tab(1,3,5);
  4    v3 integer_tab;
  5  begin
  6    v3 := v1 multiset except v2;
  7    for i in 1..v3.count() loop
  8      dbms_output.put_line(v3(i));
  9    end loop;
 10  end;
 11  /
2
4

PL/SQL procedure successfully completed.

Regards
Michel
Re: Query on PL/SQL logic [message #434251 is a reply to message #434250] Tue, 08 December 2009 02:49 Go to previous messageGo to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member
Thanks a lot Michel Smile
Re: Query on PL/SQL logic [message #434258 is a reply to message #434250] Tue, 08 December 2009 03:28 Go to previous messageGo to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member
Hi Michel,

As per the requirement I have changed the code below:

CREATE TYPE roleArrayType AS VARRAY(100) OF VARCHAR2(100);
/

declare
v1 rolearraytype := rolearraytype('a','b','c','d','e');
v2 rolearraytype := rolearraytype('a','c','e');
v3 rolearraytype;
begin
v3 := v2 multiset except v1;
for i in v3.FIRST .. v3.LAST
loop
dbms_output.put_line(v3(i));
end loop;
end;

But It is not working. I am getting the below error:
ORA-06550: line 6, column 7: PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL' ORA-06550: line 6, column 1: PL/SQL: Statement ignored.

Any input on this would be highly appreciated.

Thanks,
Astik

Re: Query on PL/SQL logic [message #434259 is a reply to message #434258] Tue, 08 December 2009 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a TABLE not a VARRAY.

Note that there is no in v2 that are not in v1, so your PL/SQL will get another error. I let you find and fix it.

Regards
Michel
Re: Query on PL/SQL logic [message #434261 is a reply to message #434259] Tue, 08 December 2009 03:42 Go to previous messageGo to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member

Thanks Michel. But I have to use varray, because I am fetching roles which are a char datatype.


Thanks,
Astik
Re: Query on PL/SQL logic [message #434262 is a reply to message #434261] Tue, 08 December 2009 03:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That statement makes no sense. VARRAY are not a special type of collection that can handle varchars.

Just replace Michels table with:
create or replace type varchar2_tab is table of varchar2(100);
/
(alter the data length according to your requirements).
Re: Query on PL/SQL logic [message #434263 is a reply to message #434261] Tue, 08 December 2009 03:50 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel And Jrowbottom already suggested....

  1* create or replace type integer_tab2 is table of varchar2(1000);
SQL> /

Type created.

SQL> ed
Wrote file afiedt.buf

  1* create or replace type integer_tab2 is table of varchar2(1000);
SQL> ed
Wrote file afiedt.buf

  1  declare
  2        v1 integer_tab2 := integer_tab2('Sriram','Oracle','Dba','Asktom','Michel');
  3        v2 integer_tab2 := integer_tab2('Oracle','Asktom','Michel');
  4        v3 integer_tab2;
  5      begin
  6        v3 := v1; --multiset except v2;
  7        for i in 1..v3.count() loop
  8          dbms_output.put_line(v3(i));
  9        end loop;
 10*    end;
SQL> /
Sriram
Oracle
Dba
Asktom
Michel

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2        v1 integer_tab2 := integer_tab2('Sriram','Oracle','Dba','Asktom','Michel');
  3        v2 integer_tab2 := integer_tab2('Oracle','Asktom','Michel');
  4        v3 integer_tab2;
  5      begin
  6        v3 := v1 multiset except v2;
  7        for i in 1..v3.count() loop
  8          dbms_output.put_line(v3(i));
  9        end loop;
 10*    end;
SQL> /
Sriram
Dba

PL/SQL procedure successfully completed.

SQL>



sriram Smile
Re: Query on PL/SQL logic [message #434266 is a reply to message #434263] Tue, 08 December 2009 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel And Jrowbottom already suggested....

So why do you repeat it? Let OP find it by himself he will then learn much more.

Regards
Michel

[Updated on: Tue, 08 December 2009 05:56]

Report message to a moderator

Re: Query on PL/SQL logic [message #434271 is a reply to message #434266] Tue, 08 December 2009 04:30 Go to previous messageGo to next message
astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata
Junior Member
Thanks Michel and Sriram. Now it is working fine.
Re: Query on PL/SQL logic [message #434272 is a reply to message #434263] Tue, 08 December 2009 04:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramoradba wrote on Tue, 08 December 2009 10:50
  1* create or replace type integer_tab2 is table of varchar2(1000);


Failed review process. Please go back to drawing board.
Re: Query on PL/SQL logic [message #434274 is a reply to message #434272] Tue, 08 December 2009 05:28 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Insted of typing everything again...I simply modified the Command As i am using Cmd prompt....Whats wrong in that ..?

sriram

[Updated on: Tue, 08 December 2009 05:29]

Report message to a moderator

Re: Query on PL/SQL logic [message #434275 is a reply to message #434274] Tue, 08 December 2009 05:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's wrong, is that you define a type called ....integer... that stores varchar2's
Re: Query on PL/SQL logic [message #434277 is a reply to message #434275] Tue, 08 December 2009 05:36 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Already said and included that its already suggested by Michel ....For the same thing i posted and used Michel type with different name Integer_tab2(Michel`s is integer_tab) i didn`t say like this is my own any where? then what ?..And i agree with Michel`s post,As he warned me for repeating post.Hope i am clear ! Then whats wrong here ?

sriram

[Updated on: Tue, 08 December 2009 05:37]

Report message to a moderator

Re: Query on PL/SQL logic [message #434283 is a reply to message #434277] Tue, 08 December 2009 06:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Michel's point is that it is poor practice to create a table that holds varchar2s and is called Integer_tab.

It will work, but it is very poor practice.
Previous Topic: SQL Query find out the bottom two date against each a_id
Next Topic: best way to optimise Delete syntax
Goto Forum:
  


Current Time: Sun Sep 25 02:41:52 CDT 2016

Total time taken to generate the page: 0.10097 seconds