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.
==============================================================
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.