not all variables bound (Simple query)-CodePudding

I've written a super simple test trying to get a value stored in l_numTables and it keeps throwing the error "not all variables bound". There's only one variable. It's a number and should work fine with the return data type from COUNT(*). Is there anything I'm missing here? (Moving from SSMS to Oracle has been rough)

declare l_numTables number := 0;

begin
    select COUNT(*) 
      into :l_numTables 
      from all_tab_columns 
     where column_name like '%TEST%';

    DBMS_OUTPUT.put_line(l_numTables);
end;

CodePudding user response：

The INTO clause is not part of the SELECT statement, it's exclusively a PL/SQL construct. l_numTables is not a bind variable (it is not part of the SQL statement, even though it appears in the middle of it). Remove the colon prefix, and see what happens - I didn't check the rest to see if it's OK.