Use outcome of SQL query as value for variable

Question

I have a query that extracts performance measurements of a number of APIs and those I want to save over time to different files in one folder. Say every hour one run and one output file.

The Invantive scripting statement

local export results as "${exportfilename}" format xml

Can do this when you have exportfilename correctly set up.

With Oracle SQL*Plus you can memorize the outcome of a query in a variable with the column ... new_value syntax.

How can I set exportfile using the outcome of an Invantive SQL query?

Answer 1

Solution was to use the ${outcome:row,column} syntax as in:

local define outfolder "c:\temp"

select sdy3.value || '-' || lpad(year(sysdate), 4, '0') || lpad(month(sysdate), 2, '0') || lpad(day(sysdate), 2, '0') || lpad(hour(sysdate), 2, '0') || lpad(minute(sysdate), 2, '0') ||'.xml' file_name
from   exactonlinerest..systemdatacontainerproperties sdy1
join   exactonlinerest..systemdatacontainerproperties sdy2
on     sdy2.data_container_alias = 'default'
and    sdy2.name = 'provider-description'
join   exactonlinerest..systemdatacontainerproperties sdy3
on     sdy3.data_container_alias = 'default'
and    sdy3.name = 'provider-short-name'
where  sdy1.data_container_alias = 'default'
and    sdy1.name = 'data-container-id'

local define exportfilename "${outfolder}\${outcome:0,0}"

<<< Run actual SQL>>>

local export results as "${exportfilename}" format xml

The ${outcome:...,...} syntax puts the string representation of the respective row number (0..max) and column number (0..max) as a value into the indicated variable name.