Function return sys_refcursor call from sql with specific columns

by

For that purpose, you might want to take a look at PIPELINED functions. You will have to declare explicit type at PL/SQL level though. That part will set the output column name:

CREATE OR REPLACE TYPE my_rec AS OBJECT (
  c CHAR,
  n NUMBER(1)
);

CREATE OR REPLACE TYPE my_tbl AS TABLE OF my_rec;

Now, the great advantage is you can not only “rename” your columns, but modify the records from your cursor on the fly too. For ex:

CREATE OR REPLACE FUNCTION my_fct
RETURN my_tbl PIPELINED
AS
  -- dummy data - use your own cursor here
  CURSOR data IS
      SELECT 'a' as A, 1 AS B FROM DUAL UNION 
      SELECT 'b', 2 FROM DUAL UNION 
      SELECT 'c', 3 FROM DUAL UNION 
      SELECT 'd', 4 FROM DUAL;
BEGIN
  FOR the_row IN data
  LOOP 
      PIPE ROW(my_rec(the_row.a, the_row.b*2));
      --                                  ^^
      --                            Change data on the fly
  END LOOP;
END

Usage:

SELECT * FROM TABLE(my_fct())
--            ^^^^^^^^^^^^^^^
--     Use this "virtual" table like any
--     other table. Supporting `WHERE`  clause
--     or any other SELECT clause you want

Producing:

C   N
a   2
b   4
c   6
d   8

More Related Contents:

  1. Is it possible to output a SELECT statement from a PL/SQL block?
  2. What is the difference between function and procedure in PL/SQL?
  3. How to use BOOLEAN type in SELECT statement
  4. Solution to “cannot perform a DML operation inside a query”?
  5. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  6. Receive an unexpected symbol error in my PLSQL query [closed]
  7. PL SQL UPDATE STATEMENT
  8. Splitting string into multiple rows in Oracle
  9. Search All Fields In All Tables For A Specific Value (Oracle)
  10. Difference between language sql and language plpgsql in PostgreSQL functions
  11. What is the string concatenation operator in Oracle?
  12. SQL to generate a list of numbers from 1 to 100
  13. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  14. Excel function to make SQL-like queries on worksheet data?
  15. passing table and column name dynamically using bind variables
  16. Selecting the second row of a table using rownum
  17. Is there any way to flush output from PL/SQL in Oracle?
  18. how to transform comma separated column into multiples rows in db2
  19. Update with Join query in Oracle
  20. What is the difference between SQL, PL-SQL and T-SQL?
  21. PLSQL Insert into with subquery and returning clause
  22. What does the colon sign “:” do in a SQL query?
  23. Oracle (Old?) Joins – A tool/script for conversion?
  24. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  25. Functions with variable number of input parameters
  26. Advice Using Pivot Table in Oracle
  27. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  28. How to check if a column exists before adding it to an existing table in PL/SQL?
  29. How to call Oracle MD5 hash function?
  30. How can I determine if a string is numeric in SQL?

Leave a Comment