OraFAQ Forum: SQL & PL/SQL » Passing bind variables to a VIEW

Home » SQL & PL/SQL » SQL & PL/SQL » Passing bind variables to a VIEW (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing bind variables to a VIEW [message #606265]

Tue, 21 January 2014 16:30

ora4me
Messages: 1
Registered: August 2012

Junior Member

I have a query like this:

with test1 as (
select emp.empno, emp.deptno, emp.name, emp.hiredate, dept.deptname
from emp, dept
where
emp.deptno = dept.deptno
and emp.deptno = 72
and emp.salary > 5000
)
select inner1.*
from (
select 'abc' as title,
1 emp_order,
name, hiredate, deptname
from test1
UNION ALL
select 'xyz' as title,
2 emp_order,
name, hiredate, deptname
from test1
) inner1
I am trying to remove the WITH clause completely and create a VIEW instead.The only problem I have is the dynamic value in the WITH clause.

I tried this:

CREATE VIEW testview as
select emp.empno, emp.deptno, emp.name, emp.hiredate, dept.deptname
from emp, dept
where
emp.deptno = dept.deptno
and emp.deptno = 72
and emp.salary > 5000

Updated query is:

select inner1.*
from (
select 'abc' as title,
1 emp_order,
name, hiredate, deptname
from testview
UNION ALL
select 'xyz' as title,
2 emp_order,
name, hiredate, deptname
from testview
) inner1

In this case how can I pass bind values for salary and deptno cols in the view?

Report message to a moderator

Re: Passing bind variables to a VIEW [message #606266 is a reply to message #606265]

Tue, 21 January 2014 17:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>In this case how can I pass bind values for salary and deptno cols in the view?
a VIEW is just a stored SELECT statement.
plain SQL does not support bind variable/value.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Passing bind variables to a VIEW [message #606275 is a reply to message #606266]

Tue, 21 January 2014 22:27

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

CREATE VIEW testview as 
select EMP.SALARY, emp.empno, EMP.DEPTNO, emp.name, emp.hiredate, dept.deptname 
from emp, dept 
where emp.deptno = dept.deptno

This view should suffice. To apply values to a view, you need to expose the needed columns.

select 'abc' as title, 1 emp_order, name, hiredate, deptname 
from testview 
where emp.deptno = 72 
and emp.salary > 5000 
UNION ALL 
select 'xyz' as title, 2 emp_order, name, hiredate, deptname
from testview 
where emp.deptno = 72 
and emp.salary > 5000

select *
from (
       select deptno,salary,'abc' as title, 1 emp_order, name, hiredate, deptname 
       from testview 
       UNION ALL 
       select deptno,salary,'xyz' as title, 2 emp_order, name, hiredate, deptname
       from testview 
      )
where deptno = 72 
and salary > 5000

Notice where the columns you apply values against, move around in the different query formulations.

Kevin

Report message to a moderator

Previous Topic:	UTL_SMTP
Next Topic:	Why Commit is not allowed in a trigger in oracle??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 05:39:07 CDT 2024