1
>        set serveroutput on  
>        set autoprint on;  
>       declare  
>       v_first_name employees.first_name%type;  
>       v_street_address locations.street_address%type;   
>       v_city locations.city%type;    
>       v_postal_code locations.postal_code%type;   
>       begin   
>       select employee_id first_name,street_address,city,postal_code       into:b_employee_id,v_first_name,v_street_address,v_city,v_postal_code 
> 
>       from employees   natural join locations    
>     where employee_id=156;    // how to get employee_id stored in b_employee_ud
>     dbms_output.put_line('the employee'||v_first_name ||' is located    at:'||v_street_address||   v_city ||v_postal_code );    
>     end;     
>     /

getting error Error report: ORA-06550: line 7, column 134: PL/SQL: ORA-00913: too many values ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

b_employee_id

156

i want to use employee_id which is stored in b_employee_id

user3291451
  • 29
  • 1
  • 2
  • 4
  • What is b_employee_id? Where is no declaration of this variable in the code, you've provided. And, i believe, there is a comma missing here: `select employee_id, first_name,street_address,city,postal_code` – Mikhail Feb 10 '14 at 06:53
  • You seem to have missed joining condition between `employees` and `locations` table – Dipendu Paul Feb 10 '14 at 07:17
  • @DipenduPaul The `natural join` will join by all columns with the same name. – Jon Heller Feb 10 '14 at 14:00

1 Answers1

2

First of all, you're missing a comma after employee_id in your SELECT clause.

select employee_id first_name,street_address,city,postal_code 
into   :b_employee_id,v_first_name,v_street_address,v_city,v_postal_code

should be

select employee_id, first_name,street_address,city,postal_code 
into   :b_employee_id,v_first_name,v_street_address,v_city,v_postal_code

Now, coming back to the part where you want to use employee_id = 156

i want to use employee_id which is stored in b_employee_id

If your intent is not hard coding the employee_id for which you want to run your query (I am guessing this by reading the commented line at the end of your WHERE clause), then you need to use substitution variable in the WHERE clause, something like this:

WHERE employee_id = :b_emp_id

Another assumption here will be there, there is one record for one employee in the table from where you are trying to retrieve the records. You should not use a substitution variable in the variables of INTO clause

In case you want to override the value being returned in the INTO clause to some other value for any reason, you can use another variable later on in your program to do that.

Incognito
  • 2,964
  • 2
  • 27
  • 40