[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Errors when compiling package  PLS-00201: PLS-00304:

Posted on 2006-04-11
2
Medium Priority
?
3,620 Views
Last Modified: 2012-06-27
Getting the following errors when trying to compile package.
The target table was changed several times columns dropped and added so
the package was altered to reflect these changes and  since then we have had the problem.

PLS-00201: identifier 'EXTRACT_INPATIENT_DATA_FOR_DW' must be declared
PLS-00304: cannot compile body of 'EXTRACT_INPATIENT_DATA_FOR_DW' without its specification



CREATE OR REPLACE PACKAGE BODY Extract_Inpatient_Data_For_Dw
AS
   PROCEDURE EXTRACT_IP (P_START_DATE IN DATE, P_END_DATE IN DATE)
IS
BEGIN

DELETE FROM WDHB_DW_INPATIENTS wdi
WHERE EXISTS (SELECT 'X' FROM TIME_DIMENSIONS td
              WHERE dateonly BETWEEN p_start_date AND p_end_date
                    AND   timeonly = 0
                    AND   td.fiscal_year = wdi.fiscal_year
                    AND   td.fiscal_month = wdi.fiscal_month)
;
COMMIT
;


DECLARE


  b_successful BOOLEAN;

BEGIN


INSERT INTO WDHB_DW_INPATIENTS(
                                                FISCAL_YEAR,
                                             FISCAL_MONTH,
                                             CALENDAR_YEAR,
                                             CALENDAR_MONTH,
                                             CALENDAR_MONTH_NAME,
                                             REPORT_GROUP,
                                             EVENT_TYPE,
                                             FACILITY_CODE,
                                             FACILITY_NAME,
                                             HOSPITAL_CODE,
                                             HOSPITAL_NAME,
                                             CLINICIAN_CODE,
                                             CLINICIAN_NAME,
                                             DEPARTMENT_CODE,
                                             DEPARTMENT_NAME,
                                             CONTRACT_CODE,
                                             CONTRACT_NAME,
                                             DOMICILE_CODE,
                                             DOMICILE_NAME,
                                             DHB,                
                                             WDHB_REGION,
                                             ETHNICITY,                
                                             FUNDER_CODE,
                                             FUNDER_NAME,
                                             DRG_CODE,
                                             DRG_NAME,
                                             DIAGNOSES_CODE,
                                             DIAGNOSES_NAME,
                                             PRODUCT_CODE,
                                             PRODUCT_NAME,
                                             AGE_GROUP,
                                             PATIENT_ID,
                                             GENDER,                      
                                             TYPE,                        
                                             ADMISSION_CODE,
                                             LOS,                      
                                             VOLUME,                    
                                             CASEWEIGHT,                
                                             AGE,                        
                                             DEATH_DATE,
                                             FIRST_NAME,
                                             LAST_NAME
                                             )
                                                
SELECT t.fiscal_year,
       t.fiscal_month,
       t.calendar_year,
       t.calendar_month,
       t.calendar_month_name,
        SUBSTR(c.report_group,1,20) report_group,
       decode(pct.category,'00','IP',pct.category) event_type,
       f.facility_code,
       f.facility_name,
       f.hospital_code,
       f.hospital_name,
       cl.ghw_md_code clinician_code,
       cl.last_name || ' ' || cl.first_name clinician_name,
       h.hsc_group department_code,
       x.hsc_group_description department_name,
       c.contract_code,
       c.description contract_name,
       dom.domicile_code,
       dom.description domicile_name,
       dom.dhb,
       dom.wdhb_region,
       e.ethnicity_group_3 ethnicity,
       c.funder_code,
       u.description funder_name,
       d.code drg_code,
       d.description drg_name,
       dia.icd_code diagnoses_code,
       dia.description diagnoses_name,
       pro.product_code,
       pro.description product_name,
       age.census_age_band age_group,
       s.patient_id,
       p.gender,
       c.TYPE,
       s.admission_code,
       SUM(s.length_of_stay) los,
       p.last_name,
       p.first_name,
       COUNT(1) volume,
       SUM(caseweight) caseweight,
       s.age,
       p.death_date,
       first_name,
       last_name
 FROM SERVICES s,
     CONTRACTS c,
     PATIENTS p,
     DOMICILES dom,
     FACILITIES f,
     DIAGNOSES dia,
     PRODUCTS pro,
     HEALTH_SPECIALTIES h,
     SPECIALTIES x,
     DRGS d,
     CLINICIANS cl,
     TIME_DIMENSIONS t,
     ETHNICITIES e,
     funders u,
     AGE_BANDS age,
       patient_categories pct
WHERE s.current_flag = 'Y'
AND   TRUNC(s.end_date) BETWEEN P_START_DATE
                            AND P_END_DATE
AND   s.contract_key = c.KEY
AND   s.patient_id = p.patient_id
AND   s.domicile_key = dom.KEY (+)
AND   p.ethnicity1_key = e.KEY (+)
AND   s.discharge_facility_key = f.KEY (+)
AND   s.discharge_hsc_key = h.KEY (+)
AND   s.discharge_clinician_key = cl.KEY (+)
AND   s.discharge_drg_key = d.KEY (+)
AND   s.principal_diagnosis_key = dia.KEY (+)
AND   s.principal_procedure_key = pro.KEY (+)
AND   s.end_date_key = t.KEY (+)
AND   s.patient_type = 'IP'
AND   h.hsc_group = x.hsc_group
AND   c.funder_code = u.funder_code (+)
AND   s.age = age.age (+)
and   s.patient_category_key = pct.key (+)
GROUP BY t.fiscal_year,
       t.fiscal_month,
       t.calendar_year,
       t.calendar_month,
       t.calendar_month_name,
        SUBSTR(c.report_group,1,20),
       decode(pct.category,'00','IP',pct.category),
       f.facility_code,
       f.facility_name,
       f.hospital_code,
       f.hospital_name,
       cl.ghw_md_code,
       cl.last_name || ' ' || cl.first_name,
       h.hsc_group,
       x.hsc_group_description,
       c.contract_code,
       c.description,
       dom.domicile_code,
       dom.description,
       dom.dhb,
       dom.wdhb_region,
       e.ethnicity_group_3,
       c.funder_code,
       u.description,
       d.code,
       d.description,
       dia.icd_code,
       dia.description,
       pro.product_code,
       pro.description,
       age.census_age_band,
       c.TYPE,
       s.admission_code,
       SUM(s.length_of_stay) los,
       COUNT(1) volume,
      SUM(caseweight) caseweight,
      s.age,
       p.death_date,      
       p.first_name,
      p.last_name
;
COMMIT
;

END;

END EXTRACT_IP;

END Extract_Inpatient_Data_For_Dw;
/
0
Comment
Question by:deNZity
2 Comments
 
LVL 4

Accepted Solution

by:
radja7 earned 2000 total points
ID: 16433405
Its look like you have not package SPECIFICATION, but try to compile its BODY

-----------
-- first create specification
CREATE OR REPLACE PACKAGE Extract_Inpatient_Data_For_Dw
AS
   PROCEDURE EXTRACT_IP (P_START_DATE IN DATE, P_END_DATE IN DATE);
END Extract_Inpatient_Data_For_Dw;
/

-----------
-- then create body
CREATE OR REPLACE PACKAGE BODY Extract_Inpatient_Data_For_Dw
AS
   PROCEDURE EXTRACT_IP (P_START_DATE IN DATE, P_END_DATE IN DATE)
   IS
   BEGIN
   ...
   END EXTRACT_IP;

END Extract_Inpatient_Data_For_Dw;
/
0
 

Author Comment

by:deNZity
ID: 16433423
thanks for the reply.

It was a permissions issue, once I logged in as a certain user everything was a ok.

points for replying.

D
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question