Manipulating Oracle Parameters

[getjbb]

Is it possible to change a where clause condition according to a parameter coming into a package.

The problem is that I have a proprietary package that cannot be changed. The package, currently, has a parameter called a_date that will need, eventually, handle a date range.

A date range will be entered via a User interface screen.

The where clause within this package has the following criteria to handle a_date:

AND (tran_date <= TRUNC(a_date) - 60)

but in order to handle the range it needs to be:

AND (tran_date between TRUNC(a_date1)and TRUNC(a_date2) - 60)

Is there some way of manipulating, using Oracle, the dates (from/to) coming from the User Interface to the proprietary package, to get the results I need.

As a reminder I cannot change the propritary package. It is only expecting a date.

Thanks.

getjbb

 

[SantaMufasa]

Getjbb,

I'm not certain what you are requesting...Are you asking if there is a way to change a proprietary package that allows for a single date to now allow for two dates?

If that is your question, then the only answer I can imagine is to request of someone with access to the proprietary code to alter the code.

If I misunderstood your question, then please re-state the question.

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.”

 

[getjbb]

What I want to know is if there is a way to create an Oracle wrapper to convert a date range to enable the proprietary package to use it according to accomodate the following where clause criteria;

The where clause within the package, currently, has the following criteria to handle a_date:

AND (tran_date <= TRUNC(a_date) - 60)

but in order to handle the range it needs to be:

AND (tran_date between TRUNC(a_date1)and TRUNC(a_date2) - 60)

The proprietary code cannot be changed due to cost and other code dependence upon it as is.

Thanks.

getjbb

 

[SantaMufasa]

Please pardon my thickheadedness, Getjbb, but if, as you say, "The where clause within the package..." behaves a certain way, then regardless of our ability to "create an Oracle wrapper" (which, yes, you can do), I don't see a way to change the proprietary package's where-clause behavior.

The problem with the package's WHERE clause is that it contains only "less-than-or-equal-to" logic...it cannot be enhanced to also contain "and greater-than-or-equal-to" logic or to use between logic.

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.”

 

[getjbb]

Thanks.

getjbb