Lua: Return value from query

[mgasperi_work]

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

[mgasperi_work · Accepted solution]

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')