PLS-00103: Encountered the symbol “(” when expecting

Question

What is wrong in the following procedure? I am getting the following Error.

LINE/COL ERROR

1/107 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

1/114 PLS-00103: Encountered the symbol "IS" when expecting one of the following: return

CREATE OR REPLACE FUNCTION MOBLIE_RX_VERIFICATION(ARG_DAD_ID IN   NUMBER,ARG_PATIENT_NAME IN VARCHAR2,ARG_VERIFY_MODE VARCHAR2(15))  AS

DECLARE 

NEW_CCH_ID NUMBER;
CCH_COUNT NUMBER;
CCL_COUNT NUMBER;
vPAT_ID VARCHAR2(20);
vENCNTR_ID NUMBER;
vSTM_ID  NUMBER;
vADMIN_DT DATE;
vGIM_ID NUMBER;
vIDM_ID NUMBER;
vQTY NUMBER;
vUOM_ID NUMBER;
vMEDIUM_IDM NUMBER;
vMEDIUM_QTY NUMBER;
vMEDIUM_UOM NUMBER;
vMEDIUM_GIM NUMBER;
vPPD_ID NUMBER;
vADMIN_TYPE VARCHAR2(50);
vSTATUS VARCHAR2(50);
vDOSE VARCHAR2(15);

BEGIN

SELECT DAD_PATIENT_ID, DAD_ENCOUNTER_ID, DAD_STM_ID, TRUNC(DAD_ADMIN_DATE), DAD_GIM_ID, DAD_IDM_ID, DAD_QUANTITY, DAD_UOM_ID, NVL(DAD_MEDIUM_IDM_ID,0), DAD_MEDIUM_QTY, DAD_MEDIUM_UOM_ID, NVL(DAD_PPD_ID,0), DAD_ADMIN_TYPE,DAD_STATUS, DAD_DOSE_NUM
INTO vPAT_ID,vENCNTR_ID,vSTM_ID,vADMIN_DT,vGIM_ID,vIDM_ID,vQTY,vUOM_ID,vMEDIUM_IDM, vMEDIUM_QTY, vMEDIUM_UOM,vPPD_ID,vADMIN_TYPE, vSTATUS,vDOSE
FROM DRUG_ADMIN_DETAIL
WHERE DAD_ID = ARG_DAD_ID;

SELECT COUNT(*) INTO CCH_COUNT FROM CURRENT_CONSUMPTION_HEADER
WHERE CCH_STM_ID = vSTM_ID AND CCH_CONSUMPTION_TYPE = 'PATIENT' AND TRUNC(CCH_CONSUMPTION_DATE) = vADMIN_DT;



IF ARG_VERIFY_MODE = 'VERIFY' THEN


    UPDATE DRUG_ADMIN_DETAIL SET DAD_STATUS= 'VERIFIED' WHERE DAD_ID= ARG_DAD_ID;

    IF vPPD_ID <> 0 THEN

        UPDATE PATIENT_PRESCRIPTION_DETAIL SET PPD_STATUS= 'VERIFIED' WHERE PPD_ID= vPPD_ID AND PPD_STATUS= 'ADMINISTERED';    

    END IF;

    IF CCH_COUNT = 0 THEN

        INSERT INTO CURRENT_CONSUMPTION_HEADER(CCH_STM_ID, CCH_CONSUMPTION_DATE, CCH_CONSUMPTION_TYPE, CCH_STATUS) 
        VALUES (vSTM_ID, vADMIN_DT, 'PATIENT', 'OUTSTANDING') RETURNING CCH_ID INTO NEW_CCH_ID;

    ELSE

        SELECT CCH_ID INTO NEW_CCH_ID FROM CURRENT_CONSUMPTION_HEADER
        WHERE CCH_STM_ID = vSTM_ID AND CCH_CONSUMPTION_TYPE = 'PATIENT' AND TRUNC(CCH_CONSUMPTION_DATE) = vADMIN_DT;


    END IF;

    INSERT INTO CURRENT_CONSUMPTION_LINE (CCL_CCH_ID, CCL_ADMISSION_NO, CCL_PATIENT_ID, CCL_IDM_ID, UOM_ID,CCL_CONS_CATOGORY, 
    CCL_CONS_FACTOR, CCL_QTY, CCL_PATIENT_NAME, CCL_SHIFT,CCL_STATUS, CCL_DAD_ID) 
    VALUES ( NEW_CCH_ID,vENCOUNTER_ID, vPATIENT_ID,vIDM_ID,VUOM_ID, (SELECT GIM_CONS_CATEGORY FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vGIM_ID),
    (SELECT GIM_CONS_FACTOR FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vGIM_ID),vQTY,ARG_PATIENT_NAME,vDOSE,'OUTSTANDING', ARG_DAD_ID);    

    IF vMEDIUM_QTY > 0 

        SELECT IDM_GIM_ID INTO vMEDIUM_GIM
        FROM ITEM_dETAILS_MASTER
        WHERE  IDM_ID = vMEDIUM_IDM;

        INSERT INTO CURRENT_CONSUMPTION_LINE (CCL_CCH_ID, CCL_ADMISSION_NO, CCL_PATIENT_ID, CCL_IDM_ID, UOM_ID,CCL_CONS_CATOGORY, 
        CCL_CONS_FACTOR, CCL_QTY, CCL_PATIENT_NAME, CCL_SHIFT) 
        VALUES (NEW_CCH_ID,vENCOUNTER_ID, vPATIENT_ID,vMEDIUM_IDM,vMEDIUM_UOM, (SELECT GIM_CONS_CATEGORY FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vMEDIUM_GIM),
        (SELECT GIM_CONS_FACTOR FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vMEDIUM_GIM),vMEDIUM_QTY,ARG_PATIENT_NAME,vDOSE,'OUTSTANDING', ARG_DAD_ID); 

    END IF;


ELSIF ARG_VERIFY_MODE = 'DEVERIFY' THEN

    UPDATE DRUG_ADMIN_DETAIL SET DAD_STATUS= 'ADMINISTERED' WHERE DAD_ID= ARG_DAD_ID;

    IF vPPD_ID <> 0 THEN

        UPDATE PATIENT_PRESCRIPTION_DETAIL SET PPD_STATUS= 'ADMINISTERED' WHERE PPD_ID= vPPD_ID AND PPD_STATUS= 'VERIFIED';    

    END IF;   


    SELECT CCL_CCH_ID INTO NEW_CCH_ID FROM CURRENT_CONSUMPTION_LINE WHERE CCL_DAD_ID = ARG_DAD_ID;

    DELETE CURRENT_CONSUMPTION_LINE
    WHERE CCL_DAD_ID = ARG_DAD_ID;


    DELETE CURRENT_COMSUMPTION_HEADER
    WHERE CCH_ID = NEW_CCH_ID
    AND (SELECT COUNT(*) FROM CURRENT_CONSUMPTION_LINE WHERE CCL_CCH_ID = NEW_CCH_ID) = 0;


END IF; 

COMMIT;
END;

Parameters do not have lengths. arg_verify_mode should just be a varchar2. You also don't use declare in a stored procedure. The as begins your declaration section. I stopped looking for syntax errors after the first couple lines. — Justin Cave
Please have a look at it please modify the required changes. I am not getting the what is the problem.@JustinCave — Murali krishna
Do you see your parameter list where you are declaring arg_verify_mode? You've included a length. Parameters do not have lengths. Remove the length. Remove the declare. Beyond that, it makes sense to start small and build up. Your declaration alone as at least those two syntax errors. Start with a stored procedure that does nothing and get that to compile. Then add in code ad make sure it compiles with each iteration. That's much easier than trying to find all the syntax errors in a hundred lines of code. — Justin Cave

APC APC · Accepted Answer · 2016-03-05T04:26:14

Your program's signature has three syntax errors.

It does not return a value therefore it is a PROCEDURE not a FUNCTION
You have specified the length of the third argument; that is not valid for PL/SQL parameters.
The DECLARE keyword is only used for anonymous blocks. It is not valid syntax for named PL/SQL units (the IS or AS keyword fulfils its function of marking the variable declaration section).

So your code should start like this

CREATE OR REPLACE procedure MOBLIE_RX_VERIFICATION
   (ARG_DAD_ID IN   NUMBER,
    ARG_PATIENT_NAME IN VARCHAR2,
    ARG_VERIFY_MODE VARCHAR2)  
AS  
    NEW_CCH_ID NUMBER;

PLS-00103: Encountered the symbol “(” when expecting

2 Answers