CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
ezgi
ezgi

Reputation: 35

PLS-00306: wrong number or types of arguments in call to error

I get this error when calling the procedure. I am trying to do this practice.

Create a procedure called find_region_and_currency to fetch and return the currency and region in which a country is located. Pass COUNTRY_NAME as an IN parameter and use a user-defined record as an OUT parameter that returns the country name, its region and currency.

I tried old answer from here but I didnt do it. Could you please help me?

CREATE OR REPLACE PACKAGE traveler_assistance_package
IS

TYPE country_rec IS RECORD (
    country_name COUNTRIES.COUNTRY_NAME%TYPE,
    region COUNTRIES.REGION_ID%TYPE,
    currency COUNTRIES.CURRENCY_CODE%TYPE);
PROCEDURE find_region_and_currency
(p_country_name   IN COUNTRIES.country_name%TYPE,
p_country_rec     OUT country_rec);
END traveler_assistance_package;
CREATE OR REPLACE package body traveler_assistance_package 
IS
PROCEDURE find_region_and_currency 
(p_country_name   IN countries.country_name%TYPE,
p_country_rec     OUT country_rec)
IS
BEGIN 
SELECT country_name, region_id, currency_code INTO p_country_rec
FROM COUNTRIES
where COUNTRY_NAME = p_country_name;
DBMS_OUTPUT.PUT_LINE('Country Name:'||p_country_rec.country_name ||  
  'Region:' || p_country_rec.region || 
  'Currency:' || p_country_rec.currency);
END;
END traveler_assistance_package;

Upvotes: 0

Views: 3468

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

You didn't post what went wrong (which part of code); anyway, here you go.

Sample table:

SQL> create table countries
  2    (country_name    varchar2(20),
  3     region_id       varchar2(20),
  4     currency_code   varchar2(20));

Table created.

SQL> insert into countries values ('Croatia', 'A', 'kn');

1 row created.

Package:

SQL> CREATE OR REPLACE PACKAGE traveler_assistance_package
  2  IS
  3    TYPE country_rec IS RECORD (
  4      country_name COUNTRIES.COUNTRY_NAME%TYPE,
  5      region COUNTRIES.REGION_ID%TYPE,
  6      currency COUNTRIES.CURRENCY_CODE%TYPE);
  7    PROCEDURE find_region_and_currency
  8      (p_country_name   IN COUNTRIES.country_name%TYPE,
  9       p_country_rec     OUT country_rec);
 10  END traveler_assistance_package;
 11  /

Package created.

Package body:

SQL> CREATE OR REPLACE package body traveler_assistance_package
  2  IS
  3    PROCEDURE find_region_and_currency
  4      (p_country_name   IN countries.country_name%TYPE,
  5       p_country_rec     OUT country_rec)
  6    IS
  7    BEGIN
  8      SELECT country_name, region_id, currency_code INTO p_country_rec
  9      FROM COUNTRIES
 10      where COUNTRY_NAME = p_country_name;
 11
 12      --dbms_output.put_line('Printing from FIND_REGION_AND_CURRENCY');
 13      --DBMS_OUTPUT.PUT_LINE('Country Name:'||p_country_rec.country_name ||
 14      --  ' Region:' || p_country_rec.region ||
 15      --  ' Currency:' || p_country_rec.currency);
 16    END;
 17  END traveler_assistance_package;
 18  /

Package body created.

Testing:

SQL> set serveroutput on;
SQL> declare
  2    l_out traveler_assistance_package.country_rec;
  3  begin
  4    traveler_assistance_package.find_region_and_currency ('Croatia', l_out);
  5
  6    dbms_output.put_line('Printing from anonymous PL/SQL block');
  7    dbms_output.put_line(l_out.country_name ||', '|| l_out.currency);
  8  end;
  9  /
Printing from anonymous PL/SQL block
Croatia, kn

PL/SQL procedure successfully completed.

Upvotes: 2

Related Questions