You can try Pipelined Function in Oracle 9i. It's very convenient.

If I wish to generate all the week days (mon to fri) one way is to by querying user_objects. Some like this

select to_char(to_date('01-jan-2003') + rownum,'day') , to_date('01-jan-2003') + rownum from user_objects
where rownum <= 365
and to_char(to_date('01-jan-2003') + rownum,'day') not in ('sunday', 'saturday')

Is there any other way to get the result without using user_objects or any intermediate table ?

But all_objects is not a table, it's a obscenely complicated view!

Pipelined function or parametrized view is the clean way to solve this.

In a simple test in Oracle9i, using a type constructor seems to work a lot quicker than using all_objects.
It also looks 'tidier' to me. [I don't want anything from all_objects, so I don't want to reference it]

create type year IS varray(366) of number(3) not null;

select sysdate+rownum
from
all_objects

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 300.00 1953.13 0 0 0 0
Execute 1 0.00 3.76 0 0 0 0
Fetch 381 19800.00 18687.22 0 117861 0 5699
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 383 20100.00 20644.11 0 117861 0 5699

select sysdate+rownum
from table(year(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25)) a,
table(year(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25)) b,
table(year(1,2,3,4,5,6,7,8,9)) c

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 100.00 54.76 0 0 0 0
Execute 1 0.00 1.21 0 0 0 0
Fetch 376 200.00 446.62 0 0 0 5625
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 378 300.00 502.59 0 0 0 5625

Parametrized view is a synomym for table function.

As far as the performance concerned this is not even a competition:

SQL> CREATE TYPE IntSet AS TABLE OF Integer;
  2  /

SQL> CREATE or replace FUNCTION UNSAFE
  2    RETURN  IntSet PIPELINED IS
  3  BEGIN
  4      loop
  5         PIPE ROW(1);
  6      end loop;
  7  END;
  8  /

Function created.

SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select count(1) from table(unsafe) 
where rownum < 1000000;

  COUNT(1)
----------
    999999

Elapsed: 00:00:01.08

         95  consistent gets
          2  physical reads

SQL> select count(1) from all_objects where rownum < 1000000;

  COUNT(1)
----------
    322153

Elapsed: 00:03:21.02

     608806  consistent gets
       7645  physical reads

2 problems with the actual view: 
1. It is unable to give me the full list of 1000000 numbers, as there are only 322K objects in the database.
2. You can't possibly beat 95 buffer gets for table fiunction. 

If there were no sorting involved, that is if I just wanted the list of million numbers back, the difference would be even more dramatic. 

They are the synomyms. When googling "parameterized view table function" I see that the context is mostly SQL Server.

Of course you realize how goofy passing parameters to a view through the context is. Table function allows embedding the view and pass parameters into the view definition as host variables.

From engineer perspective there is a difference between 10 and 10000. From matematician's there isn't.

We have a few views doing excelent using this technique. They could also be used as, sort of VPD, in the SE.

"create or replace view V
as
select ....
where c1 = sys_context( 'my_ctx', 'c1' )
and c2 like sys_context( 'my_ctx', 'c2' )"

I dont understand this. What is the difference between this and

create or replace view v as select c1,c2,....

and then a query like

select ... from V where c1=:b1 and c2=:v2

i.e. just expose c1 and c2 in the view and use it as usual with regular bind variables.

What does the sys_context stuff buy us here?

Thanks

Great, didnt think about all the situations you pointed out.

Now that you mention it, I can think of many cases where I would want to push the predicates way down into the view and just exposing the columns in the view might not work if the view is joined with few other things, aggregated, etc.

You are absolutely right, as usual. Thanks.

One more question: In the view

create or replace view V
as
select ....
where c1 = sys_context( 'my_ctx', 'c1' )


The sys_context is treated like a bind variable so the query is not hard-parsed every time it is encountered.

But if I do

exec dbms_session.set_context('my_ctx,'c1','aaa')
select * from v;

exec dbms_session.set_context('my_ctx,'c1','bbb')
select * from v;

Since the query is not hard parsed the second time around, when/where is the sys_context evaluated. It has to be evaluated for every execution of the query otherwise the result might be wrong.

Thanks

What do I need to set up a parameterize view, I mean:
Grants needs to execute it
Do I have to create a context per view?
Can you share us an example step by step.

I can create the view as follow:

CREATE TABLE T1(col1 INT, col2 INT);
INSERT INTO T1 VALUES(1,2);
INSERT INTO T1 VALUES(3,4);
INSERT INTO T1 VALUES(5,6);
INSERT INTO T1 VALUES(7,8);
COMMIT;
CREATE OR REPLACE VIEW PV_T1 AS
SELECT col2 FROM T1
WHERE col1 = sys_context('ctx','col1');
--but what privileges do I need??
u01@VENUS> EXEC dbms_session.set_context('ctx','col1','3');
BEGIN dbms_session.set_context('ctx','col1','3'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at line 1

I have created a code to call dbms_session.set_context, and now it works.

CREATE OR REPLACE PACKAGE BNFPUBLIC.PKG_EXECUTE
AUTHID CURRENT_USER AS
PROCEDURE SP_CONTEXT
( pcolumn IN VARCHAR2,
pvalue IN VARCHAR2
);
END PKG_EXECUTE;
/

CREATE OR REPLACE PACKAGE BODY BNFPUBLIC.PKG_EXECUTE
AS
PROCEDURE SP_CONTEXT
( pcolumn IN VARCHAR2,
pvalue IN VARCHAR2
) AS
BEGIN
dbms_session.set_context('CTX', pcolumn, pvalue);
END SP_CONTEXT;
END PKG_EXECUTE;
/

u01@VENUS> EXEC pkg_execute.sp_context('col1', 3);
u01@VENUS> SELECT * FROM u01.pv_t1;
COL2
----------
6

...If it is like a bind variable, it'll use the pk or idx as well???

right :)

Hello Tom,

Which option would you choose and why in an environment where hundreds of users will need different result sets based on different parameters passed by in each session:

parametrized views?
pipelined funstions?
pl/sql in global temporary tables?

(ref cursors or arrays of records are out of the question due to third party software limitations).
The result sets should be in the hundreds of rows not more than that.

Thank you very much for your response and for the amazing site,

Hello Tom,

We will only select 3 fields, 10,20 and 45 varchar2 fields.

Thank you,