Skip to Main Content
  • Questions

  • TABLE function and where clause parameters retrieving

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Olivier.

Asked: August 31, 2018 - 3:05 pm UTC

Last updated: September 05, 2018 - 2:33 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi there,

My goal is to return fieldX or fieldY depending on the where clause (which I don't have any control on since it's generated by a third party software).

The TABLE() function is very close to what I want to achieve :

select * from TABLE(customFunction()) where param1=XXX AND param2=XXX ...

That way I can construct the output in customFunction() thus enabling me to return a field or another depending on the where clause parameters.

This approach leads me to two other questions :

-Since I need to create a TYPE for this to work, is it possible to create it dynamically using the actual table schema (where the data will be coming from) at runtime ? Everything is there so it might be possible but I don't know if Oracle supports this.

-I would also need to get the actual values of the query (WHERE field1=value1 AND field2=value2) of the where clause to perform the verification needed to return fieldX or FieldY. How can I retrieve those values in PL/SQL ?

Thanks for your help.

and Chris said...

I'm not aware of a way to inspect the current values of variables in your where clause.

But I think you're overcomplicating this. You could write a case expression to return a column or value based on some criteria. So you can set this up so you return the relevant value when the where clause matches the values you expect:

create table t (
  c1 int, c2 int
);

insert into t values ( 1, 2 );
commit;

var p1 number;
var p2 number;

exec :p1 := 1;
exec :p2 := 1;

select case
         when :p1 = 1 and :p2 = 1 then c1
         when :p2 = 2 and :p2 = 2 then c2
       end
from   t
where  :p1 = 1
and    :p2 = 1;

CASEWHEN:P1=1AND:P2=1THENC1WHEN:P2=2AND:P2=2THENC2END   
                                                      1 

exec :p1 := 2;
exec :p2 := 2;

select case
         when :p1 = 1 and :p2 = 1 then c1
         when :p2 = 2 and :p2 = 2 then c2
       end
from   t
where  :p1 = 2
and    :p2 = 2;

CASEWHEN:P1=1AND:P2=1THENC1WHEN:P2=2AND:P2=2THENC2END   
                                                      2


But this whole requirement sounds fishy. What's the end goal here? Why are you trying to do this? What's the bigger picture?

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Big Picture

Olivier Gagnon, August 31, 2018 - 5:31 pm UTC

Chris,

thanks for your answer.

I thought I could remove much of the complexity from my question but, fair enough, here's the big picture:

I'm working in a spatial context.

I enter a sql statement in a third party software :

ex: select Pk, GEOM1 from table1

table1 contains two SDO_GEOMETRY fields :

GEOM1 and GEOM2

At runtime, third party software "completes" the query by wrapping my query in a subquery and adding a spatial where clause. Here's is the third party's log so you have a clear idea of whats happening :

SELECT * FROM (select Pk, GEOM1 from TABLE1
) VTABLE WHERE SDO_FILTER(GDO_GEOMETRY_S, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

Setting parameter 1 as MDSYS.SDO_GEOMETRY(2003,32198,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-372906.49583491014,786286.4324127169,-372233.6570917371,787207.4219100565))

Using value of parameter 1, I need to calculate the extent of the bbox. Depending on the result, I need to use GEOM1 or GEOM2 for the query.

There's probably another solution but from my research I'd go with a TABLE() function. That way I could build the resultset at runtime.

Query would be

select Pk, GEOM1 from TABLE(CustomFuntion())

The query launched by the third party would then be :

SELECT * FROM (select Pk, GEOM1 from TABLE(CustomFunction())
) VTABLE WHERE SDO_FILTER(GEOM1, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

I think that I could just alias the SDO_GEOMETRY field (AS GEOM1) to make it work.

While I was writing this response, I stumble upon this thread :

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526156000346490143

Which is basically what I'm trying to achieve. I particularly like this line :

Depending on the level of interception etc, you might want to consider things at the database level ? eg RLS, sql translation, query rewrite, or advanced rewrite ?

Thanks


Chris Saxon
September 03, 2018 - 10:40 am UTC

I'm still a bit puzzled about this. It sounds like you have a chick-and-egg problem. You need to decide what to return based on filters. But the filters themselves determine what you choose!

So I'm not sure how to resolve this.

The SQL translation framework allows you to rewrite a query to "anything" else. So this may help. But this is a 12c feature. I'm not sure what would help in 11g.

Read about it at:

https://docs.oracle.com/en/database/oracle/oracle-database/18/drdaa/SQL-translation-framework-overview.html#GUID-C7814DA0-96BA-4D15-A348-6F0960D4475F

Big Picture

Olivier Gagnon, August 31, 2018 - 5:32 pm UTC

Chris,

thanks for your answer.

I thought I could remove much of the complexity from my question but, fair enough, here's the big picture:

I'm working in a spatial context.

I enter a sql statement in a third party software :

ex: select Pk, GEOM1 from table1

table1 contains two SDO_GEOMETRY fields :

GEOM1 and GEOM2

At runtime, third party software "completes" the query by wrapping my query in a subquery and adding a spatial where clause. Here's is the third party's log so you have a clear idea of whats happening :

SELECT * FROM (select Pk, GEOM1 from TABLE1
) VTABLE WHERE SDO_FILTER(GDO_GEOMETRY_S, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

Setting parameter 1 as MDSYS.SDO_GEOMETRY(2003,32198,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-372906.49583491014,786286.4324127169,-372233.6570917371,787207.4219100565))

Using value of parameter 1, I need to calculate the extent of the bbox. Depending on the result, I need to use GEOM1 or GEOM2 for the query.

There's probably another solution but from my research I'd go with a TABLE() function. That way I could build the resultset at runtime.

Query would be

select Pk, GEOM1 from TABLE(CustomFuntion())

The query launched by the third party would then be :

SELECT * FROM (select Pk, GEOM1 from TABLE(CustomFunction())
) VTABLE WHERE SDO_FILTER(GEOM1, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

I think that I could just alias the SDO_GEOMETRY field (AS GEOM1) to make it work.

While I was writing this response, I stumble upon this thread :

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526156000346490143

Which is basically what I'm trying to achieve. I particularly like this line :

Depending on the level of interception etc, you might want to consider things at the database level ? eg RLS, sql translation, query rewrite, or advanced rewrite ?

Thanks

Big Picture

Olivier Gagnon, August 31, 2018 - 6:32 pm UTC

Chris,

thanks for your answer.

I tried responding with a more complete explanation but when I click "Submit Review" button, I'm taken to the question page without any message.

Here's a shorter one

I'm working in a spatial context.

I enter a sql statement in a third party software :

ex: select Pk, GEOM1 from table1

table1 contains two SDO_GEOMETRY fields :

GEOM1 and GEOM2

At runtime, third party software "completes" the query by wrapping my query in a subquery and adding a spatial where clause. Here's is the third party's log so you have a clear idea of whats happening :

SELECT * FROM (select Pk, GEOM1 from TABLE1
) VTABLE WHERE SDO_FILTER(GEOM1, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

Setting parameter 1 as MDSYS.SDO_GEOMETRY(2003,32198,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-372906.49583491014,786286.4324127169,-372233.6570917371,787207.4219100565))

Using value of parameter 1, I need to calculate the extent of the bbox. Depending on the result, I need to use GEOM1 or GEOM2 for the query.

Thanks

Clear picture

Olivier Gagnon, September 03, 2018 - 7:44 pm UTC

Hi,

Actually it's pretty simple and there's no chick-and-egg problem.

Here's the pseudo code :

if parameter 1 = X
perform query with GEOM1
else
perform query with GEOM2

The only thing I need to get is parameter 1. There's nothing more to it really.

Thanks again for your help.
Chris Saxon
September 04, 2018 - 10:10 am UTC

Does the application bind by name or position?

If it's by name, can you do something like:

         case 
           when ? = X then GEOM1 
           else GEOM2 
         end geom


to give:

SELECT * FROM (
  select Pk, 
         case 
           when ? = X then GEOM1 
           else GEOM2 
         end geom
  from TABLE1 
) VTABLE 
WHERE SDO_FILTER(geom, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'


If not, the SQL translation framework may help. I don't know of another way.

Calculation/Logic

Olivier Gagnon, September 04, 2018 - 5:26 pm UTC

Hi,

I looked into sql_translation and from the examples I've seen, you can't perform logic.

From the third party's log, I think the parameter mapping is done by position and not by name.

The solution you provided could've been a good approch but since I have to perform calculation/logic on the parameter to decide which field must be used and returned, I think I can't use it.

To me, the solution is still using a TABLE() function but I need to get the parameter value to achieve my goal.

The question is : Is it possible to get the parameter value from a select statement in Pl/SQL ?

Thanks


Chris Saxon
September 05, 2018 - 10:19 am UTC

Is it possible to get the parameter value from a select statement in Pl/SQL ?

I'm not aware of a way to do this.

If it's positional

Chuck Jolley, September 04, 2018 - 7:51 pm UTC

Will the software accept a with clause in the select statement?

eg
with p as
(select ? param1, ? param2 from dual)
select etc
from t, p
where etc

That way the parameters only resolve once and you can use them by name in your logic.
Chris Saxon
September 05, 2018 - 10:21 am UTC

Worth investigating. If I've understood correctly, the app adds the where clause after the fact. Which is also when it binds the value. So this wouldn't help sadly :(

anyway

Racer I., September 05, 2018 - 12:44 pm UTC

Hi,

I have no experience with SQL-Translate but if it can duplicate bind variables this might work :

SELECT * FROM (select Pk, GEOM1 from TABLE(CustomFunction(?))
) VTABLE WHERE SDO_FILTER(GEOM1, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

then put your logic into your function and map the correct column to GEOM1 or use

case when CustomFunction(?) = 1 then GEOM1 else GEOM2 end geom

regards,
Chris Saxon
September 05, 2018 - 2:33 pm UTC

Yeah, that's what I was thinking. Haven't tested it yet though...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.