Table-Valued Functions in ORACLE 11g ? ( parameterized views )

by

No need for SYS_CONTEXT or cursor definitions.
You do need a type so that, when the SQL is parsed, it can determine which columns are going to be returned.
That said, you can easily write a script that will generate type and collection type definitions for one or more tables based on the data in user_tab_columns.

The closest is

create table my_table
(prodid number, a varchar2(1), b varchar2(1), 
  c varchar2(1), d varchar2(1), e varchar2(1));

create type my_tab_type is object
(prodid number, a varchar2(1), b varchar2(1), 
  c varchar2(1), d varchar2(1), e varchar2(1))
.
/

create type my_tab_type_coll is table of my_tab_type;
/

create or replace function get_some_data (p_val in number) 
return my_tab_type_coll pipelined is
begin
  FOR i in (select * from my_table where prodid=p_val) loop
    pipe row(my_tab_type(i.prodid,i.a,i.b,i.c,i.d,i.e));
  end loop;
  return;
end;
/

SELECT * FROM table(get_Some_Data(3));

More Related Contents:

  1. creating parameterized views in oracle11g
  2. Maximum Number of times we can query an oracle database [closed]
  3. How to uninstall / completely remove Oracle 11g (client)?
  4. Oracle trigger after insert or delete
  5. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  6. How to handle Day Light Saving in Oracle database
  7. What is the effect of placing the commit after DML in procedure?
  8. Auto Increment for Oracle
  9. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  10. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  11. Oracle Search all tables all columns for string [duplicate]
  12. NLS_NUMERIC_CHARACTERS setting for decimal
  13. Create Pivot view in SQL from a SQL table
  14. How to create a user in Oracle 11g and grant permissions
  15. Trigger to enforce M-M relationship
  16. How can I describe a table in Oracle without using the DESCRIBE command?
  17. Functions vs procedures in Oracle
  18. How to insert a column in a specific position in oracle without dropping and recreating the table?
  19. Get the number of days between two dates in Oracle, inclusive of the dates
  20. HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g
  21. passing in table name as plsql parameter
  22. How to use parameters in a ‘where value in…’ clause?
  23. Why cannot I create triggers on objects owned by SYS?
  24. No more data to read from socket
  25. return resultset from function
  26. ORA-12704: character set mismatch
  27. No more data to read from socket error
  28. Auto-increment in Oracle without using a trigger
  29. How to send email using Oracle 10 g Forms
  30. What is a View in Oracle?

Leave a Comment