title: Oracle Pipelined Function with Joins
body: <p>Here is the creation of my two types needed for the Pipelined function to work:</p>
  
  <pre><code>create or replace type               RIS_CPU as object (
  NAME VARCHAR2(255 BYTE), COMPUTER_NAME VARCHAR2(255 BYTE), PERCENT_PROCESSOR_TIME NUMBER,
  REGION VARCHAR2(255 BYTE), ENVIRONMENT VARCHAR2(255 BYTE), GB_Date DATE, 
  DESCRIPTIVE_NAME VARCHAR2(255 BYTE), APPLICATION VARCHAR2(255 BYTE));  
  
  create or replace type RISSERVERCPUTABLE as table of MIIMonitoring.RIS_CPU; 
  </code></pre>
  
  <p>This is the section that is causing my error.  I do not really know how to approach creating 2 values to loop through for my cursor.  Please help.  </p>
  
  <pre><code>create or replace FUNCTION DUNDAS_FUNCTION_RIS_SERVER_CPU
  (
  SMOOTHV IN NUMBER, DAYSV IN NUMBER, SERVERV IN VARCHAR2
  ) RETURN RISSERVERCPUTABLE PIPELINED 
  AS 
  CURSOR DUNDAS_CURSOR2 IS 
  
  SELECT JOI.NAME, JOI.COMPUTER_NAME, JOI.PERCENT_PROCESSOR_TIME, JOI.REGION,
  JOI.ENVIRONMENT, JOI.GB_Date, N.DESCRIPTIVE_NAME, N.APPLICATION
  FROM
  (SELECT NAME, COMPUTER_NAME, TRUNC(AVG(PERCENT_PROCESSOR_TIME)+.5,0) PERCENT_PROCESSOR_TIME,
  REGION, ENVIRONMENT,
  TO_DATE(TO_CHAR(DT, 'YYYY-MM-DD HH24:')||TRUNC(TO_NUMBER(TO_CHAR(DT,'MI'))/SMOOTHV,0)*SMOOTHV||':00', 'YYYY-MM-DD HH24:MI:SS') AS GB_Date
  FROM APP_PROCESSOR 
  WHERE DT &gt; = sysdate - DAYSV
  AND NAME = '_Total'
  AND COMPUTER_NAME = SERVERV
  GROUP BY NAME, COMPUTER_NAME, REGION, ENVIRONMENT,
  TO_DATE(TO_CHAR(DT, 'YYYY-MM-DD HH24:')||TRUNC(TO_NUMBER(TO_CHAR(DT,'MI'))/SMOOTHV,0)*SMOOTHV||':00', 'YYYY-MM-DD HH24:MI:SS'))JOI
  LEFT OUTER JOIN 
  (SELECT SERVER_NAME, DESCRIPTIVE_NAME, APPLICATION
  FROM SERVERS_TRANSLATION_TABLE) N
  ON JOI.COMPUTER_NAME = N.SERVER_NAME;
  
  DUNDASRECORD1 "MIIMONITORING"."APP_PROCESSOR"%rowtype;
  DUNDASRECORD2 "MIIMONITORING"."SERVERS_TRANSLATION_TABLE"%rowtype;
  
  BEGIN
  OPEN DUNDAS_FUNCTION_RIS_SERVER_CPU.DUNDAS_CURSOR2;
  
  LOOP
  fetch DUNDAS_CURSOR2 into DUNDASRECORD1;
  exit when (DUNDAS_CURSOR2%notfound);
  fetch DUNDAS_CURSOR2 into DUNDASRECORD2;
  exit when (DUNDAS_CURSOR2%notfound);
  
  pipe row (RIS_CPU(
  DUNDASRECORD1.NAME, DUNDASRECORD1.COMPUTER_NAME, DUNDASRECORD1.PERCENT_PROCESSOR_TIME, 
  DUNDASRECORD1.REGION, DUNDASRECORD1.ENVIRONMENT, 
  DUNDASRECORD1.GB_DATE, DUNDASRECORD2.DESCRIPTIVE_NAME, DUNDASRECORD2.APPLICATION)
  );
  END LOOP;
  CLOSE DUNDAS_CURSOR2;
  RETURN;
  END DUNDAS_FUNCTION_RIS_SERVER_CPU;
  </code></pre>
  
  <p>Right now this returns the error: </p>
  
  <pre><code>FUNCTION DUNDAS_FUNCTION_RIS_SERVER_CPU compiled
  Errors: check compiler log
  FUNCTION DUNDAS_FUNCTION_RIS_SERVER_CPU compiled
  Errors: check compiler log
  </code></pre>