RE: How to pass cursor to a function Repost:left out important info

From: <Rick_Cale_at_teamhealth.com>
Date: Mon, 28 Jan 2002 06:05:46 -0800
Message-ID: <F001.003FCE36.20020128045542@fatcity.com>

Hi DBAs,

I have the follow code snippets and when I compile I get an error adt_visits_rec.patient_site_id must be declared. What do I need to pass and how so the function knows existance of adt_visits_rec.

In the for loop that opens the cursor of course it knows about adt_visits_rec. I do not want to put all the code in that one procedure. For example I would like the function get_idx_hospital_id below
be able to have scope of the curso

Thanks
Rick

CREATE OR REPLACE PACKAGE Patient_Registration_Pkg AS

   CURSOR adt_visits_cur(num_of_days IN INTEGER) IS

     SELECT adt_seq_id,patient_site_id,admission_datetime
        FROM adtlite.pv2
     WHERE (TO_CHAR(admission_datetime,'DD-MON-YYYY')
             = TO_CHAR((SYSDATE-num_of_days),'DD-MON-YYYY'));

rest of package specification
........

CREATE OR REPLACE PACKAGE BODY Patient_Registration_Pkg AS

PROCEDURE load_PatReg_table(num_of_days_to_go_back IN INTEGER) IS
-- v_rowid ROWID;

       v_idx_hospital_id VARCHAR2(5);

     BEGIN
        FOR adt_visits_rec IN adt_visits_cur(num_of_days_to_go_back) LOOP
           v_idx_hospital_id := get_idx_hospital_id();
           INSERT INTO patreg.patient_registration_info
                        (patient_site_id,
                      adt_seq_id,
                         date_of_service,
                         idx_hospital_id)
           VALUES       (adt_visits_rec.patient_site_id,
                         adt_visits_rec.adt_seq_id,
                         adt_visits_rec.admission_datetime,
                         v_idx_hospital_id);

rest of this procedure

Start of function

 FUNCTION get_idx_hospital_id

    RETURN VARCHAR2 IS
    v_idx_hospital_id VARCHAR2(5);
 BEGIN
    SELECT idx_hospital_id
    INTO v_idx_hospital_id
    FROM patreg.idx_hospid_lookup
    WHERE site_id = SUBSTR(adt_visits_rec.patient_site_id,1,6);

    RETURN v_idx_hospital_id;

        EXCEPTION
           WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLCODE)||' - '||

SUBSTR(SQLERRM,1,200)); END get_idx_hospital_id;

.....
rest of package body

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jan 28 2002 - 08:05:46 CST