Solved

Function in Oracle

Posted on 2013-11-07
12
513 Views
Last Modified: 2013-11-07
Can the below function optimized ?

CREATE OR REPLACE FUNCTION GET_ROLE_NAME(P_USER_NAME VARCHAR2)
RETURN VARCHAR2
IS
V_ROLE_NAME VARCHAR2(80);
L_RESULT VARCHAR2(4000);
CURSOR CUR_ROLE_NAME IS
select 
 distinct  SAT.ACTIVITY_SHORT_NAME as sn
FROM si_users_t U,
  SI_USER_ORG_ROLES UOR,
  SI_ORG_ROLES SOR,
  SI_ROLES_T R,
  SI_ROLE_ACTIVITIES SRA,
  si_activities_t sat
WHERE U.USER_ID     = UOR.USER_ID
AND UOR.ORG_ROLE_ID = SOR.ORG_ROLE_ID
AND SOR.ROLE_ID     = R.ROLE_ID
AND SRA.ROLE_ID     = R.ROLE_ID
and SAT.ACTIVITY_ID = SRA.ACTIVITY_ID
and SAT.ACTIVITY_SHORT_NAME  in
(
'SRA - Auditor',
'SRA - TPA Auditor',
'SRA - Lead',
'SRA - Verification Lead',
'SRA - Facility Contact'
)
AND U.USER_NAME     = P_USER_NAME;
--order by SAT.ACTIVITY_SHORT_NAME);
BEGIN
FOR I IN CUR_ROLE_NAME
LOOP
IF I.SN='SRA - Facility Contact' THEN
       L_RESULT:='Facility Contact';
       ELSE 
         IF I.SN='SRA - Lead'or i.sn='SRA - Verification Lead' 
         THEN
         L_RESULT:='Lead';
         ELSE
          IF I.SN='SRA - Verification Lead'AND 
          I.SN!='Facility Contact' AND I.SN!='SRA - Lead'
          THEN
          L_RESULT:='Verification Lead';
         
         END IF;
        END IF;
        END IF;
         END LOOP;
         RETURN L_RESULT;
         END;

Open in new window


Code to run like :

SQL> SELECT GET_ROLE_NAME('<<user name >>') FROM DUAL;
0
Comment
Question by:Swadhin Ray
  • 7
  • 4
12 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39631102
don't open a cursor to only read one result.

if it's possible your cursor will return more than one row, your current incarnation will effectively act as a random row selector because only the results of the last row will be returned but you have no constraints on which row will be last.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39631128
Looks like you had an order by originally, that will address the problem of which row will be last; but you commented it out.
Since you only return the last row, there is no point in processing all of the other rows, so the easiest solution is to simply sort in reverse order and pick the FIRST row.

Note, your original code doesn't have a condition for 'SRA - TPA Auditor'  so the function will return NULL in that case.
This code below does the same but without all the IFs and unnecessary looping.  Using this method you can also eliminate the need to use a DISTINCT.


CREATE OR REPLACE FUNCTION get_role_name(p_user_name VARCHAR2)
    RETURN VARCHAR2
IS
    l_result VARCHAR2(4000);
BEGIN
    SELECT CASE
               WHEN sn = 'SRA - Facility Contact' THEN 'Facility Contact'
               WHEN sn = 'SRA - Lead' OR sn = 'SRA - Verification Lead' THEN 'Lead'
               WHEN sn = 'SRA - Verification Lead' THEN 'Verification Lead'
           END
      INTO l_result
      FROM (  SELECT sat.activity_short_name AS sn
                FROM si_users_t u,
                     si_user_org_roles uor,
                     si_org_roles sor,
                     si_roles_t r,
                     si_role_activities sra,
                     si_activities_t sat
               WHERE u.user_id = uor.user_id
                 AND uor.org_role_id = sor.org_role_id
                 AND sor.role_id = r.role_id
                 AND sra.role_id = r.role_id
                 AND sat.activity_id = sra.activity_id
                 AND sat.activity_short_name IN ('SRA - Auditor',
                                                 'SRA - TPA Auditor',
                                                 'SRA - Lead',
                                                 'SRA - Verification Lead',
                                                 'SRA - Facility Contact')
                 AND u.user_name = p_user_name
            ORDER BY sat.activity_short_name DESC)
     WHERE ROWNUM = 1;

    RETURN l_result;
END;
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39631225
Actually when we send one username it can return multiple rows like any activity in my IN clause within the select query.

So one user can have multiple activities but I want to display with certain logic like my function should return only one activity as output  :

1) If the result of the SQL returns activities along with 'SRA - Facility Contact' then return only one result i.e. Facility Contact.

2) If the SQL returns activities  'SRA - Lead' OR sn = 'SRA - Verification Lead'  but not having 'SRA - Facility Contact' then return  ''Lead''

3) If it returns  'SRA - Verification Lead' but not  'SRA - Lead' or 'SRA - Facility Contact' then return 'Verification Lead'
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39631327
Functions by definition return exactly one item.  They cannot return multiple items.  You could however concatenate multiple, comma-separated values inside a function into a single, large variable, then return that one variable.  Or, depending on which program/application you call this function from you may be able to change the function to return a ref cursor, or an array, then pass one of these objects back to your calling program as the "return" value from this function.  Either of those objects could actually contain multiple values.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39631397
you need to do collection membership not single value compares

also note your rules conflict with each other


if you have two rows...

 'SRA - Verification Lead'   and  'SRA - Lead'  then rule 2 says you'll return 'Lead' so you'll never get to rule 3 which says you shouldn't

what are the proper rules for 2 and 3 to work together ?




and, you still don't have any rules for your Auditor names,  so the function will return NULL  is that what you expect?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39631431
Ok ... there are other rules for an example I shared 3 of them ..

If we make the In clause to the below:

                                                ('SRA - Lead',
                                                 'SRA - Verification Lead',
                                                 'SRA - Facility Contact')

Then no matters what i have to go with if rule 1 pass then show it . and if rule 1 fail then go to rule 2 .. similarly ..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39631452
you misunderstood

rule 2 in my example doesn't fail, it succeeds

but rule 3 has a contradictory rule, so it doesn't make sense. It's not that rule 3 will fail, it simply doesn't pertain to a logical condition

In other words,   rule 3 will NEVER apply to anything, you could simply delete it and it would have no effect on your results
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39631468
no, an "IN clause" is not what you're looking for
MEMBER OF "a collection" is what you want

I suppose you could do it with multiple queries and sub queries on  IN and NOT IN conditions but that's a lot more complicated.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39631498
I have the function written already except for a consistent set of rules.

Just post those and I can put them into the function and post it.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39631684
Here is the logic:

The below are the activities which one user can have one or multiple:

'SRA - Auditor',
'SRA - TPA Auditor',
'SRA - Lead',
'SRA - Verification Lead',
'SRA - Facility Contact'

Here are the rules which need to be done sequentially :

--1) Check if the user has SRA - Facility Contact activity then display 'Facility Contact'

-- 2) If the user have 'SRA - Verification Lead'  or 'SRA - Lead'  but should not have 'Facility Contact'  then display 'Lead'

-- 3) If the user have ''SRA - Verification Lead'   but  not having 'SRA - Lead'   or  SRA - Facility Contact  then display 'Verification Lead'

--4) if the user having SRA - Auditor but not FC,VL,L, TPA Auditor ..  activity then go to visit_setup table and check if the user present in onsite_lead column for status with 'active' then display 'Onsite lead' 

--5) if 4th rule fails then display 'Auditor'

--6) if the user having 'SRA - TPA Auditor' but not FC,VL,L,'SRA - Auditor ' activity then go to visit_setup table and check if the user present in onsite_lead column for status with 'active' then display 'Onsite lead' 

--7) if 6th rule fails then display ''Auditor''

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39631782
look at your rules 2 and 3, they don't make sense

First,  you have no value "Facility Contact"  so  the "should not have" condition for rule 2 doesn't apply.
Let's assume that's a mistake and it should have said "SRA - Facility Contact"


Let's go super simple.

user has exactly one row

"SRA - Verification Lead"

Rule 1 fails
Rule 2 succeeds  - done
Rule 3 (or others) are never checked.


Now, let's go more complicated

User has 2 rows

"SRA - Verification Lead"
"SRA - Facility Contact"

Rule 1 succeeds - done
Rule 2 (or others) never gets checked


User has 2 rows

"SRA - Verification Lead"
"SRA - Lead"

Rule 1 fails
Rule 2 succeeds - done
Rule 3 (or others)  never gets checked


Rule 3 has no conditions where it could succeed.
Either it won't be reached, or it will be reached and fail.

Lets' break down rule 3

The first condition:   If the user have ''SRA - Verification Lead'   if that's true then rule 2 applies first,
The second condition:  but  not having 'SRA - Lead' - this might as well be deleted.  If it's true or not true is irrelevant, because either way Rule 2 would apply first
The third condition: not having  "SRA - Facility Contact" - this is the same as rule 2, so if it's true, then Rule 2 would have already caught it, if it's not true, then rule 3 will fail for the same reason as rule 2.  Also this condition isn't needed in rule 2 or 3 because it's implicit in the fact that you got past rule 1

Also note,  Rules 6 and 7 are never going to be used either because rule 5 is a "catch all".
If you make it all the way to 4 and it fails, then 5 gives you an answer with no conditions.  So you're done when you reach 5.


I'm going to be offline for awhile
Here's the function with the first 3 rules in place.
I don't have your other tables so 4 can't be implemented here.
5,6,7 are probably not correct so I left them out too.

In any case, this has the framework of what you need.
Now it's just a matter of putting the corresponding rules that work in for each WHEN statement


CREATE OR REPLACE FUNCTION get_role_name(p_user_name VARCHAR2)
    RETURN VARCHAR2
IS
    l_result VARCHAR2(4000);
BEGIN
    SELECT CASE
               WHEN 'SRA - Facility Contact' MEMBER OF namestab
               THEN
                   'Facility Contact'
               WHEN 'SRA - Lead' MEMBER OF namestab OR 'SRA - Verification Lead' MEMBER OF namestab
               THEN
                   'Lead'
               WHEN 'SRA - Verification Lead' MEMBER OF namestab
                AND 'Facility Contact' NOT MEMBER OF namestab
                AND 'SRA - Lead' NOT MEMBER OF namestab
               THEN
                   'Verification Lead'
           END
      INTO l_result
      FROM (SELECT CAST(COLLECT(sn) AS SYS.ora_mining_varchar2_nt) AS namestab
              FROM (SELECT sat.activity_short_name AS sn
                      FROM si_users_t u,
                           si_user_org_roles uor,
                           si_org_roles sor,
                           si_roles_t r,
                           si_role_activities sra,
                           si_activities_t sat
                     WHERE u.user_id = uor.user_id
                       AND uor.org_role_id = sor.org_role_id
                       AND sor.role_id = r.role_id
                       AND sra.role_id = r.role_id
                       AND sat.activity_id = sra.activity_id
                       AND sat.activity_short_name IN ('SRA - Auditor',
                                                       'SRA - TPA Auditor',
                                                       'SRA - Lead',
                                                       'SRA - Verification Lead',
                                                       'SRA - Facility Contact')
                       AND u.user_name = p_user_name));

    RETURN l_result;
END;

Open in new window

0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39631857
thanks a lot ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now