Getting ORA-01744: inappropriate INTO when trying to compile this stored proc

Question

im writing this stored proc to update a row when date retrieved from multiple tables. Running each query on its own works but when compiling i get an inappropriate into, which i dont understand why as the select into is on the outermost layer of the query. im sure its something simple im missing but would appreciate a hand with it.

all queries work on their own but just having an issue with the 4th select into, im thinking maybe i cant select into while using concatenation or maybe with db links? not sure either way.

/* Formatted on 4/1/2023 7:00:25 PM (QP5 v5.360) */
CREATE PROCEDURE UPDATE_CLAIM (V_CLAIM_ID IN PTSADMIN.CLAIM.CLAIMID%TYPE)
IS
    V_CREW_ID      NUMBER;
    V_PERSONID     NUMBER;
    V_LIRRNUM      VARCHAR2 (10);
    V_CNAME        VARCHAR2 (10);
    V_START_DATE   DATE;
    V_NEXT_DAY     DATE;
    V_JOBNAME      VARCHAR2 (20);
    V_JOBGRADEID   NUMBER;
BEGIN
    BEGIN
        SELECT CREWID,
               PERSONID,
               CLAIMDTM,
               CLAIMDTM + 1
          INTO V_CREW_ID,
               V_PERSONID,
               V_START_DATE,
               V_NEXT_DAY
          FROM PTSADMIN.CLAIM CM
         WHERE CM.CLAIMID = V_CLAIM_ID;
    END;

    BEGIN
        SELECT NAME
          INTO V_CNAME
          FROM PTSADMIN.CREW CW
         WHERE CW.CREWID = V_CREW_ID;
    END;

    BEGIN
        SELECT LIRRNUM
          INTO V_LIRRNUM
          FROM PTSADMIN.PERSON PR
         WHERE PR.PERSONID = PERSONID;
    END;

    BEGIN
        SELECT tm.OCCUP_CODE || td.RATE_CODE
          INTO V_JOBNAME
          FROM ewm.TIMESLIP_MSTR@TEAMS_DBLINK    tm,
               ewm.TIMESLIP_DETAIL@TEAMS_DBLINK  td,
               ewm.EMP_MSTR@TEAMS_DBLINK         em
         WHERE     tm.TIMESLIP_MSTR_SID = td.TIMESLIP_MSTR_SID(+)
               AND tm.EMP_MSTR_SID = em.EMP_MSTR_SID
               AND em.EMP_NBR = V_LIRRNUM
               AND tm.EFF_DATE_TIME BETWEEN TO_DATE (V_START_DATE,
                                                     'MM/DD/YYYY')
                                        AND TO_DATE (V_NEXT_DAY,
                                                     'MM/DD/YYYY')
               AND td.VERSION = 1
              AND ROWNUM <= 1;
    END;


    BEGIN
        SELECT JOBGRADEID
          INTO V_JOBGRADEID
          FROM PTSADMIN.JOBGRADE JB
         WHERE JB.NAME = V_JOBNAME;
    END;

    UPDATE PTSADMIN.CLAIM CL
       SET CL.JOBGRADEID = V_JOBGRADEID
     WHERE CL.CLAIMID = V_CLAIM_ID;
END UPDATE_CLAIM;

Answer 1

Two obvious mistakes I noticed:

• you used personid instead of v_personid in one query
• variables - that are already DATEs - shouldn't be TO_DATEd. It seems that you actually want to TRUNC them.

Also (although it isn't wrong), it is useless in enclosing each select into its own begin-end block. It would make sense if you wanted to handle exceptions that way, but - you didn't.

When fixed (can't compile it, though, as I don't have your tables, you didn't provide test case and I don't feel like creating that many of them on my own):

CREATE PROCEDURE update_claim (
    v_claim_id IN ptsadmin.claim.claimid%TYPE
) IS

    v_crew_id    NUMBER;
    v_personid   NUMBER;
    v_lirrnum    VARCHAR2(10);
    v_cname      VARCHAR2(10);
    v_start_date DATE;
    v_next_day   DATE;
    v_jobname    VARCHAR2(20);
    v_jobgradeid NUMBER;
BEGIN
    SELECT
        crewid,
        personid,
        claimdtm,
        claimdtm + 1
    INTO
        v_crew_id,
        v_personid,
        v_start_date,
        v_next_day
    FROM
        ptsadmin.claim cm
    WHERE
        cm.claimid = v_claim_id;

    SELECT
        name
    INTO v_cname
    FROM
        ptsadmin.crew cw
    WHERE
        cw.crewid = v_crew_id;

    SELECT
        lirrnum
    INTO v_lirrnum
    FROM
        ptsadmin.person pr
    WHERE
        pr.personid = v_personid;    -- personid;      

    SELECT
        tm.occup_code || td.rate_code
    INTO v_jobname
    FROM
        ewm.timeslip_mstr@teams_dblink   tm,
        ewm.timeslip_detail@teams_dblink td,
        ewm.emp_mstr@teams_dblink        em
    WHERE
            tm.timeslip_mstr_sid = td.timeslip_mstr_sid (+)
        AND tm.emp_mstr_sid = em.emp_mstr_sid
        AND em.emp_nbr = v_lirrnum
        --AND tm.eff_date_time BETWEEN to_date(v_start_date, 'MM/DD/YYYY') AND to_date(v_next_day, 'MM/DD/YYYY')
        AND tm.eff_date_time BETWEEN trunc(v_start_date) AND trunc(v_next_day)
        AND td.version = 1
        AND ROWNUM <= 1;

    SELECT
        jobgradeid
    INTO v_jobgradeid
    FROM
        ptsadmin.jobgrade jb
    WHERE
        jb.name = v_jobname;

    UPDATE ptsadmin.claim cl
    SET
        cl.jobgradeid = v_jobgradeid
    WHERE
        cl.claimid = v_claim_id;

END update_claim;