Issue

This Content is from Stack Overflow. Question asked by Thevarungrovers

I want to convert Canadian dollars to other currencies with some convention.
I am creating a procedure and taking the input of amount in number and char to convert the currency at runtime but the procedure is getting created with some errors.

Here is the code.

CREATE OR REPLACE PROCEDURE conversion(p_cad_dollars IN NUMBER, p_new_currency IN VARCHAR2(10)) AS
v_result NUMBER;
v_new_currency VARCHAR2(20);
BEGIN
    if p_new_currency = 'E' THEN
        v_result := p_cad_dollars * 1.50;
        v_new_currency := 'EURO';
    ELSIF p_new_currency = 'Y' THEN
        v_result := p_cad_dollars * 100;
        v_new_currency := 'YEN';
    ELSIF p_new_currency = 'V' THEN
        v_result := p_cad_dollars * 10000;
        v_new_currency := 'Viet Nam DONG';
    ELSIF p_new_currency = 'Z' THEN
        v_result := p_cad_dollars * 1000000;
        v_new_currency := 'Endora ZIP';
    END IF;
    DBMS_OUTPUT.PUT_LINE('For "' || p_cad_dollars || '" dollars Canadian, you will have "' || v_result || '" ' || v_new_currency);
END;
/

I am getting this error after compilation

HOW CAN I GET RID OF THIS ERROR AND GET THE DESIRED RESULT

Solution

p_new_currency parameter is declared wrong. Drop the (10). Procedure parameters cannot state a size. Refer to Oracle documentation, specifically PL/SQL Language Reference

CREATE OR REPLACE PROCEDURE conversion(p_cad_dollars  IN NUMBER,
                                       p_new_currency IN VARCHAR2) AS
  v_result       NUMBER;
  v_new_currency VARCHAR2(20);
BEGIN
  if p_new_currency = 'E' THEN
    v_result       := p_cad_dollars * 1.50;
    v_new_currency := 'EURO';
  ELSIF p_new_currency = 'Y' THEN
    v_result       := p_cad_dollars * 100;
    v_new_currency := 'YEN';
  ELSIF p_new_currency = 'V' THEN
    v_result       := p_cad_dollars * 10000;
    v_new_currency := 'Viet Nam DONG';
  ELSIF p_new_currency = 'Z' THEN
    v_result       := p_cad_dollars * 1000000;
    v_new_currency := 'Endora ZIP';
  END IF;
  DBMS_OUTPUT.PUT_LINE('For "' || p_cad_dollars ||
                       '" dollars Canadian, you will have "' || v_result || '" ' ||
                       v_new_currency);
END;
/

This Question was asked in StackOverflow by Thevarungrovers and Answered by Abra It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.