Using Oracle procedure as data source... 1

[RobPierre]

Two questions:
1)
Apparently the only way to get a recordset out of an Oracle stored procedure is to use a ref cursor (a cursor variable).

Given this fact, is it possible to use an Oracle stored procedure as the data source in CR? If so, how do you get the data from the cursor variable to CR?

Please feel free to tell me I'm totally off base here or if there is an easier way to go about this that I haven't mentioned.

2)
Can I access the database directly from CR without using ODBC?

I am activating CR from VB on a WinNT4 client to an HP Unix server running Oracle 8.1.6.

Thanks very much,

Rob.

 

[AdHoc]

You need to write your procedure the way Crystal wants it. Then you can base your new report on one of your procedures instead of a table or view.

Here is an example of a stored procedure for a Crystal Report. You must use an IN OUT parameter which has a type which you have defined as REF CURSOR in one of your stored package specifications. Here the cursor variable has the type defined in the package specification "csm.De_DSS.dsscur".

PROCEDURE De_Hbdis012
(hbdis012_cv IN OUT csm.De_Dss.dsscur,
start_date IN DATE,
end_date   IN DATE)
IS
start_date_notime DATE := TRUNC(start_date);
end_date_notime   DATE := TRUNC(end_date);
BEGIN
  OPEN hbdis012_cv FOR
    SELECT m.facility_id,
           m.unit_c,
           m.unit,
           d.primary_diag_c,
           d.primary_diag,
           COUNT('x') AS pdiag_count
    FROM MOVEMENT m,
         PAT_DIAG_HIST d,
         PATIENT_ADM_HIST ah,
         PATIENT_DIS_HIST dish,
         PATIENT p
    WHERE m.mvmnt_type_c     = 'D'
    AND   m.prg_trt_set_c    = 'I'
    AND   m.facility_id      = d.facility_id
    AND   m.patient_id       = d.patient_id
    AND   m.episode          = d.episode
    AND   m.facility_id      = ah.facility_id
    AND   m.patient_id       = ah.patient_id
    AND   m.episode          = ah.episode
    AND   m.facility_id      = dish.facility_id
    AND   m.patient_id       = dish.patient_id
    AND   m.episode          = dish.episode
    AND   m.facility_id      = p.facility_id
    AND   m.patient_id       = p.patient_id
    AND   d.diag_type_c      = 'D'
    AND   m.mvmnt_date BETWEEN start_date_notime AND end_date_notime
    GROUP BY m.facility_id,
             m.unit_c,
             m.unit,
             d.primary_diag_c,
             d.primary_diag;
END De_Hbdis012;

 

[RobPierre]

Muchos gracias AdHoc!

What do you think about using ADO to access the db rather than ODBC? There shouldn't be a problem, eh?

Thanks again,

Rob.

 

[AdHoc]

The standard where I work calls for stand-alone Crystal Reports against our Oracle Databases so my experience is limited to that. However if you are using Visual Basic or any other front end that can create a recordset there should be no problem doing it that way. I don't think it matters whether you use ADO or something else to make your recordset.

What the code looks like depends on whether you are using the old OCX or new RDC object model. But my experience with Oracle and Crystal is all stand-alone.

 

[kumud]

hey adhoc , I have problem with store procedure when we specify refcursor as IN OUT , at the run time it is asking value for hbdis012_cv (ref cursor).