Passing column name as parameter in Oracle PL_SQL function

November 12, 2021

I am trying to pass two input parameters, column name and table name . Then the function should output a string value as defined below :

create or replace function get_id5(in_col_name IN VARCHAR2,in_tbl_name IN VARCHAR2)
  return VARCHAR2
is
  /*in_col_nm varchar(32) := in_col_name;
  /*in_tbl_nm varchar(64) := in_tbl_name; */
  integer_part  NUMBER ;
  integer_part_str VARCHAR2(32) ;
  string_part VARCHAR2(32) ;
  full_id VARCHAR2(32) ;
  out_id VARCHAR(32) ;

BEGIN
/*select MAX(in_col_nm) INTO full_id FROM  in_tbl_nm ;  */
execute immediate 'select MAX('||in_col_name||') FROM' || in_tbl_name ||'INTO' || full_id;
/*select regexp_replace(full_id , '[^0-9]', '') INTO integer_part_str  , regexp_replace(full_id , '[^a-z and ^A-Z]', '') INTO string_part from dual ; */
integer_part_str := regexp_replace(full_id , '[^0-9]', '') ;
string_part := regexp_replace(full_id , '[^a-z and ^A-Z]', '') ;
integer_part := TO_NUMBER(integer_part_str);
integer_part  := integer_part  + 1 ;
integer_part_str  := TO_CHAR(integer_part) ;
out_id  :=  string_part + integer_part_str   ;
return out_id;
END;

I have a table named BRANDS in Database and the max value for column BRAND_ID is ‘Brand05’. The expected output is ‘Brand06’.

However when i run:
select get_id5('BRAND_ID' , 'BRANDS') from dual;

DECLARE
a VARCHAR(32) ;
BEGIN
a := get_id5('BRAND_ID' , 'BRANDS');
END;

I am getting the below error:

ORA-00923: FROM keyword not found where expected

>Solution :

You need spaces between the FROM and the table_name and the INTO should be outside of the SQL text:

execute immediate 'select MAX('||in_col_name||') FROM ' || in_tbl_name INTO full_id;

You could also use DBMS_ASSERT:

execute immediate 'select MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(in_col_name)||') FROM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(in_tbl_name) INTO full_id;

Then you need to use || as the string concatenation operator (rather than +).

Your function could be:

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

For the sample data:

CREATE TABLE brands (brand_id) AS
SELECT 'BRAND5' FROM DUAL;

Then:

select get_id5('BRAND_ID' , 'BRANDS') AS id from dual;

Outputs:

ID

BRAND6

ID
BRAND6

db<>fiddle here

A better solution

To generate the number, use a SEQUENCE or, from Oracle 12, an IDENTITY column and, if you must have a string prefix then use a virtual column to generate it.

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

SELECT * FROM brands;

Outputs: