03-08-2020 01:03 PM
Hi!
I've some problems with replacement for schema in Lua - here a sample:
--/
CREATE OR REPLACE LUA SCRIPT "MYSCRIPT" (view_name, target_schema) AS
res = query([[ SELECT VIEW_TEXT
FROM EXA_ALL_VIEWS
WHERE VIEW_SCHEMA = 'SBX_RMG_ZV_TRNS'
AND VIEW_NAME = :vw
]], {vw=view_name})
print(res.statement_text)
newdef = {}
for i=1, #res do
if res[i][2] ~= nil then
newdef = string.gsub(res[i][2], "SBX_RMG_ZV_TRNS", target_schema)
end
end
/
EXECUTE SCRIPT MYSCRIPT('RULE_ENGINE_HAS_CANCELLATION', 'WLNGAMC') WITH OUTPUT
It seems like res[i][2] is not set, but actually when I execute the statement which has been generated, I can see the SQL which generates the view.
The script should extract the definition of the view and just replace the schema.
Thanks in advance!
Michael
Solved! Go to Solution.
03-08-2020 02:09 PM - edited 03-08-2020 04:36 PM
I've solved it on my own - seems like newdef = {} must be removed than it works like a charm.
--/
CREATE OR REPLACE LUA SCRIPT "TRANSACTION_CATEGORIZATION_RECREATE_VW_RULE_ENGINE" (ARRAY view_name, source_schema, target_schema) AS
-- create string from array of input views
view_str = [[']]
for i = 1, #view_name do
view_str = view_str..view_name[i].."','"
i = i + 1
end
-- cut last comma
view_str = string.sub(view_str, 1, string.len(view_str)-2)
-- create query from concatenate string
qry_text = [[ SELECT VIEW_TEXT FROM EXA_ALL_VIEWS WHERE VIEW_SCHEMA = ']]..source_schema..[[' AND VIEW_NAME IN ( ]]..view_str..[[)]]
res = query(qry_text)
for i = 1, #res do
if res[i]["VIEW_TEXT"] ~= nil then
newdef = string.gsub(tostring(res[i]["VIEW_TEXT"]), source_schema..".TRNS", target_schema..".TRNS_SINGLE")
query(newdef)
i = i + 1
end
end
/
EXECUTE SCRIPT TRANSACTION_CATEGORIZATION_RECREATE_VW_RULE_ENGINE(ARRAY ('VIEW1','VIEW2'), 'SBX_RMG_ZV_TRNS', 'WLNGAMC')
03-08-2020 02:09 PM - edited 03-08-2020 04:36 PM
I've solved it on my own - seems like newdef = {} must be removed than it works like a charm.
--/
CREATE OR REPLACE LUA SCRIPT "TRANSACTION_CATEGORIZATION_RECREATE_VW_RULE_ENGINE" (ARRAY view_name, source_schema, target_schema) AS
-- create string from array of input views
view_str = [[']]
for i = 1, #view_name do
view_str = view_str..view_name[i].."','"
i = i + 1
end
-- cut last comma
view_str = string.sub(view_str, 1, string.len(view_str)-2)
-- create query from concatenate string
qry_text = [[ SELECT VIEW_TEXT FROM EXA_ALL_VIEWS WHERE VIEW_SCHEMA = ']]..source_schema..[[' AND VIEW_NAME IN ( ]]..view_str..[[)]]
res = query(qry_text)
for i = 1, #res do
if res[i]["VIEW_TEXT"] ~= nil then
newdef = string.gsub(tostring(res[i]["VIEW_TEXT"]), source_schema..".TRNS", target_schema..".TRNS_SINGLE")
query(newdef)
i = i + 1
end
end
/
EXECUTE SCRIPT TRANSACTION_CATEGORIZATION_RECREATE_VW_RULE_ENGINE(ARRAY ('VIEW1','VIEW2'), 'SBX_RMG_ZV_TRNS', 'WLNGAMC')
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In