Hello Forum.
I need to put the content of the query output into the body of my OdiSendMail tool.
For single-row values, it's easy. I can just do a refresh variable with the query in it, and then use the variable in my email body.
For multi-row values, the refresh variable only picks up the first row of the query output. So, that won't work.
I tried to use OdiSqlUnload tool to spool the query contents to an OS level file and then use the file in my email. However, using this tool is problematic. I need to hardcode username and password values for my database and those values vary from database env to database env.
There is an article I found that talks about how to source the values from the Sunopsis API, but it doesn't work. I don't have Sunopsis API as an available technology in my procedure (I am on 12.2.1.2.6). I use ODI Tools as the technology for the target command.
My target command in the procedure looks like this:
OdiSqlUnload "-FILE=/usr/tmp/odi_publish_process_statuses.rpt" "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
select process_id||': '||count(*)
from URDW.PROCESS_STEP_STATUSES
where message like '%Failed%'
and record_date > sysdate - 1
group by process_id
UNION ALL
SELECT 'No errors today'
FROM dual
WHERE NOT EXISTS
(
select process_id||': '||count(*)
from URDW.PROCESS_STEP_STATUSES
where message like '%Failed%'
and record_date > sysdate - 1
group by process_id
)
When I run the scenario with this procedure, I get:
java.lang.IllegalArgumentException: Driver class name must not be empty
and looking at the generated code, I see that that ODI did not substitute the parameters, as the article suggested it would:
OdiSqlUnload "-FILE=/usr/tmp/odi_publish_process_statuses.rpt" "-DRIVER=" "-URL=" "-USER=" "-PASS=<@=odiRef.getInfo("SRC_ENCODED_PASS") @>" "-FILE_FORMAT=VARIABLE" "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
select process_id||': '||count(*)
from URDW.PROCESS_STEP_STATUSES
where message like '%Failed%'
and record_date > sysdate - 1
group by process_id
UNION ALL
SELECT 'No errors today'
FROM dual
WHERE NOT EXISTS
(
select process_id||': '||count(*)
from URDW.PROCESS_STEP_STATUSES
where message like '%Failed%'
and record_date > sysdate - 1
group by process_id
)
Any idea on how to make this work, or more broadly, a better / simpler way of getting results on a multi-row query into a variable that can further be used in other parts of the package, such as OdiSendMail?
Thank you
Boris