Forum Discussion

johandewaardt's avatar
johandewaardt
Regular Visitor
6 years ago

Oracle Alter Session in ODBC query

I need to run an ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY' command before I can execute another Oracle query successfully. This query uses an Oracle function that returns a table: 


SELECT
IM_MEASURES_PER_DAY.METRIC_DATE,
IM_MEASURES_PER_DAY.STATUS_OPEN,
IM_MEASURES_PER_DAY.STATUS_OVERDUE,
IM_MEASURES_PER_DAY.CLOSED,
IM_MEASURES_PER_DAY.OPENEN,
IM_MEASURES_PER_DAY.CUMMULATIVE_CLOSED
FROM
(

SELECT CAST(TO_DATE(METRIC_PERIOD,'YYYYMMDD') AS DATE) AS METRIC_DATE,
ST_OPEN AS STATUS_OPEN,
ST_OVERDUE AS STATUS_OVERDUE,
CLOSED AS CLOSED,
OPENED AS OPENEN,
CUM_CLOSED AS CUMMULATIVE_CLOSED
FROM TABLE
(
d_get_gra4_DAY /* GRA4: DAY level */

(IP_SDATE => '01-01-2019'
,IP_FDATE => '07-30-2019'
,IP_ENTITY => IM_USERVCHARTAB('ALL')
,IP_SUBENTITY => IM_USERVCHARTAB('ALL')
,IP_LOCATION_IMPACTED => IM_USERVCHARTAB('ALL')
,IP_LOCATION_ORIGINATED => IM_USERVCHARTAB('ALL')
,IP_PRIORITY => IM_USERVCHARTAB('ALL')
,IP_DATA_LABEL => IM_USERVCHARTAB('ALL')
,IP_RAISER_NAME => IM_USERVCHARTAB('ALL')
,IP_OWNER_DEPARTMENT => IM_USERVCHARTAB('ALL')
,IP_OWNER_NAME => IM_USERVCHARTAB('ALL')
,IP_COORDINATOR_NAME => IM_USERVCHARTAB('ALL')
,IP_OVERDUE => IM_USERVCHARTAB('ALL')
,IP_DQ_TASKFORCE => IM_USERVCHARTAB('ALL')

)
)
) IM_MEASURES_PER_DAY

 

In the From ODBC dialog, Advanced Options, SQL Statement it seems not possible to enter multiple SQL statements. I tried to separate them with a semi-colon; tried to embed the statements in a BEGIN-END block, but all I get is errors. 

 

Is there a way in Power Bi to initialize an Oracle or ODBC connection with an ALTER SESSION statement?

I know this can be achieved on the database level, but I need it on a session level.

 

Rgds,

-johan