OraFAQ Forum: SQL & PL/SQL » Query on PL/SQL logic

Home » SQL & PL/SQL » SQL & PL/SQL » Query on PL/SQL logic

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

Query on PL/SQL logic [message #434178]

Mon, 07 December 2009 10:16

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

Attachment: Procedure.txt
(Size: 2.51KB, Downloaded 933 times)

Report message to a moderator

Re: Query on PL/SQL logic [message #434181 is a reply to message #434178]

Mon, 07 December 2009 10:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I'm pretty sure you posted this question last week.....

Report message to a moderator

Re: Query on PL/SQL logic [message #434184 is a reply to message #434178]

Mon, 07 December 2009 10:31

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

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434243 is a reply to message #434184]

Tue, 08 December 2009 01:55

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434250 is a reply to message #434243]

Tue, 08 December 2009 02:41

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

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434251 is a reply to message #434250]

Tue, 08 December 2009 02:49

astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata

Junior Member

Thanks a lot Michel Smile

Smile

Report message to a moderator

Re: Query on PL/SQL logic [message #434258 is a reply to message #434250]

Tue, 08 December 2009 03:28

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434259 is a reply to message #434258]

Tue, 08 December 2009 03:38

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

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434261 is a reply to message #434259]

Tue, 08 December 2009 03:42

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434262 is a reply to message #434261]

Tue, 08 December 2009 03:44

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

Report message to a moderator

Re: Query on PL/SQL logic [message #434263 is a reply to message #434261]

Tue, 08 December 2009 03:50

ramoradba
Messages: 2457
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

Report message to a moderator

Re: Query on PL/SQL logic [message #434266 is a reply to message #434263]

Tue, 08 December 2009 04:09

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

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

astik.mondal
Messages: 14
Registered: March 2008
Location: Kolkata

Junior Member

Thanks Michel and Sriram. Now it is working fine.

Report message to a moderator

Re: Query on PL/SQL logic [message #434272 is a reply to message #434263]

Tue, 08 December 2009 04:58

Frank
Messages: 7901
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.

Report message to a moderator

Re: Query on PL/SQL logic [message #434274 is a reply to message #434272]

Tue, 08 December 2009 05:28

ramoradba
Messages: 2457
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

Frank
Messages: 7901
Registered: March 2000

Senior Member

What's wrong, is that you define a type called ....integer... that stores varchar2's

Report message to a moderator

Re: Query on PL/SQL logic [message #434277 is a reply to message #434275]

Tue, 08 December 2009 05:36

ramoradba
Messages: 2457
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

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.

Report message to a moderator

Previous Topic:	SQL Query find out the bottom two date against each a_id
Next Topic:	best way to optimise Delete syntax

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 14:34:54 CST 2024