Create Oracle View and calling a function

[SundancerKid]

How would I call a Function within an Oracle View?

 

[Turkbear]

Hi,
A view is just a 'regular' sql query stored in the data dictionary, so you can call the function however you would normally in your query.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

...As an example using Oracle's "to_char" function:

create or replace view sundancer as
select last_name, to_char(start_date,'Month') Hire_month
  from s_emp;

View created.

select * from sundancer;

LAST_NAME                 HIRE_MONTH
------------------------- ----------
Velasquez                 March
Ngao                      March
Nagayama                  June
Quick-To-See              April
Ropeburn                  March
Urguhart                  January

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SundancerKid]

I want to use a function in the Where clause.

I was trying to use it like this

Select a, b, c 
From ABCClub
Where (Select Function_AthruZ ( 'p_ccn', 'p_type') as MyResults from dual)

Function_AthruZ is the Function I am calling and it has 2 Parameters and it is returning a VarChar2 field.

Thanks

 

[SundancerKid]

It is not working in Where Clause

 

[SantaMufasa]

It is not working in Where Clause

That's because a WHERE clause evaluates a conditional phrase which results must be TRUE for a row to become part of the result set.

The way you syntax currently reads would be like saying:

...WHERE 103

...which is not a valid boolean result.

I presume that your "Function AthruZ" yields some result (e.g. "xyz"). Re-word your code to read:

Select a, b, c 
From ABCClub
Where Function_AthruZ ( 'p_ccn', 'p_type') = 'xyz';

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SundancerKid]

When used in a Stored Procedure it is used as

wh := ' FROM MV_IHSAMPLE WHERE ' || Function_AthruZ('','');

The Function_AthruZ returns the results as '1 = 0' or '1 = 1'

So I could say

Select a, b, c 
From ABCClub
Where Function_AthruZ ( 'p_ccn', 'p_type') = '1 = 1'

is that right?

 

[SantaMufasa]

is that right?

Yes, that would seem to work. What happens when you use that construct?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”