Link to home
Start Free TrialLog in
Avatar of Gene Moody
Gene MoodyFlag for United States of America

asked on

ORACLE Fetch INTO (Custom) TYPE Structure

Hey Experts!

Here we go again!  (Unfortunately I'm _still_ learning how to use Oracle - it's wonderful, but because it can do anything, I end up confused a lot.)

Here's the situation: I have this neat-o function that will go out and, based on a [Process_Step], a [DateRange] (in days), and an [Anchor Date] (date point of reference) will go out and grab some statistics.  That's where the problem initially lies: statisticS, not just a single return value.  So I looked around the 'net, and the general gist I got was "create a TYPE in ORACLE, and use it as your return value.  So I did.  (A few may even remember these from yesterday's fun, excitement, and really wild things...)

        DROP TYPE LONGFIST.FIVE_POINT_SUMMARY;

        CREATE OR REPLACE TYPE LONGFIST.Five_Point_Summary AS OBJECT
        (
          Min_Outlier NUMBER(14,4),
          Lower_Quartile NUMBER(14,4),
          Median_Value NUMBER(14,4),
          Upper_Quartile NUMBER(14,4),
          Max_Outlier NUMBER(14,4),
          Std_Average NUMBER(14,4),
          IQRange NUMBER(14,4)
        );
        /

Open in new window


...so far, so good.  Then I created this monstrous FUNCTION to make use of it.  The only problem is I have no idea how to FETCH the CURSOR into the return value LONGFIST.Five_Point_Summary - it's apparently not as easy as

           FETCH dataSummary INTO dwellTimeSummary.Min_Outlier,
                      dwellTimeSummary.Lower_Quartile,
                      dwellTimeSummary.Median_Value,
                      dwellTimeSummary.Upper_Quartile,
                      dwellTimeSummary.Max_Outlier,
                      dwellTimeSummary.Std_Average,
                      dwellTimeSummary.IQRange;

Open in new window


...I do know that I can set the values initially, because this made it past the compiler:
        dwellTimeSummary.Min_Outlier := 0;
        dwellTimeSummary.Lower_Quartile := 0;
        dwellTimeSummary.Median_Value := 0;
        dwellTimeSummary.Upper_Quartile := 0;
        dwellTimeSummary.Max_Outlier := 0;
        dwellTimeSummary.Std_Average := 0;
        dwellTimeSummary.IQRange := 0;

Open in new window


..so I had a few clues - but now I'm just stalemated.

The function makes use of a cursor that contains the massive SQL code, which ends up returning a bunch of statistical summations, listed above.  It just gives me:

        ORA-06530: Reference to uninitialized composite
        ORA-06512: at "LONGFIST.LRI_DWELL_SUMMARY", line 60

...and nothing more gets accomplished.  I'm not sure what I'm supposed to be doing here.  And yes, because all of these return values are numbers, I guess I could return an ARRAY: I simply do not know how to.  And there seems to be a dearth of shared info about this.

...because I know somebody will ask, here is the code for the FUNCTION, as I currently have it (and it currently compiles).  Of course, lots of things will compile but will not run.  This is (unfortunately) one of them.  What do I need to do to fix this?
CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY
( Process_Step IN VARCHAR2, DayRange IN NUMBER, AnchorDate IN VARCHAR2 )
RETURN LONGFIST.FIVE_POINT_SUMMARY
IS
dwellTimeSummary LONGFIST.FIVE_POINT_SUMMARY;

CURSOR dataSummary IS
       SELECT MIN("Elapsed Total") LOWER_OUTLIER,
              PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total") LOWER_QUARTILE,
              PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY "Elapsed Total") MEDIAN_VALUE,
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") UPPER_QUARTILE,
              MAX("Elapsed Total") UPPER_OUTLIER,
              ROUND(AVG("Elapsed Total"), 4) STANDARD_AVERAGE,
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total") INTERQUARTILE_RANGE
       FROM
       ( 
              SELECT ROUND(((86400 * (TO_DATE(SUBSTR(tsComplete, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4)  "Elapsed Total"
              FROM   LR_MEASURES.INERFEROMETRY@EPICENTRE a,
                     LR_MEASURES.SPECTROSCOPY@EPICENTRE b 
              WHERE  a.OBID = b.LEFT
              AND    b.CLASS1 = 'INTERFEROMETRY'
              AND    tsComplete IS NOT NULL
              AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) IN (ECR_Step)
              AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
       UNION
              SELECT ROUND(((86400 * (TO_DATE(SUBSTR(tsComplete, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4)  "Elapsed Total"
              FROM   LR_MEASURES.INERFEROMETRY@EPICENTRE a,
                     LR_MEASURES.PHOTOMETRY@EPICENTRE c
              WHERE  a.OBID = c.LEFT
              AND    c.CLASS1 = 'INTERFEROMETRY'
              AND    tsComplete IS NOT NULL
              AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) IN (ECR_Step)
              AND    TO_DATE(SUBSTR(tsInitiate, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
       )
       GROUP BY 1;

BEGIN

   OPEN dataSummary; 
   FETCH dataSummary INTO dwellTimeSummary.Min_Outlier,
                          dwellTimeSummary.Lower_Quartile,
                          dwellTimeSummary.Median_Value,
                          dwellTimeSummary.Upper_Quartile,
                          dwellTimeSummary.Max_Outlier,
                          dwellTimeSummary.Std_Average,
                          dwellTimeSummary.IQRange;

    
   --IF dataSummary%notfound THEN
      --dwellTimeSummary := { 0, 0, 0, 0, 0, 0, 0 };
   --END IF;
    
   RETURN dwellTimeSummary;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        dwellTimeSummary.Min_Outlier := 0;
        dwellTimeSummary.Lower_Quartile := 0;
        dwellTimeSummary.Median_Value := 0;
        dwellTimeSummary.Upper_Quartile := 0;
        dwellTimeSummary.Max_Outlier := 0;
        dwellTimeSummary.Std_Average := 0;
        dwellTimeSummary.IQRange := 0;
       NULL;
     -- WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       -- RAISE;
       -- RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END ECR_DWELL_SUMMARY;
/

Open in new window


I should be able to pass this func a [Process Step] (string), a [DayRange] (number), and an [Anchor Date] (string) and get back a structure containing the seven calculated values.  But I cannot, and this time I know that I missed something, somewhere...

Thanks for your time and attention,

- The Lurking LongFist
Avatar of paquicuba
paquicuba
Flag of United States of America image

Get the idea with this example:

drop type dwellTimeSummary_typ;
/
        DROP TYPE LONGFIST.FIVE_POINT_SUMMARY;

        CREATE OR REPLACE TYPE Five_Point_Summary AS OBJECT
        (
          Min_Outlier NUMBER(14,4),
          Lower_Quartile NUMBER(14,4),
          Median_Value NUMBER(14,4),
          Upper_Quartile NUMBER(14,4),
          Max_Outlier NUMBER(14,4),
          Std_Average NUMBER(14,4),
          IQRange NUMBER(14,4)
        );
        /
       
create or replace type dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/

       
CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY
RETURN dwellTimeSummary_typ
IS

dwellTimeSummary dwellTimeSummary_typ;

BEGIN

   SELECT FIVE_POINT_SUMMARY(
              1.0,
              1.0,
              1.0,
              1.0,
              1.0,
              1.0,
              1.0
              )
       bulk collect INTO dwellTimeSummary              
       FROM
       DUAL;

   
   --IF dataSummary%notfound THEN
      --dwellTimeSummary := { 0, 0, 0, 0, 0, 0, 0 };
   --END IF;
   
   RETURN dwellTimeSummary;


END LRI_DWELL_SUMMARY;
/

select * from table(LRI_DWELL_SUMMARY)
Try yours. If it doesn't work we can fix it:


drop type dwellTimeSummary_typ;
/
DROP TYPE FIVE_POINT_SUMMARY;
/
CREATE OR REPLACE TYPE Five_Point_Summary AS OBJECT
(
  Min_Outlier NUMBER(14,4),
  Lower_Quartile NUMBER(14,4),
  Median_Value NUMBER(14,4),
  Upper_Quartile NUMBER(14,4),
  Max_Outlier NUMBER(14,4),
  Std_Average NUMBER(14,4),
  IQRange NUMBER(14,4)
);
/
create or replace type dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/

CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY( Process_Step IN VARCHAR2, DayRange IN NUMBER, AnchorDate IN VARCHAR2 )
RETURN LONGFIST.FIVE_POINT_SUMMARY
IS

dwellTimeSummary dwellTimeSummary_typ;

BEGIN

   SELECT    
         FIVE_POINT_SUMMARY
              (
              LOWER_OUTLIER,
              LOWER_QUARTILE,
              MEDIAN_VALUE,
              UPPER_QUARTILE,
              UPPER_OUTLIER,
              STANDARD_AVERAGE,
              INTERQUARTILE_RANGE
              )
    BULK COLLECT INTO dwellTimeSummary
    FROM
    (
              MIN("Elapsed Total") LOWER_OUTLIER,
              PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total") LOWER_QUARTILE,
              PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY "Elapsed Total") MEDIAN_VALUE,
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") UPPER_QUARTILE,
              MAX("Elapsed Total") UPPER_OUTLIER,
              ROUND(AVG("Elapsed Total"), 4) STANDARD_AVERAGE,
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total") INTERQUARTILE_RANGE
       FROM
       (
              SELECT ROUND(((86400 * (TO_DATE(SUBSTR(tsComplete, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4)  "Elapsed Total"
              FROM   LR_MEASURES.INERFEROMETRY@EPICENTRE a,
                     LR_MEASURES.SPECTROSCOPY@EPICENTRE b
              WHERE  a.OBID = b.LEFT
              AND    b.CLASS1 = 'INTERFEROMETRY'
              AND    tsComplete IS NOT NULL
              AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) IN (ECR_Step)
              AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
       UNION
              SELECT ROUND(((86400 * (TO_DATE(SUBSTR(tsComplete, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4)  "Elapsed Total"
              FROM   LR_MEASURES.INERFEROMETRY@EPICENTRE a,
                     LR_MEASURES.PHOTOMETRY@EPICENTRE c
              WHERE  a.OBID = c.LEFT
              AND    c.CLASS1 = 'INTERFEROMETRY'
              AND    tsComplete IS NOT NULL
              AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) IN (ECR_Step)
              AND    TO_DATE(SUBSTR(tsInitiate, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
       )
       GROUP BY 1
       );

   
   IF dwellTimeSummary IS EMPTY THEN
      dwellTimeSummary := dwellTimeSummary_typ(FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   END IF;
   
   RETURN dwellTimeSummary;

   EXCEPTION
      WHEN OTHERS THEN
      RAISE;
END LRI_DWELL_SUMMARY;
/
Is your "GROUP BY 1" working? If it's, you're not grouping by column 1, you are just simply grouping by number 1. You can use the column position only in the ORDER BY clause.
Avatar of Gene Moody

ASKER

Rearranged the FUNCTION to contain the SQL SELECT statement internally;

       SELECT MIN("Elapsed Total") LOWER_OUTLIER,
              PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total") LOWER_QUARTILE,
              PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY "Elapsed Total") MEDIAN_VALUE,
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") UPPER_QUARTILE,
              MAX("Elapsed Total") UPPER_OUTLIER,
              ROUND(AVG("Elapsed Total"), 4) STANDARD_AVERAGE,
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total") INTERQUARTILE_RANGE
              BULK COLLECT INTO dwellTimeSummary
       FROM

Open in new window


...instead of opening a cursor.  Okay so far, but now I encounter the following ORACLE SYNTAX ERROR during compile:

PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

Open in new window


...I have the feeling that we're getting closer.  I must admit to having never used the BULK COLLECT ever before, however...
GROUP BY was supposed to be grouping by the first column: it is an artifact from when the SQL statement (originally) returned sixteen columns of data - you know, to make sure that we were identifying the right stuff to summarize.  I't been removed.

If I understand this, I want to surround my SQL statement with another SELECT, wherein I use the BULK COLLECT, right?
Take a look at my example and you'll find that I wrapped around your SQL, so you can use the column aliases.

Change what you posted above to this:


SELECT FIVE_POINT_SUMMARY
              (
              MIN("Elapsed Total"),
              PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total"),
              PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY "Elapsed Total"),
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total"),
              MAX("Elapsed Total"),
              ROUND(AVG("Elapsed Total"), 4),
              PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY "Elapsed Total") - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY "Elapsed Total")
              )
              BULK COLLECT INTO dwellTimeSummary
       FROM
...
...


I must admit to having never used the BULK COLLECT ever before, however...

BULK COLLECT is the way to go unless you can achieve the same result using plain SQL. Otherwise, don't let anyone tell you that BULK COLLECT is not the fastest way to fetch a resultset into a collection.
If I understand this, I want to surround my SQL statement with another SELECT, wherein I use the BULK COLLECT, right?

You don't have to, but I did it for clarity. The example above removes the column aliases.

Also, you have to use your object constructor FIVE_POINT_SUMMARY().
Head scratchin' time.  We're almost there.  I re-structured the whole shebang to look like this:

CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY
( Process_Step IN VARCHAR2, DayRange IN NUMBER, AnchorDate IN VARCHAR2 )
RETURN LONGFIST.FIVE_POINT_SUMMARY
IS
dwellTimeSummary LONGFIST.FIVE_POINT_SUMMARY;

BEGIN

       SELECT
          LONGFIST.FIVE_POINT_SUMMARY
               (
                 MIN(ELAPSED_DAYS),
                 PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS),
                 PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY ELAPSED_DAYS),
                 PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS),
                 MAX(ELAPSED_DAYS),
                 ROUND(AVG(ELAPSED_DAYS), 4),
                 PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS)
               )
       BULK COLLECT INTO dwellTimeSummary
       FROM
       (
       SELECT              
             MIN(ELAPSED_DAYS) LOWER_OUTLIER,
             PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) LOWER_QUARTILE,
             PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY ELAPSED_DAYS) MEDIAN_VALUE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) UPPER_QUARTILE,
             MAX(ELAPSED_DAYS) UPPER_OUTLIER,
             ROUND(AVG(ELAPSED_DAYS), 4) STANDARD_AVERAGE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) INTERQUARTILE_RANGE
          FROM
          ( 
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.SPECTROSCOPY@EPICENTRE b 
                 WHERE  a.OBID = b.LEFT
                 AND    b.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          UNION
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.PHOTOMETRY@EPICENTRE c
                 WHERE  a.OBID = c.LEFT
                 AND    c.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          )
       );          
       

   IF dwellTimeSummary IS EMPTY THEN 
      dwellTimeSummary := dwellTimeSummary_typ(LONGFIST.FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   END IF;

    
   RETURN dwellTimeSummary;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        dwellTimeSummary.Min_Outlier := 0;
        dwellTimeSummary.Lower_Quartile := 0;
        dwellTimeSummary.Median_Value := 0;
        dwellTimeSummary.Upper_Quartile := 0;
        dwellTimeSummary.Max_Outlier := 0;
        dwellTimeSummary.Std_Average := 0;
        dwellTimeSummary.IQRange := 0;
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END LRI_DWELL_SUMMARY;
/

Open in new window


...but the silly thing marks line 38 and says:

     PL/SQL: ORA-00904: "ELAPSED_DAYS": invalid identifier

Open in new window


...that would be the last line in the FIVE_POINT_SUMMARY constructor, I believe.  I'm not sure what to think - I've simplified the names (carry-over from the previous construct - I'm sure you get the picture...) and everything.  Not sure where to take this next.  Maybe I'm missing some punctuation?

I had to add the SELECT into the first inner grouping, or it continually erred out on MIN(...
Eliminate the NO_DATA_FOUND exception. BULK COLLECT doesn't throw the NO_DATA_FOUND exception. All you have to so is to check whether the collection is EMPTY and we have already done that in the following section:

IF dwellTimeSummary IS EMPTY THEN
      dwellTimeSummary := dwellTimeSummary_typ(LONGFIST.FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   END IF;


So, try this:

CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY
( Process_Step IN VARCHAR2, DayRange IN NUMBER, AnchorDate IN VARCHAR2 )
RETURN LONGFIST.FIVE_POINT_SUMMARY
IS
dwellTimeSummary LONGFIST.FIVE_POINT_SUMMARY;

BEGIN

       SELECT
          LONGFIST.FIVE_POINT_SUMMARY
               (
                 MIN(ELAPSED_DAYS),
                 PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS),
                 PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY ELAPSED_DAYS),
                 PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS),
                 MAX(ELAPSED_DAYS),
                 ROUND(AVG(ELAPSED_DAYS), 4),
                 PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS)
               )
       BULK COLLECT INTO dwellTimeSummary
       FROM
       (
       SELECT              
             MIN(ELAPSED_DAYS) LOWER_OUTLIER,
             PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) LOWER_QUARTILE,
             PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY ELAPSED_DAYS) MEDIAN_VALUE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) UPPER_QUARTILE,
             MAX(ELAPSED_DAYS) UPPER_OUTLIER,
             ROUND(AVG(ELAPSED_DAYS), 4) STANDARD_AVERAGE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) INTERQUARTILE_RANGE
          FROM
          (
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.SPECTROSCOPY@EPICENTRE b
                 WHERE  a.OBID = b.LEFT
                 AND    b.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          UNION
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.PHOTOMETRY@EPICENTRE c
                 WHERE  a.OBID = c.LEFT
                 AND    c.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          )
       );          
       

   IF dwellTimeSummary IS EMPTY THEN
      dwellTimeSummary := dwellTimeSummary_typ(LONGFIST.FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   END IF;

   
   RETURN dwellTimeSummary;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END LRI_DWELL_SUMMARY;
/
So, I keep getting this "Invalid Indentifier" error, so I decide to change the Constructor to look like the following:
          LONGFIST.FIVE_POINT_SUMMARY
               (
                 MIN(1),
                 PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY 1),
                 PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY 1),
                 PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY 1),
                 MAX(1),
                 ROUND(AVG(1), 4),
                 PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY 1)
               )

Open in new window


...at which point it give me the other error:
     PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

Open in new window


...I must be missing something pretty simple.  Maybe BULK COLLECT knows somehow that the return set will only be a single "row" in size?
I see. you wrapped the SQL, but you're not using the column aliases you created, you're trying to re-create them instead and that's unnecessary and not possible:

Try this:

CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY
( Process_Step IN VARCHAR2, DayRange IN NUMBER, AnchorDate IN VARCHAR2 )
RETURN LONGFIST.FIVE_POINT_SUMMARY
IS
dwellTimeSummary LONGFIST.FIVE_POINT_SUMMARY;

BEGIN

       SELECT    
             FIVE_POINT_SUMMARY
                  (
                  LOWER_OUTLIER,
                  LOWER_QUARTILE,
                  MEDIAN_VALUE,
                  UPPER_QUARTILE,
                  UPPER_OUTLIER,
                  STANDARD_AVERAGE,
                  INTERQUARTILE_RANGE
                  )
        BULK COLLECT INTO dwellTimeSummary
       FROM
       (
       SELECT              
             MIN(ELAPSED_DAYS) LOWER_OUTLIER,
             PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) LOWER_QUARTILE,
             PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY ELAPSED_DAYS) MEDIAN_VALUE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) UPPER_QUARTILE,
             MAX(ELAPSED_DAYS) UPPER_OUTLIER,
             ROUND(AVG(ELAPSED_DAYS), 4) STANDARD_AVERAGE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) INTERQUARTILE_RANGE
          FROM
          (
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.SPECTROSCOPY@EPICENTRE b
                 WHERE  a.OBID = b.LEFT
                 AND    b.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          UNION
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.PHOTOMETRY@EPICENTRE c
                 WHERE  a.OBID = c.LEFT
                 AND    c.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          )
       );          
       

   IF dwellTimeSummary IS EMPTY THEN
      dwellTimeSummary := dwellTimeSummary_typ(LONGFIST.FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   END IF;

   
   RETURN dwellTimeSummary;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END LRI_DWELL_SUMMARY;
/
Closing in on the result: I kept getting the "PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list" error at "BULK COLLECT INTO..." so I took a chance and removed the "BULK" identifier.  Tat seems to have placated ORACLE, for I only have one remaining error: "PLS-00306: wrong number or types of arguments in call to 'IS EMPTY'" pointing at "IF dwellTimeSummary IS EMPTY THEN..." - if I comment that segment out it compiles (once again).

But I think I like having that check in-line, since I -1'ed those values, a distinct flag that something, somewhere has gone terribly wrong.

BTW: Thank you for your kind patience with me - as I said before I'm still learning.  My previous ORACLE experience was with 9i, and apparently I'm way behind.  (We never did anything like this with 9, let me tell you...)
If you follow this example, you can get yours to work:

drop type dwellTimeSummary_typ;
/
DROP TYPE FIVE_POINT_SUMMARY;
/
CREATE OR REPLACE TYPE Five_Point_Summary AS OBJECT
(
  Min_Outlier NUMBER(14,4),
  Lower_Quartile NUMBER(14,4),
  Median_Value NUMBER(14,4),
  Upper_Quartile NUMBER(14,4),
  Max_Outlier NUMBER(14,4),
  Std_Average NUMBER(14,4),
  IQRange NUMBER(14,4)
);
/        
create or replace type dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/

       
CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY( p_dummy dual.dummy%type)
RETURN dwellTimeSummary_typ
IS

dwellTimeSummary dwellTimeSummary_typ;

BEGIN

   SELECT FIVE_POINT_SUMMARY(
              col1,
              col2,
              col3,
              col4,
              col5,
              col6,
              col7
              )
       bulk collect INTO dwellTimeSummary
       from
       (
       select
        1.0 col1,
        2.0 col2,
        3.0 col3,
        4.0 col4,
        5.0 col5,
        6.0 col6,
        7.0 col7
       FROM
       DUAL
       where dummy = upper(p_dummy)
       );

   IF dwellTimeSummary IS EMPTY THEN
      dwellTimeSummary := dwellTimeSummary_typ(FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   END IF;
   
   RETURN dwellTimeSummary;

END LRI_DWELL_SUMMARY;
/

select * from table(LRI_DWELL_SUMMARY('X'))
/
select * from table(LRI_DWELL_SUMMARY('Y'))
/
Look at what I'm doing here:


drop type dwellTimeSummary_typ;   -- Drop the collection TYPE
/
DROP TYPE FIVE_POINT_SUMMARY;   --Drop the OBJECT type
/

-- Create the OBJECT
CREATE OR REPLACE TYPE Five_Point_Summary AS OBJECT
(
  Min_Outlier NUMBER(14,4),
  Lower_Quartile NUMBER(14,4),
  Median_Value NUMBER(14,4),
  Upper_Quartile NUMBER(14,4),
  Max_Outlier NUMBER(14,4),
  Std_Average NUMBER(14,4),
  IQRange NUMBER(14,4)
);
/      

-- Create the nested table( collection) type  
create or replace type dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/
The problem is that you're not paying attention to what I'm posting and you're changing things your way and they are not going to work your way.
ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The problem is that you're not paying attention to what I'm posting and you're changing things your way and they are not going to work your way.

I can well understand that they don't work my way: I guess I need to understand "why".  The TYPE hasn't changed since the beginning of this ordeal - is it really necessary to DROP it and REBUILD IT?  Have I been approaching this from the wrong angle?

I've left the FIVE_POINT_SUMMARY TYPE totally alone during this entire adventure, and have instead been focusing on the FUNCTION.  Is that the problem?
<Almost simultaneous posts:>
"Yes, that is the problem..."
Yes, because the OBJECT type along is not going to work. Look at my first posting and you'll see this CREATE statement:

create or replace type dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/

FIVE_POINT_SUMMARY is your OBJECT type, but then you have to create a collection type to hold that object and the collection type is what you use to instantiate new collections from.
is it really necessary to DROP it and REBUILD IT?

No, it's not necessary, I just wanted to show you all the steps...
Hang on a sec: I've gotta' go get permissions to accomplish this feat - it seems that I'm not allowed to perform that particular function at this time.

create or replace type LONGFIST.dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/

Open in new window


ORACLE responds with "ORA-01031: insufficient privileges"

...I'll be back in seconds with an update...
The problem is that you're not paying attention to what I'm posting and you're changing things your way and they are not going to work your way.

I didn't mean to sound angry, I just wanted you to pay attention to every little piece of code since just one wrong declaration here will mess up everything...
Gotta go to vote, I'll check with you later...
I didn't mean to sound angry
Not sounding angry - sounding frustrated.  I can totally understand.  Because I'm the one being obtrusively bone-headed about it.

But here we are.  DBA has scheduled a visit to my location to view the error code: looks like I might should have been granted that privilege, too, but it somehow got overlooked.  Will know in a little bit.

Thanks again for your inspirational level of patience!  I'll be back with some news in a few minutes or so...    ...waiting for the DBA, you know..
sounding frustrated

ha ha
DBA just left.  He set me straight.  And we were *both* almost there.

Here's what it looks like:

I cannot do this:
create or replace type LONGFIST.dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY;
/

Open in new window


...because it is not allowed outside of PL/SQL.  (No, I'm not entirely sure what that really means - I thought the FUNCTION was PL/SQL, but apparently I'm confuzzled.  Happens a lot, these days.  Might just be part of gettin' old.  I dunno...

So, he moves in, starts checking over my/your ("our") work, and it comes down to two words that he says don't belong: "BULK COLLECT" --- okay, I ask for reasons (I seek first to understand), and he tells me that --- because we're "cooking down" a thousand or so records into a single row of data, and we will never get more than a single row of data --- we have no need to create a table off a type (which I can only do within a PL/SQL FUNCTION or STORED PROCEDURE or something, apparently) to store more than one row of anything.  So he edits the final work a little, and it ends up looking like this:

CREATE OR REPLACE FUNCTION LRI_DWELL_SUMMARY
( Process_Step IN VARCHAR2, DayRange IN NUMBER, AnchorDate IN VARCHAR2 )
RETURN LONGFIST.FIVE_POINT_SUMMARY
IS
dwellTimeSummary LONGFIST.FIVE_POINT_SUMMARY;

BEGIN

       SELECT
          LONGFIST.FIVE_POINT_SUMMARY
               (
                 LOWER_OUTLIER,
                 LOWER_QUARTILE,
                 MEDIAN_VALUE,
                 UPPER_QUARTILE,
                 UPPER_OUTLIER,
                 STANDARD_AVERAGE,
                 INTERQUARTILE_RANGE                 
               )
       INTO dwellTimeSummary
       FROM
       (
       SELECT              
             MIN(ELAPSED_DAYS) LOWER_OUTLIER,
             PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) LOWER_QUARTILE,
             PERCENTILE_DISC(.5)  WITHIN GROUP (ORDER BY ELAPSED_DAYS) MEDIAN_VALUE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) UPPER_QUARTILE,
             MAX(ELAPSED_DAYS) UPPER_OUTLIER,
             ROUND(AVG(ELAPSED_DAYS), 4) STANDARD_AVERAGE,
             PERCENTILE_DISC(.75) WITHIN GROUP (ORDER BY ELAPSED_DAYS) - PERCENTILE_DISC(.25) WITHIN GROUP (ORDER BY ELAPSED_DAYS) INTERQUARTILE_RANGE
          FROM
          ( 
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.SPECTROSCOPY@EPICENTRE b 
                 WHERE  a.OBID = b.LEFT
                 AND    b.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          UNION
                 SELECT ROUND(((86400 * (TO_DATE(SUBSTR(timestampout, 0, 19), 'yyyy/mm/dd HH24:MI:SS') - TO_DATE(SUBSTR(timestampin, 0, 19), 'yyyy/mm/dd HH24:MI:SS'))) / 86400), 4) ELAPSED_DAYS
                 FROM   LR_MEASURES.INTERFEROMETRY@EPICENTRE a,
                        LR_MEASURES.PHOTOMETRY@EPICENTRE c
                 WHERE  a.OBID = c.LEFT
                 AND    c.CLASS1 = 'INTERFEROMETRY'
                 AND    timestampout IS NOT NULL
                 AND    LPAD(RTRIM(SUBSTR(workdesc, 6, 3)),3) = Process_Step
                 AND    TO_DATE(SUBSTR(TIMESTAMPIN, 0, 10), 'yyyy/mm/dd') >= (TO_DATE(AnchorDate, 'mm/dd/yyyy') - DayRange)
          )
       );          
       

   --IF dwellTimeSummary IS EMPTY THEN 
   --   dwellTimeSummary := dwellTimeSummary_typ(LONGFIST.FIVE_POINT_SUMMARY(0,0,0,0,0,0,0));
   --END IF;

    
   RETURN dwellTimeSummary;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END LRI_DWELL_SUMMARY;
/

Open in new window


...and in spite of me, it compiles.  I then throw forth the challenge - it was giving me errors before - will it execute?  (Remember, that's what drove me here in the first place, right?)  And he flips over to an empty SQL Tab, and types the following:

SELECT LONGFIST.LRI_DWELL_SUMMARY('375', 30, '11/06/2012') FROM DUAL;

Open in new window


...and blast if that devilish thing doesn't spit out a five-point-summary of all of the isolated intergalactic scans within that filter grade within that time range!  Yeah, it took a few seconds - but it was soooo worth it!  Except it made me look - well - I am ignorant, there's no escaping that.

So we both win - but you get the points.  You got me so close to the end range product we could reach out and slap it --- but it all hung up over create or replace type LONGFIST.dwellTimeSummary_typ as table of FIVE_POINT_SUMMARY; - and the fact that we are summarizing hundreds to thousands of rows of data (inefficiently stored; I know - but that's a different kettle of fish) into a single row of data.

For what it's worth - you were right, I needed the CONSTRUCTOR.  You were also right about punching the data into the return value, except that we're not pulling rows of data, but a single row, which can fit nicely within the structure provided.

This has been a rather successful conclusion to a grand experiment that I was beginning to think I would have to abandon.  Thank you for seeing me through it without having an aneurism.  I appreciate that!
For what it's worth - you were right, I needed the CONSTRUCTOR.  You were also right about punching the data into the return value, except that we're not pulling rows of data, but a single row, which can fit nicely within the structure provided.

Removed the BULK COLLECT - leaving it with just "INTO dwellTimeSummary" (after initializing the TYPE Constructor), and it now returns the proper data, in the proper order, to the calling processes.
Great it worked buddy!

Now I understand exactly what you wanted.