0

I want to make a view in Oracle in which I am required to give student_ID as parameter and it will extract student_name from student table. I have no experience in views and no experience with Oracle.

Kate Gregory
  • 18,808
  • 8
  • 56
  • 85
Atif Imtiaz
  • 215
  • 2
  • 4
  • 24
  • Read this: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF01504 – Art Mar 22 '13 at 12:27
  • 2
    Views cannot be parameterised in the sense that I think you mean. If you want to pass specific arguments to a query, you would have to rely on Oracle's programmability: For example, your view could reference functions in a package, which return some value and can be set through the package's interface... See http://stackoverflow.com/questions/9024696 – Xophmeister Mar 22 '13 at 12:38
  • @Xophmeister with parameters, i meant the arguments that we can pass, just like we pass arguments to stored procedures. isn't that possible in oracle? – Atif Imtiaz Mar 22 '13 at 13:05
  • 1
    @AtifImtiaz Nope, not directly. – Xophmeister Mar 22 '13 at 14:03

2 Answers2

3

Assuming a table definition like this:

CREATE TABLE STUDENT (
  student_id int,
  student_name varchar2(100),
  student_address varchar2(255),
  student_email varchar2(100)
);

Create a view with just id and name fields:

CREATE VIEW VSTUDENT AS
SELECT student_id, student_name
FROM STUDENT;

You can then:

SELECT student_name
FROM VSTUDENT
WHERE student_id=123;

Creating the view with only the student_id (query field) and student_name (required field) protects all the other student data from those who do not have access permissions.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Don
  • 2,317
  • 2
  • 16
  • 16
1
CREATE VIEW view_name AS 
  SELECT student_name, student_grades, more columns...
   FROM student_table
  WHERE student_id = 20
 /
Art
  • 5,616
  • 1
  • 20
  • 22