Powered By Blogger

Saturday, October 17, 2015

Oracle function with dynamic SQL

   Following is an example of a function which dynamically creates an SQL statement using one of its input parameters and executes the same. And also, the query consists of place holders, which are replaced when executing (like in a prepared statement).

==============================================================

CREATE OR REPLACE
FUNCTION DASH_BOARD_ABW (YOP IN NUMBER, BU IN NUMBER, FIN_Y IN VARCHAR2, MON IN VARCHAR2)
  RETURN NUMBER AS
    query_str VARCHAR2(500) := NULL;
    abw NUMBER := 0;
  BEGIN
  query_str := 'select ' || MON || ' from ahl_fas_dash_board_abw where trim(fin_year) = :FIN_Y and bu_id = :BU and yop = :YOP and rownum = 1';
  EXECUTE IMMEDIATE query_str INTO abw
  USING FIN_Y, BU, YOP;
  RETURN abw;
END DASH_BOARD_ABW;


==============================================================

   The result of the execution is assigned to a variable 'abw' which is returned. 'EXECUTE IMMEDIATE' executes the query and 'USING' clause is to substitute placeholders of the query respectively. 

thanks,
Shyarmal.