Click here to Skip to main content
15,891,905 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
CREATE OR REPLACE PROCEDURE country_demographics
      (p_country_name  IN countries.country_name%TYPE) IS
    
       v_country_demo_rec    VARCHAR2(2000);
     
    BEGIN
      SELECT country_name, location, capitol, population, airports, climate
      INTO v_country_demo_rec
      FROM countries;
     DBMS_OUTPUT.PUT_LINE('Country Name:' || v_country_demo_rec.country_name || 'Location:' || v_country_demo_rec.location || 'Capitol:' || v_country_demo_rec.capitol || 'Population:' || v_country_demo_rec.population || 'Airports:' || v_country_demo_rec.airports || 'Climate:' || v_country_demo_rec.climate );
    
     IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20201, 'This country does not exist.');
     END IF;
    END;

The problem is asking me to create a procedure called country_demograhics. Pass the country_name as an IN parameter. Display CONTRY_NAME, LOCATION, CAPITOL, POPULATION, AIRPORTS, CLIMATE. Use a user-defined record structure for the INTO clause of your select statement. Raise an exception if the country does not exist.
Now here is a copy of my code, that keeps coming back with an error of:

Error at line 9: PL/SQL: ORA-00947: not enough values


7. INTO v_country_demo_rec
8. FROM countries;
9. DBMS_OUTPUT.PUT_LINE('Country Name:' || v_country_demo_rec.country_name || 'Location:' || v_country_demo_rec.location || 'Capitol:' || v_country_demo_rec.capitol || 'Population:' || v_country_demo_rec.population || 'Airports:' || v_country_demo_rec.airports || 'Climate:' || v_country_demo_rec.climate );
10. IF SQL%NOTFOUND THEN
11. RAISE_APPLICATION_ERROR(-20201, 'This country does not exist.');

What I have tried:

I've tried this code using TYPE. That didn't work for me.
Posted
Updated 21-Dec-17 7:33am
v3

1 solution

Just a guess:
- 'DBMS_OUTPUT.PUT_LINE' fail if country does not exist. you have to move it so that it execute only if country exist.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900