Oracle 10 G - Procedure Package for Crystal Reports 2

[nkshah]

Hi friends,

i developed one complex query with parameters that satisfy my business requirments.

Now, i want to convert this query with parameter into Oracle 10 G Procedure.

i want to use this Procedure's result sets into Crystal Reprots.

i did googling, most of people suggesting, to declare Cursor inside Procedure and then use this Cursor
inside Oracle Package.

But, i fundamentally not understand why to use Cursor in Procedure and Package ?

any kind of suggestion is well come.

Thanks.

Happy New Year to all.

Thanks.

 

[Turkbear]

Hi,
For Crystal to 'see' the result set your SP creates it must be defined as an OUT value with a REFCURSOR type..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nkshah]

HI,

is it mandatory to defined Ref Cursor in Stored Procedure and Package ?

can i simplly developed Stored Procedure and add that Procedure in Crystal using Database Expert ?

Thanks.

 

[oradba101]

Yes, because Crystal will only recognize a table or a view so in order to use a package (Crystal will not work with a procedure) it has to return a REF CURSOR which Crystal will interpret as a table.

Regards,


William Chadbourne
Programmer/Analyst

 

[nkshah]

Thanks for giving me reply.

But, still my concept is not clear yet.

i will developed oracle procedure and defined REF CURSOR inside oracle procedure.

now, i will add this oracle procedure in crystal report.

my assumption is that, is it working this way ?

without declaring REF CURSOR in package ?

Let me know, i want to clear my fundamental and all doubt.

Thanks in advance.

 

[oradba101]

I have a package I created to do this for a Crystal Report which I will upload when I get to work. I'll add comments to explain what is going on.

Regards,


William Chadbourne
Programmer/Analyst

 

[oradba101]

Greetings, nkshah

This package is used in a Crystal Report for the purpose of allowing the user to select how many "bars" a user wishes to see in a bar graph report.

This is the package spec:

CREATE OR REPLACE PACKAGE ANALYTE_HISTOGRAM
/***********************************************************
      NAME: LABWORKS.ANALYTE_HISTOGRAM
   PURPOSE:    In order to use a stored procedure in   Crystal Reports it must be
               defined within a package and must use a REF CURSOR as the input
               parameter.

   REVISIONS:
   Ver        Date        Author              Description
   ---------  ----------  ---------------     --------------------------------
   1.0        04/19/2008  William Chadbourne   1. Created this package.
***********************************************************/
AS TYPE hist_type IS REF CURSOR  ;
   PROCEDURE histogram_cnt
      (InputBin     IN NUMBER,
       InputLoc     IN VARCHAR2,
       InputAnl     IN VARCHAR2,
       InputBegDate IN DATE,
       InputEndDate IN DATE,
       hist_cursor  IN OUT hist_type) ;
END ANALYTE_HISTOGRAM;

The first five IN parameters are the inputs that have been put into the Crystal Report by the user.

The "hist_cursor" is the IN OUT cursor that will be returned as a table to Crystal Reports.

This is the package body:

CREATE OR REPLACE PACKAGE BODY analyte_histogram AS
   PROCEDURE histogram_cnt
      (InputBin     IN NUMBER,
       InputLoc     IN VARCHAR2,
       InputAnl     IN VARCHAR2,
       InputBegDate IN DATE,
       InputEndDate IN DATE,       
       hist_cursor  IN OUT hist_type)
/******************************************************************************
   NAME:       histogram_cnt
   PURPOSE:    To define the number of data points (bind) a user can define on
               a Crystal Reports chart in the Analyte Histogram report.

   REVISIONS:
   Ver        Date        Author              Description
   ---------  ----------  ---------------     --------------------------------
   1.0        04/16/2008  William Chadbourne  1. Created this procedure.

   NOTES:

******************************************************************************/
IS
BEGIN
   DECLARE
      v_bins               NUMBER ;
      v_divisor            NUMBER ;
      v_last_acode         view_histogram.acode%TYPE;
      v_last_loccode       view_histogram.loccode%TYPE;
      v_last_analyte       view_histogram.analyte%TYPE;
      v_last_anlname       view_histogram.anlname%TYPE;
      v_last_sample_date   view_histogram.sample_date%TYPE;
      v_last_casnumb       view_histogram.casnumb%TYPE;
      v_x_ins              NUMBER ;

      -- Get the minimum and maximume values of the result column
      -- by loccode and Analyte.
      CURSOR c_minmax IS
      WITH temp AS
      (
      SELECT loccode, 
             analyte,
             sample_date,
             result
        FROM view_histogram
       WHERE INSTR(InputLoc,LocCode) > 0
         AND analyte = InputAnl
         AND TO_DATE(sample_date) BETWEEN InputBegDate AND InputEndDate
         AND acode IN('$SCAN_AMBIENT','$SCAN_AMBIENT_ME','$SCAN_AMBIENT_UC','$SIM_AMBIENT',
                      '$SIM_AMBIENT_RI','$TO-15_AMBIENT_EPA')
      )
      SELECT loccode, 
             analyte,
             MIN(DISPLAY_AS_NUM(RESULT)) AS min_result,
             MAX(DISPLAY_AS_NUM(RESULT)) AS max_result
        FROM temp
       GROUP BY loccode, analyte
;

      -- Retrieve the columns needed for the report
      CURSOR c_temp IS
      SELECT acode,
             DISPLAY_AS_NUM(result) AS result,
             loccode,
             analyte,
             anlname,
             sample_date,
             sampno,
             casnumb
        FROM view_histogram
       WHERE INSTR(InputLoc,LocCode) > 0
         AND analyte = InputAnl
         AND TO_DATE(sample_date) BETWEEN InputBegDate AND InputEndDate
         AND acode IN('$SCAN_AMBIENT','$SCAN_AMBIENT_ME','$SCAN_AMBIENT_UC','$SIM_AMBIENT',
                      '$SIM_AMBIENT_RI','$TO-15_AMBIENT_EPA') ;

       
   BEGIN
      -- Empty the HISTOGRAM_TEMP table
      EXECUTE IMMEDIATE('TRUNCATE TABLE histogram_temp') ;

      -- Pass the value entered by the user to the v_bins variable
      v_bins := InputBin ;

      -- Execute the first loop which determines the divisor and then
      -- computes the values based upon the user selection in the
      -- InputBins parameter.
      FOR r_minmax IN c_minmax

      -- Populate the DIVISOR variable with the result of subtracting
      -- the minimum value from the maximum value in the RESULT column
      LOOP
         CASE
            WHEN r_minmax.max_result - r_minmax.min_result = 0 THEN
               v_divisor := 0 ;
            ELSE
               v_divisor := (r_minmax.max_result - r_minmax.min_result) / v_bins ;
         END CASE ;


         -- Execute the second loop which inserts the value of the
         -- v_x_ins variable into the range column of the
         -- histogram_temp table.
         FOR r_temp IN c_temp

         LOOP

            IF r_temp.loccode = r_minmax.loccode AND
               r_temp.analyte = r_minmax.analyte THEN

               -- RBE - 2008 MAY 01 - Save off values to use in dummy records
               v_last_acode       := r_temp.acode;
               v_last_loccode     := r_temp.loccode;
               v_last_analyte     := r_temp.analyte;
               v_last_anlname     := r_temp.anlname;
               v_last_sample_date := r_temp.sample_date;
               v_last_casnumb     := r_temp.casnumb;

               /* RBE - 2008 APR 30 - New formula */
               IF (v_divisor>0) THEN
                  IF (r_temp.result=r_minmax.min_result) then
                     /* Use v_divisor+r_minmax.min_result to set v_x_ins 
                        to the upper bounds of the first range.
                        Use r_minmax.min_result to to set v_x_ins to the 
                        upper bounds of the first range. */
                     v_x_ins := v_divisor+r_minmax.min_result;
                  ELSE
                     IF ((r_temp.result<=r_minmax.max_result) AND
                         (r_temp.result>r_minmax.min_result)) THEN
                        /* Use ceil() to set v_x_ins to the upper bounds 
                           of the range that includes r_temp.result.
                           Use floor() to to set v_x_ins to the upper 
                           bounds of the range that includes r_temp.result. */
                        v_x_ins := (((ceil((r_temp.result-r_minmax.min_result)/v_divisor))*v_divisor)+r_minmax.min_result);
                     ELSE
                        v_x_ins := 0;
                     END IF;
                  END IF;
                ELSE
                  v_x_ins := 0;
               END IF;
            
               -- Insert the records into the table
               INSERT INTO histogram_temp(acode,
                                          result,
                                          loccode,
                                          analyte,
                                          anlname,
                                          sample_date,
                                          sampno,
                                          casnumb,
                                          min_result,
                                          max_result,
                                          range)
                                   VALUES(r_temp.acode,
                                          r_temp.result,
                                          r_temp.loccode,
                                          r_temp.analyte,
                                          r_temp.anlname,
                                          r_temp.sample_date,
                                          r_temp.sampno,
                                          r_temp.casnumb,
                                          r_minmax.min_result,
                                          r_minmax.max_result,
                                          v_x_ins) ;                                          

            END IF ;
         END LOOP ;
         
         -- RBE - 2008 MAY 01 - Insert one dummy record for each range value.
         -- Set v_x_ins to r_minmax.min_result for lower end of range.
         -- Set v_x_ins to r_minmax.min_result+v_divisor for upper end of range.
         v_x_ins := r_minmax.min_result+v_divisor;
         LOOP
         -- Use >= for lower end of range.
         -- Use > for upper end of range.
            EXIT WHEN v_x_ins>r_minmax.max_result;

            -- Use saved values in dummy records
            INSERT INTO histogram_temp(acode,
                                       result,
                                       loccode,
                                       analyte,
                                       anlname,
                                       sample_date,
                                       sampno,
                                       casnumb,
                                       min_result,
                                       max_result,
                                       range)
                                VALUES(v_last_acode,
                                       null, -- to mark the dummy record
                                       v_last_loccode,
                                       v_last_analyte,
                                       v_last_anlname,
                                       v_last_sample_date,
                                       null, -- to mark the dummy record
                                       v_last_casnumb,
                                       r_minmax.min_result,
                                       r_minmax.max_result,
                                       v_x_ins) ;                                          
            v_x_ins := v_x_ins+v_divisor;
         END LOOP ;
      END LOOP ;
     
      -- This is the result returned to Crystal Reports
      OPEN hist_cursor 
       FOR
    SELECT *
      FROM histogram_temp ; 
   END ;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
   WHEN OTHERS THEN
      RAISE;
END histogram_cnt ;

END ANALYTE_HISTOGRAM;

Notice the line that starts with "OPEN hist_cursor". This is the part that is needed to return the result to Crystal Reports.

William Chadbourne
Programmer/Analyst