Oracle: parametrized queries

[boudino]

Hi

Is there any way how create a query with parameter, which I can then call as "subprogram"? Something like named query created using with statement, but with parameters (unfortunately, named queries does not work form me even without parameters).

I don't want to use packages or stored procedures, I would like to achieve this only as SQL statement.


Hope you can understad what I want. I'm not sure myself how to describe it :sick: Maybe an example can help? It is PSEUDOCODE, but as ilustration it can work,
with fromdual as (select * from dual where dommy = avalue)
select * from fromdual('a')
union
select * from fromdual('b')

[exterminator]

Not clear :confused: .. what do you actually want to do? All i could get a hint of is that you want a query to be dynamically built. If its that then its pretty simple but I guess you mean something else. The pseudo code doesnt help either. All it tells me is that you want some QueryBuilder class whose functions you call with various parameters to build a final sql command object that you can then execute against the database. Please explain the problem in a little bit more detail.. and then lets see if we can come up with something that you want! :) Regards.

[boudino]

That is what I was affraid of - nobody could understood me :(

But despite that, I think that you are on the right track, exterminator. But I don't want not to have anything which could persist in DB after the evaluation (function, stored procedure). I also don't want to use something like .NET, Java, Perl or other higher programming language.

What I would like to have is parametrized subquery which I can reuse.

I try to explain it whith example: I have comlex select statement with many joins. I want to get records meeting a condition, say a date range. That's simple. But I want to run this select for far several different date ranges and union results to one result table. The whole script I want to run just in TOAD.
I don't want to write whole base select more than once, because it is complex and it will be hard (to immposible) to maintain it if there will be changes.

It is more undrestanable now? Answer is important for me. If my idea is unreachable in Oracle, I need to know it to avoid spending my time with chasing Chimera.

[Krzemo]

If I understand U correctly,U want to make 1 script in which U can easily change parameters.... so why don't U use parameters in query itself ?

Since it is one time script U can declare params and set values at begining or palce special metatags in code which can be easily replaced with proper values (search and replace).

Best regards,
Krzemo

[exterminator]

I even had problems understanding why the usage of stored procedures was ruled out...

Dynamic SQL (http://www.sqlteam.com/item.asp?ItemID=4599) - this is relevant to MS SQL Server but I am sure oracle must be supporting dynamic stored procedures (even though they cause a bit of performance hit)