PL/SQL Procedure with parameter

Question

I'm dead tired and my assignment is due in seven hours. I need a kind soul(s) to help me!

So this is my first PL/SQL class. We are starting work on Procedures and Functions.

So here is the one I am stuck on. The question says:

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.

B. Execute your procedure from an anonymous block, using country_id 90.

C. Re-execute the procedure from the anonymous block, this time using country_id 95. What happens?

D. Retrieve your procedure code from Saved SQL and modify it to trap the NO_DATA_FOUND exception in an exception handler. Re-execute the procedure using country_id 95 again. Now what happens?

Here is what I wrote for A:

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 capitol, country_name 
INTO p_capitol, p_name
FROM wf_countries
WHERE country_id=p_id;
END;

I got it to run. But then I realized that if I try to call the procedure by typing:

BEGIN
get_country_info
END;

that I need the country ID. Duh. But I can't just put get_country_info(90) because it is expecting more parameters. I only need to use the country ID as the parameter and DISPLAY the name and capitol.

I'm really frustrated and appreciate any help that you might offer.

seriously? You are asking for help on your assignments in Stack Overflow?? — Arnab Bhagabati
– Arnab Bhagabati, Commented May 12, 2014 at 6:59

dariyoosh · Accepted Answer · 2014-05-12 06:09:29Z

1

According to your problem specification: get_country_info should take only one parameter: country_id.

Remove the other two parameters and declare them as local variables inside your procedure
For displaying the value, use DBMS_OUTPUT.PUT_LINE

If this is run in SQL*Plus don't forget to run first:

SET SERVEROUTPUT ON

Also for the exception part, have a look at Error Handling

answered May 12, 2014 at 6:09

dariyoosh

6141 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomás · Accepted Answer · 2014-05-12 08:19:07Z

0

CREATE OR REPLACE PROCEDURE get_country_info
    (p_id IN wf_countries.country_id%TYPE,
    oCur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN oCur FOR
    SELECT capitol, country_name 
      FROM wf_countries
     WHERE country_id=p_id;
END;

Then, when calling:

DECLARE retCursor SYS_REFCURSOR;
BEGIN
    get_country_info(123, retCursor);
END;
/

answered May 12, 2014 at 8:19

Tomás

5657 silver badges23 bronze badges

Comments

halfer · Accepted Answer · 2019-07-19 07:22:04Z

0

Try this:

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

  DBMS_OUTPUT.PUT_LINE('Name='  || vName || '  Capitol=' || vCapitol);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found for COUNTRY_ID=' || p_id);
END;

edited Jul 19, 2019 at 7:22

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered May 12, 2014 at 13:20

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

PL/SQL Procedure with parameter

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related