Can anyone tell me what about these two scripts is causing "created with compilation errors." warnings? We're using Oracle db 19c. Do the "COMMIT;" statements matter one way or the other? I thought they were only needed for like create, update, delete, etc operations but added them because I thought it couldn't hurt. I don't currently have an environment in which I can test the scripts directly. Thanks!
create or replace PACKAGE PNM_WS_ADS_CONTRACT_ENTITIES AS
PROCEDURE GET_INFO
(
PNM_P_ADS_CE_ID IN VARCHAR2
, PNM_P_ADS_CE_VERSION_NUMBER IN VARCHAR2 DEFAULT NULL
, PNM_P_ADS_CE_INFO_CUR OUT SYS_REFCURSOR
, PNM_P_RETURNCD OUT VARCHAR2
, PNM_P_RETURNMSG OUT VARCHAR2
) ;
PROCEDURE GET_NETWORKS
(
PNM_P_ADS_CE_ID IN VARCHAR2
, PNM_P_ADS_CE_VERSION_NUMBER IN VARCHAR2 DEFAULT NULL
, PNM_P_ADS_CE_NETWORK_CUR OUT SYS_REFCURSOR
, PNM_P_RETURNCD OUT VARCHAR2
, PNM_P_RETURNMSG OUT VARCHAR2
) ;
END PNM_WS_ADS_CONTRACT_ENTITIES;
COMMIT;
create or replace PACKAGE BODY PNM_WS_ADS_CONTRACT_ENTITIES "PNM_WS_ADS_CONTRACT_ENTITIES" AS
PROCEDURE GET_INFO
(
PNM_P_ADS_CE_ID IN VARCHAR2
, PNM_P_ADS_CE_VERSION_NUMBER IN VARCHAR2 DEFAULT NULL
, PNM_P_ADS_CE_INFO_CUR OUT SYS_REFCURSOR
, PNM_P_RETURNCD OUT VARCHAR2
, PNM_P_RETURNMSG OUT VARCHAR2
) AS
PNM_L_CE_INFO_QUERY VARCHAR2(2000);
BEGIN
PNM_L_CE_INFO_QUERY:= '';
IF PNM_P_ADS_CE_ID IS NOT NULL THEN
open PNM_P_ADS_CE_INFO_CUR for
SELECT 1 FROM DUAL;
ELSE
PNM_P_RETURNCD := '1001';
PNM_P_RETURNMSG := 'INAVALID REQUEST';
RETURN;
END IF;
PNM_P_RETURNCD := '1000';
PNM_P_RETURNMSG := 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
PNM_P_RETURNCD := SQLCODE;
PNM_P_RETURNMSG := SUBSTR(SQLERRM, 1, 100);
END GET_INFO;
PROCEDURE GET_NETWORKS
(
PNM_P_ADS_CE_ID IN VARCHAR2
, PNM_P_ADS_CE_VERSION_NUMBER IN VARCHAR2 DEFAULT NULL
, PNM_P_ADS_CE_NETWORK_CUR OUT SYS_REFCURSOR
, PNM_P_RETURNCD OUT VARCHAR2
, PNM_P_RETURNMSG OUT VARCHAR2
) AS
PNM_L_CE_NETWORK_QUERY VARCHAR2(2000);
BEGIN
PNM_L_CE_NETWORK_QUERY:= '';
IF PNM_P_ADS_CE_ID IS NOT NULL THEN
open PNM_P_ADS_CE_NETWORK_CUR for
SELECT DISTINCT CE.SRC_CONTRACT_ENTITY_ID,
CE.CONTRACT_ENTITY_NAME,
CONT.ADS_CONTRACT_ID,
NET.NETWORK_NAME,
HP.HEALTHPLAN_NAME,
CONT.CONTRACT_NUMBER,
CONT.CONTRACT_STATUS,
CONT.TARGET_PERCENT,
CONT.FORECAST_PERCENT,
CONT.ACTUAL_PERCENT,
CONT.IN_PATIENT_RATE,
CONT.OUT_PATIENT_RATE,
CONT.FIRST_SIGNATURE_DT,
CONT.EST_CONTRACT_CLOSE_DT,
CONT.CONTRACT_CLOSE_DT,
CONT.CONTRACT_NAME,
CONT.EFFECTIVE_DT,
CONT.IN_PATIENT_RATE,
CONT.OUT_PATIENT_RATE,
CONT.PROV_REQUESTED,
CONT.NETWORK_ADEQ,
CONT.HIGH_PERF_PROV,
CONT.TIER_PRIORITY,
CONT.PHASE,
CONT.ACCEPT,
CONT.CONTRACT_TYPE,
NET.ADS_NETWORK_ID,
NW.LINE_OF_BUSINESS,
NW.NEGOTIATED_LANGUAGE,
NW.NEGOTIATED_RATES,
NW.CAPITATION,
NW.CONTRACT_TYPE CONTRACT_CATEGORY
FROM PLMSTAGE.V_ADS_CL_CONTRACT_ENTITY CE
LEFT JOIN PLMSTAGE.V_ADS_CL_XF_CE_CNTRCT_NET NW
ON CE.ADS_CONTRACT_ENTITY_ID = NW.ADS_CONTRACT_ENTITY_ID
INNER JOIN PLMSTAGE.V_ADS_CL_XF_CE_NW XREF_CE_NET
ON XREF_CE_NET.ADS_CONTRACT_ENTITY_ID = CE.ADS_CONTRACT_ENTITY_ID
AND XREF_CE_NET.ADS_NETWORK_ID = NW.ADS_NETWORK_ID
INNER JOIN PLMSTAGE.V_ADS_CL_NETWORK NET
ON XREF_CE_NET.ADS_NETWORK_ID = NET.ADS_NETWORK_ID
LEFT JOIN PLMSTAGE.V_ADS_CL_HEALTHPLAN HP
ON NW.ADS_HEALTHPLAN_ID = HP.ADS_HEALTHPLAN_ID
LEFT JOIN PLMSTAGE.V_ADS_CL_CONTRACT CONT
ON NW.ADS_CONTRACT_ID = CONT.ADS_CONTRACT_ID
WHERE CE.ADS_CONTRACT_ENTITY_ID = PNM_P_ADS_CE_ID;
ELSE
PNM_P_RETURNCD := '1001';
PNM_P_RETURNMSG := 'INAVALID REQUEST';
RETURN;
END IF;
PNM_P_RETURNCD := '1000';
PNM_P_RETURNMSG := 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
PNM_P_RETURNCD := SQLCODE;
PNM_P_RETURNMSG := SUBSTR(SQLERRM, 1, 100);
END GET_NETWORKS;
END PNM_WS_ADS_CONTRACT_ENTITIES;
COMMIT;
Do the
COMMIT;statements matter one way or the other?
No, the COMMIT statement does not matter. When you run a DDL statement (including CREATE PACKAGE and CREATE PACKAGE BODY) then the database will issue an implicit COMMIT statement so you will try to create the package, implicitly COMMIT, the package compiles (or fails) and then explicitly COMMITs, which is unnecessary as there would be nothing to COMMIT.
However, you need to terminate PL/SQL blocks with / on a new line; so, at the end of the both the package specification and body, you should have:
END PNM_WS_ADS_CONTRACT_ENTITIES;
/
Because the CREATE PACKAGE statement is not terminated then you will get a syntax error from the COMMIT statement following it as it is unexpected. If you had terminated the package body/specification then the COMMIT would be superfluous (unnecessary but relatively harmless) but because you haven't then it becomes part of the previous statement.
Also, depending on the database driver you are using to communicate with the database, you may get additional runtime errors if you return a cursor without opening it.
There may be additional errors but I do not have your tables to test against.