Solved

how to un chnged order for other users?

Posted on 2014-04-03
6
294 Views
Last Modified: 2014-04-07
HI EXPERT ,
BELOW is MY procedure

create or replace procedure MS_SRA_USER_INFO_REORDER(P_USER_ID number
    )
as

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
-- for non IST role
update  SI_USER_INFOCENTER_T outer set (default_infocenter_flag,display_order) = (
                select case when myrn=1 then 'Y' else 'N' end flag, myrn from
                (
                select user_id, infocenter_name, default_infocenter_flag,
                                row_number() over(partition by user_id order by
                case INFOCENTER_NAME
                when 'Landing Page'     then 1
                WHEN 'Site Visits'             THEN 2
                WHEN 'CAP'                                        THEN 3
                WHEN 'Briefcase'             THEN 4
                WHEN 'Reports'                                THEN 5
                when 'Admin'                   then 6
                when 'Reassignment' THEN 7
                Else Display_Order
                END ) myrn
                From SI_USER_INFOCENTER_T
               
               
  ) Inner
Where Inner.User_Id=Outer.User_Id
and inner.INFOCENTER_NAME=outer.INFOCENTER_NAME
)
WHERE NOT EXISTS (SELECT
      1
  FROM MS_SRA_USER_ORG_ROLE_ACT_V
  WHERE
   ORG_ENTITY_ID =
    ( select ORG_ENTITY_ID from SI_ORG_ENTITIES_T where ORG_ENTITY_NAME='IST'
    )
   
    and USER_ID=outer.USER_ID
    and USER_ID =P_USER_ID );
    end MS_SRA_USER_INFO_REORDER





select INFOCENTER_NAME,DISPLAY_ORDER from SI_USER_INFOCENTER_T where USER_ID=100295;
HISTORICAL data UPLOAD      7
REASSIGNMENT                7
CAP                          7
REPORTS                      7
SITE VISITS                  7
admin                        7
QUESTIONS                    7
PROTOCOLS                    7


execute MS_SRA_USER_INFO_REORDER(100296);--one user

select INFOCENTER_NAME,DISPLAY_ORDER from SI_USER_INFOCENTER_T where USER_ID=100295;--other user

HISTORICAL data UPLOAD         6
REASSIGNMENT                  8
CAP                            2
REPORTS                       3
SITE VISITS                    1
admin                          4
QUESTIONS                      7
Protocols                      5

BUT MY REQURIREMENT is only  100296 user DISPLAY_order WILL BE reset not for OTHER USERS

SO MY EXPECTED OUT for 100295 user SHOULD BE BELOW like THIS

HISTORICAL data UPLOAD      7
REASSIGNMENT                7
CAP                          7
REPORTS                      7
SITE VISITS                  7
admin                        7
QUESTIONS                    7
PROTOCOLS                    7



Thanks
Thomos
0
Comment
Question by:deve_thomos
6 Comments
 
LVL 10

Accepted Solution

by:
HuaMinChen earned 500 total points
Comment Utility
You can instead use Dynamic sql to achieve this.

Here are few examples.
CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER, 
                             emp_column VARCHAR2, amount NUMBER) IS
   v_column VARCHAR2(30);
   sql_stmt  VARCHAR2(200);
BEGIN
-- determine if a valid column name has been given as input
  SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;
  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' 
               || v_column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt USING amount, column_value;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column 
                        || ' = ' || column_value);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column);
END raise_emp_salary;
/

DECLARE
   plsql_block       VARCHAR2(500);
BEGIN
-- note the semi-colons (;) inside the quotes '...'
  plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';
  EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;
  EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'
      USING 112, 'EMPLOYEE_ID', 10;
END;
/

DECLARE
   sql_stmt          VARCHAR2(200);
   v_column          VARCHAR2(30) := 'DEPARTMENT_ID';
   dept_id           NUMBER(4) := 46;
   dept_name         VARCHAR2(30) := 'Special Projects';
   mgr_id            NUMBER(6) := 200;
   loc_id            NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
  sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
  EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id;
  EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
      USING dept_id;
  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
  EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/

Open in new window

0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
you mean even after the procedure call its not displaying the desired result?

if this is the case, then its because of the keyword PRAGMA AUTONOMOUS_TRANSACTION;

you have to place a commit in the procedure so as to view the updated output.

If you are on something else then do use dynamic sql as above expert suggested
0
 

Author Comment

by:deve_thomos
Comment Utility
Hello wasimibm,
even if i place commit also for user which one i am passing in to procedure it is working fine

but for other users the display order is breaking . please see my code below that for user 100295 display order is breaking . please see my select command you can under stand.
i run that procedure for user 100296 the dispaly order is comimg properly but  the order is breaking for other users  like 100295 previously it was 7.7.... like but after executing 100296 the order is breaking for 100295 user.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
correct me if am wrong...

there is one table  SI_USER_INFOCENTER_T which has some values, this table gets updated when you run this procedure MS_SRA_USER_INFO_REORDER,

your concern, when you pass a particular user id 100296, values are displayed as expected, but when you pass another particular user id 100295 , the values are not in desired order .. is it the scenario?

for this to understand better and to provide you a solution, you need to show us the whole code of the procedure and some sample data in existing tables for specific user ids
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>SO MY EXPECTED OUT for 100295 user SHOULD BE BELOW like THIS

What should all the display orders be '7' for 100295?

A '7' is for:
when 'Reassignment' THEN 7

I agree that some table structures with sample data would help.  Then we could set up a test case and run actual code on our systems.
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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

772 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

15 Experts available now in Live!

Get 1:1 Help Now