plsql error " Encountered the symbol ( when expecting one of the following: :-) " on line "drug_name IN CHAR(255),"

user_15973023 0
Viewed 39
CREATE OR REPLACE
PROCEDURE ADD_DRUG_TO_ORDER
  (
    order_id  IN INT,
    drug_name IN CHAR(255),
    quantity  IN INT
  )
AS
  drug MEDICINE%ROWTYPE;
    insufficient_quantity EXCEPTION;
  BEGIN
    SELECT *
    INTO drug
    FROM MEDICINE
    WHERE 'drug_name' = drug_name;
plsql stored-procedures oracle
1 Answers

Cause of error you reported is that parameters can't have size, so it is not char(255) but char. However, you'd rather use varchar2 (char is right-padded to its full size by spaces).

Other than that, you should not name parameters as column names because you'll get unexpected results.

Something like this might be a starting point for you:

SQL> create or replace procedure add_drug_to_order
  2    (par__order_id  in int,
  3     par_drug_name  in varchar2,
  4     par_quantity   in int
  5    )
  6  is
  7    l_drug medicine%rowtype;
  8  begin
  9    select *
 10      into l_drug
 11      from medicine
 12      where drug_name = par_drug_name;
 13  end;
 14  /

Procedure created.

SQL>
user_90979060
Related