Dynamic SQL Statement in a Function

Problem: 

If we don't have configurations such as Custom Fields, Custom Views etc. versioned control in git repository, we cannot use it directly in our logics. It will give build errors when the sanity runs in IFS Cloud.

To overcome this problem we can write SQL statement as dynamic statement to avoid build errors. Below is a sample function created for that.

FUNCTION Get_Mapping ( 

   name_          IN VARCHAR2,  

   source_value_  IN VARCHAR2 ) RETURN VARCHAR2 

IS 

   stmt_    VARCHAR2(4000); 

   ret_     VARCHAR2(2000); 

BEGIN 

   IF (Installation_SYS.View_Exist('c_trax_mapping_clv')) THEN 

      stmt_ := ' 

               DECLARE 

                  mapped_value_ varchar2(1000); 

                  CURSOR get_data(in_name_ VARCHAR2, in_source_val_ VARCHAR2) IS 

                     SELECT cf$_mapped_value 

                     FROM   cs_trax_mapping_clv 

                     WHERE  cf$_name = in_name_ AND cf$_source_value = in_source_val_; 

               BEGIN 

                  OPEN  get_data(:s1, :s2); 

                  FETCH get_data INTO :s3; 

                  IF get_data%found THEN 

                     CLOSE get_data; 

                  ELSE 

                     CLOSE get_data; 

                  END IF; 

                  OPEN  get_data(:s1, ''*''); 

                  FETCH get_data INTO :s3; 

                  IF get_data%found THEN 

                     CLOSE get_data; 

                  ELSE 

                     CLOSE get_data; 

                  END IF; 

               END;'; 

      @ApproveDynamicStatement(2023-05-24,ASANKA) 

      EXECUTE IMMEDIATE stmt_ USING IN name_, source_value_, OUT ret_; 

   END IF; 

   RETURN ret_; 

   RAISE_APPLICATION_ERROR(-20001, 'No valid mapping for ' || source_value_ || ' in ' || name_); 

END Get_Mapping;