Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE brands (brand_id) AS
SELECT 'BRAND5' FROM DUAL;
1 rows affected

create or replace function get_id5(
  in_col_name IN VARCHAR2,
  in_tbl_name IN VARCHAR2
) RETURN VARCHAR2
IS
  full_id VARCHAR2(32);
BEGIN
  execute immediate 'select MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(in_col_name)||') '
                  || 'FROM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(in_tbl_name)
                  INTO full_id;
  return regexp_replace(full_id , '\d+', '')
         || TO_CHAR(regexp_replace(full_id , '\D+', '') + 1);
END;
/
select get_id5('BRAND_ID' , 'BRANDS') As id from dual;


ID


BRAND6


DROP TABLE brands;
CREATE TABLE brands(
  ID NUMBER
     GENERATED ALWAYS AS IDENTITY
     PRIMARY KEY,
  brand_id VARCHAR2(10)
           GENERATED ALWAYS
             AS (CAST('BRAND' || TO_CHAR(id, 'FM000') AS VARCHAR2(10)))
);
BEGIN
  INSERT INTO brands (id) VALUES (DEFAULT);
  INSERT INTO brands (id) VALUES (DEFAULT);
  INSERT INTO brands (id) VALUES (DEFAULT);
END;
/
1 rows affected

SELECT *
FROM   brands;


ID
BRAND_ID


1
BRAND001

2
BRAND002

3
BRAND003