2 Replies - 3423 Views - Last Post: 11 May 2014 - 09:52 PM

#1 bombinabirdcage  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 04-May 13

Write a procedure using parameters-DARN SYNTAX

Posted 11 May 2014 - 03:38 PM

Hi all!

I am in my first PL/SQL class and am working on my homework. Here is the problem that I have an issue on:

This question uses the wf_countries table

A. Create a procedure that accepts a country_id as a parameter and displays the name of the country and its capitol city. Name your procedure get_country_info. Save your procedure definition for later use.

Here is the code I have written:

CREATE OR REPLACE PROCEDURE get_country_info
(p_id IN wf_countries.country_id%TYPE,
p_capitol OUT wf_countries.capitol%TYPE,
p_name OUT wf_countries.country_name%TYPE)
IS
BEGIN
SELECT country_id, capitol, country_name INTO p_id, p_capitol, p_name
FROM wf_countries
WHERE country_id=p_id;
END;



This is the error I am getting:

Error at line 7: PLS-00403: expression 'P_ID' cannot be used as an INTO-target of a SELECT/FETCH statement


5. IS
6. BEGIN
7. SELECT country_id, capitol, country_name INTO p_id, p_capitol, p_name
8. FROM wf_countries
9. WHERE country_id=p_id;

Thank you for your help!

This post has been edited by macosxnerd101: 11 May 2014 - 03:52 PM
Reason for edit:: Fixed code tags


Is This A Good Question/Topic? 0
  • +

Replies To: Write a procedure using parameters-DARN SYNTAX

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6996
  • View blog
  • Posts: 14,634
  • Joined: 16-October 07

Re: Write a procedure using parameters-DARN SYNTAX

Posted 11 May 2014 - 04:35 PM

Seems like a reasonably obvious solution. Take the p_id out of the into?
SELECT capitol, country_name
  INTO p_capitol, p_name
  FROM wf_countries
  WHERE country_id=p_id;



After all, you already know p_id: why read it?

If you wish to read it back again, try p_id IN OUT wf_countries.country_id%TYPE.
Was This Post Helpful? 1
  • +
  • -

#3 bombinabirdcage  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 04-May 13

Re: Write a procedure using parameters-DARN SYNTAX

Posted 11 May 2014 - 09:52 PM

Oh my goodness, I feel silly. Thank you so much!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1