How to get character input at runtime from a procedure in PL/SQL-CodePudding

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?

CodePudding user response：

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;
/