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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
