Apex ORA-01008: not all variables bound on host variables

Question

I am working with an apex environment where I need a dynamic sql query. Apex provides this api with the help of "Classic Report (based on function)". I would like you to draw your attention to line 8. My appoligies the spacing gets messed up when I paste it into stackoverflow. Now the problem is that I am getting the error 'ORA-01008: not all variables bound' Which is funny because when I change the :TEAM_SELECTOR in line 8 to something along the lines of 'MYSQL' (which is a team name) then this code works without error. Notice that :TEAM_SELECTOR is also used in the returned query without problem. Also I am using APEX 5.

    DECLARE
        v_time INT;
        v_start_time int;
        v_end_time int;
        v_rownum int := 1;
        v_max_shifts int;
        v_location INT;
        v_P4_team_selector varchar2(30) := :TEAM_SELECTOR;
BEGIN

    select extract(hour from CAST(sysdate AS TIMESTAMP)) into v_time from dual;

    select count(*) into v_max_shifts from oncall_shift
        where team = v_P4_team_selector;

    FOR i IN 0..v_max_shifts
    LOOP

            select start_time into v_start_time from (select * from oncall_shift where team =v_P4_team_selector)
        where rownum = v_rownum;
            select end_time into v_end_time from (select * from oncall_shift where team =v_P4_team_selector)
        where rownum = v_rownum;
        v_rownum := v_rownum + 1;   

        if v_time >= v_start_time and v_time <= v_end_time
        then

                select location into v_location from oncall_shift
            where team = v_P4_team_selector
            and start_time = v_start_time
            and end_time = v_end_time;

            return '
                    SELECT E.FNAME "First Name",E.LNAME "Last Name",E.OFFICE_NUM "Office Number", E.MOBILE_NUM "Mobile Number",L.NAME Location, o.position "Primary/Secondary"
                FROM EMPLOYEE E, LOCATION L, ON_CALL O
                WHERE E.ID = O.EMP_ID
                AND L.ID = O.LOC_ID
                AND O.ONCALL_DATE=TRUNC(SYSDATE)
                AND O.TEAM=:TEAM_SELECTOR
                AND L.ID = ' || v_location ||
                ' ORDER BY l.name asc
                ';  
            EXIT;

        END IF;

    END LOOP;
END;

How are you declaring and binding a value to TEAM_SELECTOR? And in the return it's part of a string literal, so whatever then uses that generated statement binds that value itself when it is executed (if that is what is happening). — Alex Poole, Feb 17 '16 at 15:59
where are you declaring this code? it's possible that you're not declaring TEAM_SELECTOR in a field called "page items to submit" — Typo, Feb 19 '16 at 20:38
I don't think it has anything to do with that because even if I put TEAM_SELECTOR into the "page items to submit" it's still throwing variables are not bound errors. I also tried using an apex item from the page that is on the same page which still doesn't work. This is consistent with the other code that I have in other pages that reference TEAM_SELECTOR without error because no apex item seems to work in this one classic report based on a function. — asdf, Feb 19 '16 at 21:07
have you tried using `:TEAM_SELECTOR` directly instead of assigning into a variable? — Vance, Feb 22 '16 at 00:51
What IS `TEAM_SELECTOR` even? Is it a page or application item? — Tom, Feb 22 '16 at 11:28
TEAM_SELECTOR is an item on a separate page. It is a required selection, by that I mean that if TEAM_SELECTOR has a null value then the page redirects (via javascript) to the TEAM_SELECTOR page to force a selection. And yes I have tried to directly use the TEAM_SELECTOR directly into the code instead of putting it into a variable first which gave the same error message. — asdf, Feb 22 '16 at 14:21

score 0 · Answer 1 · answered Feb 22 '16 at 17:51

Okay so I believe the issue was that the classic report based on a function executes before the host variables (TEAM_SELECTOR) is able to be bound therefore throwing the error. My solution to this was to change it to a normal classic report based on a sql query which runs after host variables are bound, because I needed one separate variable I created a P1_LOCATION item on that page using the same the query expect changing "v_location" to P1_LOCATION. Now in the P1_LOCATION page item I put the procedure to get the location.

P1_LOCATION code:

    DECLARE
        v_time INT;
        v_start_time int;
        v_end_time int;
        v_rownum int := 1;
        v_max_shifts int;
        v_location INT;
        v_P4_team_selector varchar2(30) := :TEAM_SELECTOR;
BEGIN

    select extract(hour from CAST(sysdate AS TIMESTAMP)) into v_time from dual;

    select count(*) into v_max_shifts from oncall_shift
        where team = v_P4_team_selector;

    FOR i IN 0..v_max_shifts
    LOOP

            select start_time into v_start_time from (select * from oncall_shift where team =v_P4_team_selector)
        where rownum = v_rownum;
            select end_time into v_end_time from (select * from oncall_shift where team =v_P4_team_selector)
        where rownum = v_rownum;
        v_rownum := v_rownum + 1;   

        if v_time >= v_start_time and v_time <= v_end_time
        then

                select location into v_location from oncall_shift
            where team = v_P4_team_selector
            and start_time = v_start_time
            and end_time = v_end_time;

            APEX_UTIL.set_session_state('P1_LOCATION',v_location);

            EXIT;

        END IF;

    END LOOP;
END;

Classic Report (sql query):

SELECT E.FNAME "First Name",E.LNAME "Last Name",E.OFFICE_NUM "Office Number", E.MOBILE_NUM "Mobile Number",L.NAME Location, o.position "Primary/Secondary"
                FROM EMPLOYEE E, LOCATION L, ON_CALL O
                WHERE E.ID = O.EMP_ID
                AND L.ID = O.LOC_ID
                AND O.ONCALL_DATE=TRUNC(SYSDATE)
                AND O.TEAM=:TEAM_SELECTOR
                AND L.ID = :P1_LOCATION
                 ORDER BY l.name asc

Apex ORA-01008: not all variables bound on host variables

1 Answers1