Our DBA wants us to run an ‘alter session set sort_area_size = 50000000’ before a specific report runs.
I tried to modify the SQL in the report and add the statement but I get an ORA-02248: invalid option for ALTER SESSION error. I already disabled the Restrict SQL to “Select” Only option so that’s not the problem.
I tried to use a / and ; as delimiter between teh ALTER SESSION and SELECT statements but that doesn’t seem to work.
Is what I want to do…doable ? I don’t want to set this option at the connection level because as far as we know, the only problem report is the one I’m trying to modify.
Try to use alter session in a function and call that function in the freehand sql window. i am not sure can we use alter session command in a function. Pl try this
follow the steps in bo reporter:
1.add a new variable with the name “dummy”,it’s a const of any value
2.add a new table, query on the same universe
3.the new table includes only the “dummy” variable
4.hide the new table
5.open vba window
6.in the "before refresh "event,
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim dp As DataProvider
Set dp = ThisDocument.DataProviders.Item(2)
dp.SQL = "alter session set sort_area_size = 50000000'"
dp.refresh
7,press the "refresh button,the “alter …” statement should have been executed
but the problem is there will be alert msgbox “no data retrived…”
who knows how to remove this prompt?