Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    46

    Unanswered: Calling Stored Proccedure inside the Stored procedure

    Hi,

    I am new for oracle,

    when i am calling stored procedure inside the stored procedure i am getting error : PLS-00103: Encountered the symbol.

    Please help me !!
    Thanks in advance!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It would help if you posted actual code. Obviously, there's a character which isn't expected.
    Quote Originally Posted by Oracle
    PLS-00103: %s

    Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.

    Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct.

  3. #3
    Join Date
    Jun 2009
    Posts
    46
    Hi below is the test proc where i execute the procedure :

    CREATE OR REPLACE PROCEDURE Prc_Caculatesiteallowence
    AS
    finalamount FLOAT;
    EMPAttendance INT;
    LOPDays INT;
    Daysinmonth INT;
    MonthlastDt DATE;
    MonthFirstDt DATE;
    Leaveunpayd INT;
    Attunpayd INTEGER;
    SAMonth INTEGER;
    SAYear INTEGER;
    EmpGrade INT;
    empgradeamt FLOAT;
    allowencetype INT;
    Applicable CHAR;
    Branch INT;
    worklocation INT;
    Cur_Employeeid INT;

    CURSOR CurEmpId IS
    SELECT Employeeid FROM TBL_EMPLOYEES;

    CURSOR CurSiteAllowence IS
    SELECT ALLOWENCETYPEID,APPLICABLE FROM TBL_SITEALLOWENCE WHERE
    EMPLOYEEID IN (SELECT Employeeid FROM TBL_EMPLOYEES WHERE employeeid=Cur_Employeeid AND isdeleted='N');

    BEGIN
    BEGIN
    execute procedure Prc_CalculateHoldSiteallowence();
    END;
    OPEN CurEmpId;

    LOOP

    FETCH CurEmpId INTO Cur_Employeeid;

    EXIT WHEN CurEmpId %NOTFOUND;
    BEGIN
    SELECT TO_CHAR(SYSDATE,'MM') INTO SAMonth FROM dual;
    END;

    BEGIN
    SELECT TO_CHAR(SYSDATE,'YYYY') INTO SAYear FROM dual;
    END;

    BEGIN
    MonthFirstDt:=TO_DATE('01' || '/' || CAST(SAMonth AS VARCHAR2) || '/' || cast(SAYear AS VARCHAR2) ,'DD/MM/YYYY');
    END;

    --It will Calculate total no of Days in month
    BEGIN
    SELECT LAST_DAY(TO_DATE(MonthFirstDt,'DD/MM/YYYY'))-(ADD_MONTHS(LAST_DAY(TO_DATE(MonthFirstDt,'DD/MM/YYYY')),-1)) INTO Daysinmonth FROM dual;
    END;

    --It wi give last date of the month
    BEGIN
    MonthlastDt:=TO_DATE( CAST(Daysinmonth AS VARCHAR2) || '/' || cast(SAMonth AS VARCHAR2) || '/' || cast(SAYear AS VARCHAR2),'DD/MM/YYYY');
    END;

    --it will Calculate Unpayed Days from tbl_Leaveapplication
    BEGIN
    SELECT NVL(SUM(UNPAIDLEAVE),0) INTO Leaveunpayd FROM TBL_LEAVEAPPLICATION WHERE EMPLOYEE_ID =Cur_Employeeid AND TO_CHAR(LEAVEFROMDT,'MM')=SAMonth AND TO_CHAR(LEAVETODT,'MM')=SAMonth AND TO_CHAR(LEAVEFROMDT,'YYYY')= SAYear AND TO_CHAR(LEAVETODT,'YYYY')=SAYear AND Isdeleted='N';
    END;

    BEGIN
    SELECT WORKLOCATION_ID INTO Branch FROM TBL_OFFICIALS WHERE employeeid=Cur_Employeeid;
    END;

    --It will Calculate Absent Dayes
    BEGIN
    SELECT (Calculate_Datedif (MonthFirstDt, MonthlastDt)) - COUNT(DISTINCT TBL_HOLIDAYLOCATIONS.holiday_id) - Fn_Getweeklyoffs (MonthFirstDt ,MonthlastDt,Branch) - Fn_Getemployeeattendance(MonthFirstDt , MonthlastDt ,Cur_Employeeid) INTO Attunpayd FROM TBL_HOLIDAYLOCATIONS;
    END;

    --Total LOP Dayes
    BEGIN
    LOPDays:=(Leaveunpayd + Attunpayd);
    END;

    --It will fetch Attendance of the Month
    BEGIN
    SELECT Fn_Getemployeeattendance(MonthFirstDt , MonthlastDt ,Cur_Employeeid) INTO EMPAttendance FROM dual;
    END;


    BEGIN
    SELECT DISTINCT GRADE_ID INTO EMPGrade FROM TBL_OFFICIALS WHERE employeeid=Cur_Employeeid AND ISDELETED = 'N';
    END;
    --It will give Employee Grade Site Allowence Amount
    BEGIN
    --SELECT NVL(AMOUNT,0) INTO empgradeamt FROM TBL_EMPGRADEAMOUNT WHERE GRADEID =EMPGrade;
    SELECT NVL(AMOUNT,0) INTO empgradeamt FROM TBL_EMPGRADEAMOUNT WHERE GRADEID IN(SELECT DISTINCT GRADE_ID FROM TBL_OFFICIALS WHERE employeeid=Cur_Employeeid AND ISDELETED = 'N');

    END;
    --empgradeamt:=10;
    -- BEGIN
    -- INSERT INTO TESTSITE VALUES(Leaveunpayd,EMPGrade,empgradeamt,Attunpayd, LOPDays,EMPAttendance);
    -- END;

    --2nd Cursor Start
    OPEN CurSiteAllowence;

    LOOP

    FETCH CurSiteAllowence INTO allowencetype,Applicable;

    EXIT WHEN CurSiteAllowence%NOTFOUND;

    --****************************
    --ALLOWENCE TYPE
    --1=withCTC
    --2=withoutCTC
    --***************************
    --SITE ALLOWENCE STATUS
    --0=pending
    --1-HOLD
    --2-DONE
    --*****************************
    IF allowencetype =1 AND Applicable =1 THEN

    IF EMPAttendance <> 0 THEN
    BEGIN
    finalamount:= ROUND((empgradeamt/(Daysinmonth)*(Daysinmonth - LOPDays)));
    INSERT INTO TBL_SITEALLOWENCE_AMOUNT(ID,EMPLOYEEID,SITEMONTH,S ITEYEAR,STATUS,SITEAMOUNT,BRANCHID)
    VALUES(SQC_TBL_SITEALLOWENCE_AMOUNT.NEXTVAL,Cur_Em ployeeid,SAMonth,SAYear,0,finalamount,Branch);
    END;
    ELSE
    BEGIN
    INSERT INTO TBL_SITEALLOWENCE_AMOUNT(ID,EMPLOYEEID,SITEMONTH,S ITEYEAR,STATUS,SITEAMOUNT,BRANCHID)
    VALUES(SQC_TBL_SITEALLOWENCE_AMOUNT.NEXTVAL,Cur_Em ployeeid,SAMonth,SAYear,1,empgradeamt,Branch);
    END;
    END IF;

    ELSIF allowencetype =2 AND Applicable =1 THEN
    IF Branch = 19 THEN
    IF EMPAttendance <> 0 THEN
    BEGIN
    finalamount:= ROUND((empgradeamt/(Daysinmonth)*(Daysinmonth - LOPDays)));
    INSERT INTO TBL_SITEALLOWENCE_AMOUNT(ID,EMPLOYEEID,SITEMONTH,S ITEYEAR,STATUS,SITEAMOUNT,BRANCHID)
    VALUES(SQC_TBL_SITEALLOWENCE_AMOUNT.NEXTVAL,Cur_Em ployeeid,SAMonth,SAYear,1,finalamount,Branch);
    END;
    ELSE
    BEGIN
    INSERT INTO TBL_SITEALLOWENCE_AMOUNT(ID,EMPLOYEEID,SITEMONTH,S ITEYEAR,STATUS,SITEAMOUNT,BRANCHID)
    VALUES(SQC_TBL_SITEALLOWENCE_AMOUNT.NEXTVAL,Cur_Em ployeeid,SAMonth,SAYear,1,empgradeamt,Branch);
    END;
    END IF;
    ELSE
    IF EMPAttendance <> 0 THEN
    BEGIN
    finalamount:= ROUND((empgradeamt/(Daysinmonth)*(Daysinmonth - LOPDays)));
    INSERT INTO TBL_SITEALLOWENCE_AMOUNT(ID,EMPLOYEEID,SITEMONTH,S ITEYEAR,STATUS,SITEAMOUNT,BRANCHID)
    VALUES(SQC_TBL_SITEALLOWENCE_AMOUNT.NEXTVAL,Cur_Em ployeeid,SAMonth,SAYear,0,finalamount,Branch);
    END;
    ELSE
    BEGIN
    INSERT INTO TBL_SITEALLOWENCE_AMOUNT(ID,EMPLOYEEID,SITEMONTH,S ITEYEAR,STATUS,SITEAMOUNT,BRANCHID)
    VALUES(SQC_TBL_SITEALLOWENCE_AMOUNT.NEXTVAL,Cur_Em ployeeid,SAMonth,SAYear,1,empgradeamt,Branch);
    END;
    END IF;
    END IF;

    END IF;
    END LOOP;
    CLOSE CurSiteAllowence ;
    --2nd Cursor ends here
    END LOOP;
    CLOSE CurEmpId ;
    --1st Cursor Ends Here/
    END Prc_Caculatesiteallowence;
    /


    Thanks!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Huh, this code is pain to read ... please, try to format it and use [code] tags to preserve formatting.

    I guess that this piece of code is what you are talking about:
    Code:
    BEGIN
      execute procedure Prc_CalculateHoldSiteallowence();
    END;
    If so, where did you find that syntax? There's no "execute procedure" - you call it by specifying its name:
    Code:
    Prc_CalculateHoldSiteallowence();
    Also, why did you enclose each and every statement into its own BEGIN-END block? There's really no need to do that (but can be useful if, for example, you want to handle certain exceptions).

  5. #5
    Join Date
    Jun 2009
    Posts
    46
    Thanks!
    It helps to solve the issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •