0

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?

0

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.

  • After set define on I am getting an error "missing or invalid option". – Agniva Dutta Apr 14 at 14:33
  • Suspiciously I can successfully run it in Sql Plus command line utility. It is running as usual. But in case of report in sql developer this error is generating. – Agniva Dutta Apr 14 at 14:34
  • @AgnivaDutta substitution variables works in sql worksheet but for reports you need to use bind variables so your where clause for report should be where employees.salary>:sal; – Sam Apr 14 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.