Oracle sql - SP2-0552: Bind variable "T_END" not declared error, but "T

Check this Oracle sql script:

SET SERVEROUTPUT ON SIZE 200000;

declare
  start timestamp; 
  fin timestamp;
  opr varchar2(64);
  op varchar2(64);
  ext_act varchar2(64);
  time_sum float;
  counter integer := 1;
  query1 varchar2(4096):='select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end and rownum < 500000 order by IH.EVENT_DATE';
  query2 varchar2(4096):='select * from (select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end) where rownum < 500000';
  query3 varchar2(4096):='select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end fetch first 500000 rows only';


begin
    FOR query IN (SELECT column_value FROM table(sys.dbms_debug_vc2coll(query1, query2, query3))) loop 
        dbms_output.put_line('This is query ' || counter);
            for i in 1..10 loop
                start:=systimestamp;
                exec :opr       := '88000001';
                exec :op        := 'CHANGE_OWNER';
                exec :ext_act   := 'NOT_APPLICABLE';
                exec :t_start   :=TO_DATE('2018/07/01', 'yyyy/mm/dd');
                exec :t_end     :=TO_DATE('2020/05/01', 'yyyy/mm/dd');
                query;
                fin := systimestamp;
                duration := fin - start;
                dbms_output.put_line('Time taken for query' || counter || ' for run ' || i || ': ' || duration);
                time_sum := time_sum || duration;
            end loop;
            dbms_output.put_line('Avg time for query' || counter || ': ' || time_sum / 10);
            time_sum := 0;
        counter:=counter 1;
    end loop;
end

I have error: SP2-0552: Bind variable "T_END" not declared., but T_START is fine and is before T_END. If T_END is bad, why T_START is not? What is wrong?

Originally they were t1 and t2, but the error is the same: no error for t1 and error for t2.

CodePudding user response：

If T_END is bad, why T_START is not?

They are both bad - neither is declared.

SQL Developer (I guess - or SQL*Plus, or SQLcl) is reporting the first high-level issue it finds, which is with an undeclared bind variable. At that level it isn't looking at the PL/SQL itself or passing anything to the database engine to be verified, it's doing its own scan to see if it thinks the code is sane - and deciding it isn't, so it stops immediately, without doing any real work.

It's parsing the block backwards so it sees and reports t_end first, and stops there. If you comment out that line (23) then it will report that t_start is not declared instead.

None of the bind variables you refer to are defined in the client - you would need variable and exec commands before the PL/SQL block; exec is itself a client shorthand for an anonymous PL/SQL block, so it doesn't belong where you have it - it's a client command, not PL/SQL or SQL.

Some of the other issues in your code:

you are using reserved words (like start) which are illegal;
you've declared opr, op and ext_act as local PL/SQL variables - not bind variables - but don't use those;
you've declare time_sum as a float, but you're working with the differences between timestamps, which are intervals;
you are referring to query as if it's a value, but it's effectively a record - to use the value returned by the cursor query you would need to refer to query.column_value, or alias that in the query for a more helpful name;
you are trying to run query; as if it's a complete statement not a variable (or record, see previous point) - you need to treat it as dynamic SQL, which leads to:
you either need to use query.column_value as a dynamic cursor and loop over the results, or change the query to return a single scalar value, which you still have to select into something;
time_sum || duration is doing string concatenation - you presumably meant to use addition;
probably some other things I've forgotten.

I think this does what you were aiming at:

declare
  l_start timestamp; 
  l_fin timestamp;
  l_result pls_integer;
  l_duration interval day to second;
  l_time_sum interval day to second := interval '0' second;
  l_counter integer := 1;
  l_query1 varchar2(4096):='select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end and rownum < 500000 order by IH.EVENT_DATE';
  l_query2 varchar2(4096):='select count(*) from (select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end) where rownum < 500000';
  l_query3 varchar2(4096):='select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end fetch first 500000 rows only';

begin
    FOR query IN (SELECT column_value as str FROM table(sys.dbms_debug_vc2coll(l_query1, l_query2, l_query3))) loop 
        dbms_output.put_line('This is query ' || l_counter);
        for i in 1..10 loop
            l_start:=systimestamp;
            execute immediate query.str
                into l_result            -- not used
                using '88000001',        -- opr
                      'CHANGE_OWNER',    -- op
                      'NOT_APPLICABLE',  -- ext_act
                      DATE '2018-07-01', -- t_start
                      DATE '2020-05-01'; -- t_end
            l_fin := systimestamp;
            l_duration := l_fin - l_start;
            dbms_output.put_line('Time taken for query' || l_counter || ' for run ' || i || ': ' || l_duration);
            l_time_sum := l_time_sum   l_duration;
        end loop;
        dbms_output.put_line('Avg time for query' || l_counter || ': ' || (l_time_sum / 10));
        l_time_sum := interval '0' second;
        l_counter:=l_counter 1;
    end loop;
end;
/

You could also put the query strings into a collection and then just loop over that, without having to use a cursor and query:

declare
  l_start timestamp; 
  l_fin timestamp;
  l_result pls_integer;
  l_duration interval day to second;
  l_time_sum interval day to second := interval '0' second;
  l_queries sys.odcivarchar2list := new sys.odcivarchar2list(
      'select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end and rownum < 500000 order by IH.EVENT_DATE',
      'select count(*) from (select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end) where rownum < 500000',
      'select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end fetch first 500000 rows only');

begin
    for l_counter in 1..l_queries.count loop
        dbms_output.put_line('This is query ' || l_counter);
        for i in 1..10 loop
            l_start:=systimestamp;
            execute immediate l_queries(l_counter)
                into l_result            -- not used
                using '88000001',        -- opr
                      'CHANGE_OWNER',    -- op
                      'NOT_APPLICABLE',  -- ext_act
                      DATE '2018-07-01', -- t_start
                      DATE '2020-05-01'; -- t_end
            l_fin := systimestamp;
            l_duration := l_fin - l_start;
            dbms_output.put_line('Time taken for query' || l_counter || ' for run ' || i || ': ' || l_duration);
            l_time_sum := l_time_sum   l_duration;
        end loop;
        dbms_output.put_line('Avg time for query' || l_counter || ': ' || (l_time_sum / 10));
        l_time_sum := interval '0' second;
    end loop;
end;
/

db<>fiddle using dummy dynamic queries as we don't have your tables (but hopefully you don't have a table called package either?)

If you don't want to pass the literals directly in the using clause then you could declare and populate local PL/SQL variables and refer to those instead; or you could declare client-level bind variables (var/exec), but there doesn't seem to be any advantage in doing that.

I've guessed/assumed a count would give you the timing information you want, allowing a single scalar results, which you ignore anyway. That's simpler than looping over the actual results - and it seems unlikely you're trying to compare the overall fetch times for 50000 rows. Also the order by in the first dynamic query probably isn't doing what you think, and means the three aren't quite equivalent.