Getting error while running SQL query in Procedure in Oracle

Question

Tring to create a simple procedure that takes a input and performs a Select Query as below:

CREATE OR REPLACE PROCEDURE test3(br IN VARCHAR2)
AS
    a varchar(32);
BEGIN
    SELECT * FROM BRANDS B WHERE B.BRAND_ID = br  INTO a;
    dbms_output.put_line(a);
END;

I am getting the following error:

Errors: PROCEDURE TEST3
Line/Col: 5/1 PL/SQL: SQL Statement ignored
Line/Col: 5/47 PL/SQL: ORA-00933: SQL command not properly ended

Table is:

BRAND_ID	NAME	ADDRESS	JOIN_DATE	PASSWORD
Brand01	Brand X	503 Rolling Creek Dr Austin, AR	04/01/2021	12345abcde
Brand02	Brand Y	939 Orange Ave Coronado, CA	03/25/2021	12345abcde
Brand03	Brand Z	20 Roszel Rd Princeton, NJ	05/08/2021	12345abcde

Answer 1

• You want the INTO clause before the FROM.
• You need a %ROWTYPE variable with SELECT *.
• Following on from the previous point, you cannot use DBMS_OUTPUT.PUT_LINE() on a record; you need to extract the individual fields.
• You should handle the NO_DATA_FOUND exception.
• Do NOT store passwords as plain-text. Store a one-way hash of the password.

Which gives:

CREATE OR REPLACE PROCEDURE test3(
  br IN BRANDS.BRAND_ID%TYPE
)
AS
  a BRANDS%ROWTYPE;
BEGIN
  SELECT *
  INTO   a
  FROM   BRANDS
  WHERE  BRAND_ID = br;
  
  dbms_output.put_line(
    a.brand_id
    || ' ' || a.name
    || ' ' || a.address
    || ' ' || a.join_date
    || ' ' || a.password
  );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

Then:

BEGIN
  test3('Brand02');
END;
/

Outputs:

Brand02 Brand Y 939 Orange Ave Coronado, CA 25-MAR-21 12345abcde

db<>fiddle here