How to pipeline a function with a dynamic name of columns?

I need to create a report which retrieve expenses for the last 12 months. Depends on the day I execute my report the name of the columns of my report will change (for example if I execute the report in JUL, it should retrieve twelve columns with the expenses in JUN, MAY, APR, MARCH. The name of the columns are the last 12 months).

The following PL/SQL code creates a view with dinamic name columns depending 开发者_如何学Pythonon the sysdate.

DECLARE

vSQL VARCHAR2(4000);

BEGIN

vSQL := 'CREATE OR REPLACE VIEW IFSAPP.RLDateTest AS SELECT 
            ip.part_no,
            IFSAPP.RD_PURCH_DEMAND_QTY_ISSUE_API.Get_Avr_Usage_Per_Month(ip.contract,ip.part_no ,to_char(sysdate, ''MM'')-1) ' || **SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3)** ||
         ', IFSAPP.RD_PURCH_DEMAND_QTY_ISSUE_API.Get_Avr_Usage_Per_Month(ip.contract,ip.part_no ,to_char(sysdate, ''MM'')-2) ' || **substr(ADD_MONTHS(SYSDATE, -2),4,3)** || ' FROM ifsapp.inventory_part ip WHERE ip.contract = ''S03'' ';

EXECUTE IMMEDIATE vSQL;

END;

(|| SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3) || is the alias of each colum)

The point is that once I execute my report, it should create the view and show all its rows. For that I had thought of using pipelined (to create a type table consisting of the result of my view and this would let me do SELECT * FROM TABLE(getView())) in my report.

My problem is that as the name of the columns of my view are not always the same I can't create my type table using pipelined.

Any idea how could do that or any other solution which let me use my view in a select statment?

Many thanks.

---

Either I have completely misunderstood your requirements or this is a design nonsense. It's a very rare situation where you have to dynamically do any DDL. To have to create dynamically a VIEW is very suspicious.

However you didn't name your columns in the select part so they get implicit names and that may be what makes tham "dynamic". When you make any expression in your select statements, always name them:

create or replace view v1 as 
select a + b as a_plus_b 
  from table t;

Have no idea what pipelining has to do with this at all.

---

Don't give a column the name of some parameter. Give it the name of the value it returns. In your case it would be: "Avr_Usage_this_Month", "Avr_Usage_previous_Month". Now the columns always have the same name. Much easier to use your view now.

Some further remarks:

SUBSTR(ADD_MONTHS(SYSDATE, -1),4,3)
substr(ADD_MONTHS(SYSDATE, -2),4,3)

Wouldn't this result in the same column name, unless you run this at the second day of the month?

Also, always use explicit date format strings. You cannot rely on these statements to always return the name of a month.

---

I'd go with one view/function that selects the twelve columns as

month_minus_1, month_minus_2,....month_minus_12

Then either the end client can figure out the report column headers title by itself. Failing that hardcode the 12 options and just have a fresh script run on the 1st of the month through DBMS_SCHEDULER.