Not all variables bound ORA-01008: not all variables bound error

oracleoracle-11g-r2oracle-sql-developerplsql

I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like
first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, commission_pct, manager_id, department_id.

I am writting a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt.
If you enter 12000, it should display all employees earning more than 12000.
Eg: Salary_value: 12000.

My report is like this

select last_name, salary
from employees
where employees.salary>&sal;

But I am getting an error like this

ORA-01008: not all variables bound
01008. 00000 - "not all variables bound"
*Cause:
*Action:
Vendor code 1008

Clearly there is nothing in the cause section. I need help please. Thanks in advance.

BUT I can succesfully run it from Sql plus command line utility as well as I can run it as a *.sql file. But It is not working in case of a report. Why is this happening?

Best Answer

Maybe you have set define to off.

set define off
select last_name, salary from employees where employees.salary>&sal;

Error starting at line : 2 in command -
select last_name, salary from employees where employees.salary>&sal
Error at Command Line : 2 Column : 31
Error report -
SQL Error: ORA-01008: not all variables bound
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:

Set it to on (which is the default value by the way):

set define on
select last_name, salary from employees where employees.salary>&sal;

With define set to on, SQL Developer will prompt for a value for the variable &sal.

Related Solutions

Why ORA-00932: Inconsistent Types error

When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'". The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query. Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;

Ora 20001 error in oracle dbms scheduler

Your question contain 2 items that must have been looked at before question itself arose:

Have you looked at API of DBMS_SCHEDULER (any programmer should do that before knowing how to use built-in [package]) ; another words have you RTFM?
Oracle (most of the time) is in-fact very good with "responses" - error signaling back to "client". Have you googled ora - 20001 ?

To your question:

Your job action is NULL; you need to have some procedure that does something AND also is not named as Oracle keyword or PLSQl block that must have at bare minimum begin and end - docs
Oracle error 20001 is custom error and if you want to see the code you need to contact DBA's.
Based on the message , seems that your schema also missing some grants (you obviously logged in to the database , so message itself is deceiving , but the exact error can only be identified by going thru the code or checking with DBA's directly - see above )
why do you need job id? your job name is providing your with information already! check API's!!

Best Answer

Related Solutions

Why ORA-00932: Inconsistent Types error

Ora 20001 error in oracle dbms scheduler

Related Question