CODEWITHSUNDEEP
oracle-databaseplsql
DKCroat
DKCroat

Reputation: 355

Procedure is compiled but when calling procedure and passing parameters I would receive error

I have created procedure for name change but when calling procedure I would receive error: Error report: ORA-06550: line 4, column 5: PLS-00306: wrong number or types of arguments in call to 'PRODCHANGE_SP' procedure:

    create or replace 
    PROCEDURE PRODCHANGE_SP 
    (
    P_PRODID IN bb_product.idproduct%TYPE 
    , P_NAME IN OUT bb_product.productname%TYPE
    ) AS 
    BEGIN
      UPDATE bb_product
      SET productname = p_name
      WHERE idproduct = p_prodid;
   COMMIT;
   END PRODCHANGE_SP;

BLOCK, calling procedure to check if is working:

 DECLARE
    lv_pname_txt  bb_product.productname%TYPE;
BEGIN
    prodchange_sp(1, 'CapressoBar Model #352', lv_pname_txt);
    DBMS_OUTPUT.PUT_LINE(lv_pname_txt);
END;

Upvotes: 0

Views: 48

Answers (1)

ArtBajji
ArtBajji

Reputation: 960

You need to fetch the old value before you update it. After update, overwrite the out variable with the old value.

create or replace PROCEDURE PRODCHANGE_SP 
(
    P_PRODID IN bb_product.idproduct%TYPE, 
    P_NAME IN OUT bb_product.productname%TYPE
) AS 
   P_NAME_old bb_product.productname%TYPE;
BEGIN
    SELECT productname
      INTO P_NAME_old
      FROM bb_product
     WHERE idproduct = p_prodid;

      UPDATE bb_product
      SET productname = p_name
      WHERE idproduct = p_prodid;

    COMMIT;

    P_NAME := P_NAME_old;

END PRODCHANGE_SP;

You need to pass the value in the same variable as it is IN OUT. Rewrite the code to invoke procedure as below.

DECLARE
    lv_pname_txt  bb_product.productname%TYPE;
BEGIN
    lv_pname_txt := 'CapressoBar Model #352';
    prodchange_sp(1, lv_pname_txt);
    DBMS_OUTPUT.PUT_LINE(lv_pname_txt);
END;

Upvotes: 2

Related Questions