title: Not able to store values to a nested table in oracle
body: <p>I am trying to write a function to show values for monthly data according to the selection made by the user in monthly report. Code snippet below is just trying to fetch values in a nested table and once data is loaded successfully in a nested table, I will call the function to display the table. I have tried a few things; but am running into issues while loading data. Below are 2 different SQLs to create this function but both of them are getting same error regarding incorrect values; I have tried a few things but to no avail:</p>
  
  <p>Snippet 1:</p>
  
  <pre><code>/* Formatted on 10/16/2012 8:40:45 AM (QP5 v5.215.12089.38647) */
  
  CREATE OR REPLACE TYPE tempObject AS OBJECT
  (
     kpiid number,
     kpigroup VARCHAR2 (300)
  );
  
  CREATE OR REPLACE TYPE tempTable AS TABLE OF tempObject;
  
  
  CREATE OR REPLACE FUNCTION KPI_HORIZON.Monthly_All_Data (
     mainarea IN VARCHAR2)
  
     RETURN tempTable
  IS
     MonthlyData   temptable := temptable ();
     n             INTEGER := 0;
  
  BEGIN
  
     IF (mainarea = 'ALL')
  
     THEN
        FOR r IN (SELECT DISTINCT kpiid, kpigroup
                    FROM kpi_summary_reporting
                   WHERE kpifrequency = 'Monthly' AND active_ind = 'Y')
        LOOP
           monthlydata.EXTEND;
           n := n + 1;
           monthlydata (n) := tempobject (r.kpiid, r.kpigroup);
        END LOOP;
    END IF;
  
     RETURN MonthlyData;
  END;
  </code></pre>
  
  <p>Error: [Error] PLS-00306 (26: 29): PLS-00306: wrong number or types of arguments in call to 'TEMPOBJECT'</p>
  
  <p>Snippet2:</p>
  
  <pre><code>/* Formatted on 10/16/2012 8:27:22 AM (QP5 v5.215.12089.38647) */
  CREATE OR REPLACE TYPE tempObject AS OBJECT
  (
     kpiid NUMBER,
     kpigroup VARCHAR2 (300)
  );
  
  CREATE OR REPLACE TYPE tempTable AS TABLE OF tempObject;
  
  
  CREATE OR REPLACE FUNCTION KPI_HORIZON.Monthly_All_Data (
     mainarea IN VARCHAR2)
     RETURN tempTable
  AS
     MonthlyData   temptable := temptable ();
  BEGIN
     IF (mainarea = 'ALL')
     THEN
        SELECT DISTINCT ksr.kpiid, ksr.kpigroup
          INTO MonthlyData 
          FROM kpi_summary_reporting ksr
         WHERE kpifrequency = 'Monthly' AND active_ind = 'Y';
     ELSE
        SELECT DISTINCT kpiid, kpigroup
          INTO MonthlyData
          FROM kpi_summary_reporting;
     END IF;
  
     RETURN MonthlyData;
  END;
  </code></pre>
  
  <p>Error: [Error] ORA-00947 (24: 9): PL/SQL: ORA-00947: not enough values</p>