0

I have one task to solve.

Create a procedure that will list all branches from the locations table based on the specified country_name from the countries table. The listing will be country_name, city, street_address, state_province.Treat the case if there is no branch in the country.Call the procedure in PL / SQL for ‘Zambia‘.

I wrote this, but I have a mistake somewhere. Can you please help me? Thank you.

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE POBOCKY(C_NAME IN COUNTRIES.COUNTRY_NAME VARCHAR2(50)%TYPE)
IS CITY_P LOCATIONS.CITY VARCHAR2(30)%TYPE;
S_ADDRESS LOCATIONS.STREET_ADDRESS VARCHAR2(40)%TYPE;
S_PROVINCE LOCATIONS.STATE_PROVINCE VARCHAR2(25)%TYPE;
BEGIN
SELECT COUNTRY_NAME, CITY, STREET_ADDRESS, STATE_PROVINCE INTO
C_NAME, CITY_P, S_ADDRESS, S_PROVINCE FROM COUNTRIES
FULL OUTER JOIN LOCATIONS ON COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID
GROUP BY COUNTRY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('Error');
RAISE;
END POBOCKY;
Kristina
  • 15
  • 4
  • I think this is wrong: `CITY_P LOCATIONS.CITY VARCHAR2(30)%TYPE`. Should either be `CITY_P LOCATIONS.CITY%TYPE`, to use the type of the column, or just `CITY_P VARCHAR2(30)`, to use varchar2 specifically. It helps if you have a tool that provides detailed compilation info, including the line on which the error is, and what the error is. You could use Oracle's own [SQL Developer](https://www.oracle.com/tools/downloads/sqldev-downloads.html) for free. – GolezTrol May 25 '21 at 20:38
  • Apart from the syntax error(s), once this runs, the query itself will thrown an error if you have more than one row in the table. `select into` can return 1 row exactly. You either have to write a `where` clause to make that happen, or use a cursor to fetch from, so `cursor C_COUNTRIES is ;` and then in code you can use a `for` loop to iterate the cursor, [like this](https://www.oracletutorial.com/plsql-tutorial/plsql-cursor-for-loop/). – GolezTrol May 25 '21 at 20:43

1 Answers1

1
  • if you're inheriting datatypes from columns, then remove explicit datatype
  • if parameter is IN, you can't select into it
  • full outer join? Why? Shouldn't it be inner join ...
    • ... along with WHERE clause so that you'd filter rows by city name passed as a parameter?
  • if there are several rows that can be returned, consider using a loop
    • it also means that you don't have to worry about local variables and (most frequent) exceptions such as no_data_found and too_many_rows
  • it is a good habit to use table aliases and precede column names with them; the way you put it, it is impossible to know which column belongs to which table (so I won't be guessing)

Shortly, something like this:

create or replace procedure pobocky
  (c_name in countries.country_name%type)
is 
begin
  for cur_r in 
    (select city, street_address, state_province 
     from countries join locations on countries.country_id = locations.country_id
     where coutry_name = c_name
    )
  loop
    dbms_output.put_line(c_name ||', '|| cur_r.city ||', '|| cur_r.street_address ||', '||
      cur_r.state_province);
  end loop;
end pobocky;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thank You for your answer, it was very helpful. I tried your code and procedure was compiled. But now when I'm trying to call the procedure like this: ```begin pobocky(Zambia); end;``` I'm getting an error: PLS-00201: the identifier 'ZAMBIA' must be declared. What am I doing wrong? @Littlefoot – Kristina May 25 '21 at 23:02
  • You're welcome. As it is a **string**, you have to enclose it into single quotes: `begin pobocky('Zambia'); end;` – Littlefoot May 26 '21 at 05:14
  • I tried, but now I am getting an error: PLS-00905: the SYSTEM.POBOCKY object is invalid@Littlefoot – Kristina May 26 '21 at 06:55
  • Uh, don't EVER create any of your objects in SYS or SYSTEM schema! Never! As of the error you got: procedure is invalid, you have to find out why. Run the CREATE statement again and then (if you're using SQL*Plus) run SHOW ERR. Otherwise, query USER_ERRORS. – Littlefoot May 26 '21 at 07:57