Hi,
I am trying to create functions which uses IF ELSE loops depending on some user data(which can vary) using dynamic sql for automating some process. Currently, I am plainly using string(NVARCHAR(5000)) and concatenating in a FOR LOOP for writing the syntax of functions. However, at some point, the final string reaches the maximum chars value.
Example: in this code snippet, the CREATE_LEVEL_IF_BLOCK is exceeding the max range of NVARCHAR(500) sometimes.
To resolve this issue, I am planning to write the string variables (containing the syntax of the functions) into a file and then execute the file. Does anyone knows what is the syntax of writing into a file and executing it or any better way to solve this issue ?
FYI: In any case, static function creation is not an option currently.
FOR i in 1..hierarchyLevelCount DO
DECLARE levels NVARCHAR(10);
DECLARE RANGES TABLE(RESULT NVARCHAR(50));
DECLARE totalRows INTEGER;
DECLARE cnt INTEGER = 0;
DECLARE CURSOR RANGES_CURSOR FOR SELECT "RESULT" FROM :RANGES;
IF i = 1 THEN
CREATE_LEVEL_IF_BLOCK := CREATE_LEVEL_IF_BLOCK ||
'IF (level = 1) THEN ';
ELSE
CREATE_LEVEL_IF_BLOCK := CREATE_LEVEL_IF_BLOCK ||
'ELSEIF (level = 2) THEN ';
END IF;
SELECT "WORDS" INTO levels FROM DIGITS_TO_WORDS WHERE "DIGITS" = i;
GET_HIERARCHIES_FROM_JSON_DQUERY :=
'SELECT JSON_QUERY("Parameters", ''$.Hierarchy_levels.' || levels || ''') FROM CONFIG'
' WHERE "Column" = ''' || columnName || ''';';
EXECUTE IMMEDIATE GET_HIERARCHIES_FROM_JSON_DQUERY INTO hierarchies;
SELECT TRIM ('[]' FROM hierarchies) "trim both" INTO hierarchies FROM DUMMY;
RANGES = LIB2:SPLIT_TO_TABLE(hierarchies, ',');
SELECT COUNT(*) INTO totalRows FROM :RANGES;
FOR C_ROW AS RANGES_CURSOR DO
DECLARE eachRange NVARCHAR(50);
DECLARE lowerRange, upperRange INT;
cnt := cnt + 1;
SELECT TRIM ('"' FROM C_ROW."RESULT") "trim both" INTO eachRange FROM DUMMY;
IF cnt = totalRows THEN
CREATE_RANGES_IF_BLOCK := CREATE_RANGES_IF_BLOCK ||
'ELSE outValue := ''' || eachRange || '''; END IF; ';
ELSE
(lowerRange, upperRange) = LIB2:SPLIT(eachRange, '-');
IF cnt = 1 THEN
CREATE_RANGES_IF_BLOCK := CREATE_RANGES_IF_BLOCK ||
'IF (num >= ' || lowerRange || ' AND num <= ' || upperRange || ') THEN '
'outValue := ''' || eachRange || '''; ';
ELSE
CREATE_RANGES_IF_BLOCK := CREATE_RANGES_IF_BLOCK ||
'ELSEIF (num >= ' || lowerRange || ' AND num <= ' || upperRange || ') THEN '
'outValue := ''' || eachRange || '''; ';
END IF;
END IF;
END FOR;
CREATE_LEVEL_IF_BLOCK := CREATE_LEVEL_IF_BLOCK || CREATE_RANGES_IF_BLOCK;
END FOR;<br>