Home » SQL & PL/SQL » SQL & PL/SQL » Help required on Select query (Oracle 10g)
icon5.gif  Help required on Select query [message #392843] Thu, 19 March 2009 08:20 Go to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member

Hi All,

I have two tables named for eg: "Emp" and "View_Emp"
In my application, I need to show the column values from "Emp" table.

But the columns to be select in "Emp" are defined in "View_Emp" table based on the user logged on.

There is no mapping or join can be done between these two tables

How can I achieve this.

Any help would be appreciated.


Thanks
Surendran

Re: Help required on Select query [message #392844 is a reply to message #392843] Thu, 19 March 2009 08:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
There is no mapping or join can be done between these two tables



So are you saying that you cannot reference this view_emp 'Table'
Re: Help required on Select query [message #392847 is a reply to message #392843] Thu, 19 March 2009 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Help required on Select query [message #392990 is a reply to message #392843] Thu, 19 March 2009 22:44 Go to previous messageGo to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member

Hi Michel and pablolee
Thanks for the reply.
I apologize for not describing my problem very clear.

Here are the create and insert statements for the two tables I mentined above.

create table Emp (
empno 	number,
name 	varchar2(25),
job		varchar2(25),
Sal 	number(12,2),
Dept 	number(2),
Doj  	date,
Grade 	varchar(3)
)

insert into Emp values(111,'AAA','Programmer',12334.50,'Finance','20-jun-2006','A1');
insert into Emp values(222,'BBB','Analyst',22334.50,'HR','20-jun-2002','A2');
insert into Emp values(333,'CCC','Manager',32334.50,'Admin','20-jun-2001','A5');
insert into Emp values(444,'DDD','Team Leader',32334.50,'Finance','20-jun-2004','A3');
insert into Emp values(555,'EEE','Asst.Manager',23334.50,'ResourceMgmt','20-jun-2005','A4');
insert into Emp values(666,'FFF','Sr.Programmer',18334.50,'Admin','20-jun-2006','A2');

Create table View_Emp (
Login_user varchar2(20),
Login_Dept varchar2(15),
Viewname   varchar2(10),
Columnname varchar2(15),
ReadOnly	varchar2(1)
)

insert into View_Emp values('XYZ','Admin','AdminView','empno','N');
insert into View_Emp values('XYZ','Admin','AdminView','name','Y');
insert into View_Emp values('XYZ','Admin','AdminView','job','Y');
insert into View_Emp values('XYZ','Admin','AdminView','Sal','Y');
insert into View_Emp values('XYZ','Admin','AdminView','Dept','Y');
insert into View_Emp values('XYZ','Admin','AdminView','Doj','Y');
insert into View_Emp values('XYZ','Admin','AdminView','Grade','Y');
insert into View_Emp values('ABC','Finance','FinanceView','empno','N');
insert into View_Emp values('ABC','Finance','FinanceView','name','N');
insert into View_Emp values('ABC','Finance','FinanceView','job','N');
insert into View_Emp values('ABC','Finance','FinanceView','Sal','Y');


Here in the View_Emp table, there are two login user details and the column to be shown when they logon is entered.

I need to select only those 4 columns if "ABC" is logged on and select only those 6 columns if "XYZ" is logged on from 'Emp' table.

I hope this explains better

Thanks
Surendran
Re: Help required on Select query [message #392991 is a reply to message #392843] Thu, 19 March 2009 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The input is getting more complete & acceptable.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

>along with the result you want with these data.

Please use <code tags> & show the expected/desired results
& explain why the results meet your requirements.
Re: Help required on Select query [message #392993 is a reply to message #392843] Thu, 19 March 2009 23:08 Go to previous messageGo to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member

Hi,
Sorry again if I didnt follow the guidelines properly.

This is what my expected result

If XYZ is logged in,
I need to use a
Quote:
select
statement like below
select empno,name,job,sal from emp


If ABC is logged in,
I need to use a
Quote:
select
statement like below
select empno,name,job,sal,Dept,doj from emp


But here I need to get the selectable columns from the View_Emp table.

I hope this helps even better Sad

Thanks
Surend
Re: Help required on Select query [message #393009 is a reply to message #392993] Fri, 20 March 2009 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create your statement in a string and use dbms_sql to execute it.
Of course this is a very poor design that will perform slowly and will not scale up.

Regards
Michel
Re: Help required on Select query [message #393033 is a reply to message #392843] Fri, 20 March 2009 01:39 Go to previous messageGo to next message
sky_lt
Messages: 28
Registered: February 2009
Location: vilnius
Junior Member
Hi,

You can consider using Fine-Grained Access Control

http://stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/apdvcntx.htm#1007183

Sky_lt
Re: Help required on Select query [message #393052 is a reply to message #393033] Fri, 20 March 2009 02:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does FAC also provide means to NOT display columns?
I thought it would only nullify the restricted columns (in column-level VPD)
Re: Help required on Select query [message #393095 is a reply to message #392843] Fri, 20 March 2009 05:38 Go to previous messageGo to next message
sethulogin
Messages: 7
Registered: March 2009
Location: Malaysia
Junior Member

Pls chk the below query is this satisfied your requirement?

SELECT * FROM
(SELECT ROWNUM R1,A.* FROM EMP A),
(SELECT ROWNUM R2,B.* FROM VIEW_EMP B)
WHERE R1(+) = R2


Re: Help required on Select query [message #393107 is a reply to message #393095] Fri, 20 March 2009 06:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Sethulogin - That is a solution to a completely different problem.

The OP wants to restrict the set of columns returned to those in the row returned from View_Emp where login_user = his current user.
Re: Help required on Select query [message #393108 is a reply to message #393095] Fri, 20 March 2009 06:01 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Pls chk the below query is this satisfied your requirement?


Not unless the OP just wants some random rows.

[Updated on: Fri, 20 March 2009 06:01]

Report message to a moderator

Re: Help required on Select query [message #393109 is a reply to message #392990] Fri, 20 March 2009 06:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I would solve this by creating a set of views for each user and only letting them access the data via the views.

If you wanted, you could create the views procedurally from the data in that table, to allow you to easily change the data the users could see.
Re: Help required on Select query [message #393128 is a reply to message #393109] Fri, 20 March 2009 07:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't know what application you use to interact with your users, but I think you should let your UI take care of this. Simply select the complete table and only display the selected columns.
Re: Help required on Select query [message #393246 is a reply to message #393128] Fri, 20 March 2009 16:24 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I would probably go with the multiple views solution, as previously suggested by JRowbottom. It looks like you might only need two such views, one for admin and one for finance, as opposed to one per user. You could then grant select on only the appropriate view to each user and select from the appropriate view depending on the user.

However, you could generate a select statement dynamically as demonstrated below. But, this requires select privileges on both tables, so you would have to restrict access through other means, such as SQL*Plus, so it is not as secure. In the demo below, I had to make some corrections to your code and I changed a table name for convenience. I have also used roles, such as resource for convenience, which are not necessarily recommended or related to the problem.

SCOTT@orcl_11g> create table Tab_Emp (
  2  empno   number,
  3  name    varchar2(10),
  4  job     varchar2(15),
  5  Sal     number(12,2),
  6  Dept    varchar2(15),
  7  Doj     date,
  8  Grade   varchar2(3)
  9  )
 10  /

Table created.

SCOTT@orcl_11g> insert into Tab_Emp values(111,'AAA','Programmer',12334.50,'Finance','20-jun-2006','A1');

1 row created.

SCOTT@orcl_11g> insert into Tab_Emp values(222,'BBB','Analyst',22334.50,'HR','20-jun-2002','A2');

1 row created.

SCOTT@orcl_11g> insert into Tab_Emp values(333,'CCC','Manager',32334.50,'Admin','20-jun-2001','A5');

1 row created.

SCOTT@orcl_11g> insert into Tab_Emp values(444,'DDD','Team Leader',32334.50,'Finance','20-jun-2004','A3');

1 row created.

SCOTT@orcl_11g> insert into Tab_Emp values(555,'EEE','Asst.Manager',23334.50,'ResourceMgmt','20-jun-2005','A4');

1 row created.

SCOTT@orcl_11g> insert into Tab_Emp values(666,'FFF','Sr.Programmer',18334.50,'Admin','20-jun-2006','A2');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> Create table View_Emp (
  2  Login_user varchar2(20),
  3  Login_Dept varchar2(15),
  4  Viewname	varchar2(11),
  5  Columnname varchar2(15),
  6  ReadOnly	     varchar2(1)
  7  )
  8  /

Table created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','empno','N');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','name','Y');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','job','Y');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','Sal','Y');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','Dept','Y');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','Doj','Y');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('XYZ','Admin','AdminView','Grade','Y');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('ABC','Finance','FinanceView','empno','N');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('ABC','Finance','FinanceView','name','N');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('ABC','Finance','FinanceView','job','N');

1 row created.

SCOTT@orcl_11g> insert into View_Emp values('ABC','Finance','FinanceView','Sal','Y');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE USER abc IDENTIFIED BY abc
  2  /

User created.

SCOTT@orcl_11g> CREATE USER xyz IDENTIFIED BY xyz
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT, RESOURCE TO abc, xyz
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT SELECT ON tab_emp TO abc, xyz
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT SELECT ON view_emp TO abc, xyz
  2  /

Grant succeeded.

SCOTT@orcl_11g> CONNECT abc/abc
Connected.
ABC@orcl_11g> 
ABC@orcl_11g> VARIABLE g_emps REFCURSOR
ABC@orcl_11g> SET AUTOPRINT ON
ABC@orcl_11g> DECLARE
  2    v_sql  VARCHAR2 (32767);
  3  BEGIN
  4    v_sql := 'SELECT ';
  5    FOR r IN
  6  	 (SELECT columnname
  7  	  FROM	 scott.view_emp
  8  	  WHERE  login_user = USER)
  9    LOOP
 10  	 v_sql := v_sql || r.columnname || ',';
 11    END LOOP;
 12    v_sql := RTRIM (v_sql, ',') || ' FROM scott.tab_emp';
 13    OPEN :g_emps FOR v_sql;
 14  END;
 15  /

PL/SQL procedure successfully completed.


     EMPNO NAME       JOB                    SAL
---------- ---------- --------------- ----------
       111 AAA        Programmer         12334.5
       222 BBB        Analyst            22334.5
       333 CCC        Manager            32334.5
       444 DDD        Team Leader        32334.5
       555 EEE        Asst.Manager       23334.5
       666 FFF        Sr.Programmer      18334.5

6 rows selected.

ABC@orcl_11g> CONNECT xyz/xyz
Connected.
XYZ@orcl_11g> 
XYZ@orcl_11g> VARIABLE g_emps REFCURSOR
XYZ@orcl_11g> SET AUTOPRINT ON
XYZ@orcl_11g> DECLARE
  2    v_sql  VARCHAR2 (32767);
  3  BEGIN
  4    v_sql := 'SELECT ';
  5    FOR r IN
  6  	 (SELECT columnname
  7  	  FROM	 scott.view_emp
  8  	  WHERE  login_user = USER)
  9    LOOP
 10  	 v_sql := v_sql || r.columnname || ',';
 11    END LOOP;
 12    v_sql := RTRIM (v_sql, ',') || ' FROM scott.tab_emp';
 13    OPEN :g_emps FOR v_sql;
 14  END;
 15  /

PL/SQL procedure successfully completed.


     EMPNO NAME       JOB                    SAL DEPT            DOJ       GRA
---------- ---------- --------------- ---------- --------------- --------- ---
       111 AAA        Programmer         12334.5 Finance         20-JUN-06 A1
       222 BBB        Analyst            22334.5 HR              20-JUN-02 A2
       333 CCC        Manager            32334.5 Admin           20-JUN-01 A5
       444 DDD        Team Leader        32334.5 Finance         20-JUN-04 A3
       555 EEE        Asst.Manager       23334.5 ResourceMgmt    20-JUN-05 A4
       666 FFF        Sr.Programmer      18334.5 Admin           20-JUN-06 A2

6 rows selected.

XYZ@orcl_11g>

Previous Topic: Problem retrieving substring from a CLOB variable
Next Topic: how to import SP from...
Goto Forum:
  


Current Time: Sun Dec 04 10:22:38 CST 2016

Total time taken to generate the page: 0.04023 seconds