?
Solved

how to un chnged order for other users?

Posted on 2014-04-03
6
Medium Priority
?
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 2000 total points
ID: 39974321
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
ID: 39974338
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
ID: 39974412
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
ID: 39974430
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39976264
>>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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

777 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