BusinessObjects Board

Automatically apply Oracle NLS values during report runtime

building semantic layer
#1

Hi all,

My question resides around whether or not it is possible within Designer to automatically set the NLS values at execution of a query.

I need to set the following values every time a query is run over a specific database:

dbms_application_info.set_client_info(906);
execute immediate 'alter session set nls_language="AMERICAN"';

Is it even possible to do this and if so how.

The one thing I don’t want to do (and I don’t even know if this would work) is to create a dimension in the universe with this information and then make that dimension the first in my report. Reason I don’t want to do this is that the particular universe that is being built will be used by many people.

Any ideas would be greatful.

Thanks

plessiusa :netherlands: (BOB member since 2004-03-22)

Append set command to the query
Editing SQL generation parameters
#2

plessiusa,

Im not sure why you are trying to execute this code in every query? What does this do? Arent these parameters set at the database level, and utilized by every query fired against it. I dont think you need to execute this code. All you need via your BO report is to create the query. Im not even sure how you will get this code to execute via a dimension. because what you put in dimensions are transformed into the select part of a query.

Roshknee :india: (BOB member since 2007-10-29)

#3

Did you check ‘ConnectInit’ option while making connections in Designer. In Custom Parameters Window…?

BO_Chief :us: (BOB member since 2004-06-06)

#4

BO_Chief, thanks for the hint, I’ll give this ago

Roshknee, The reason that this codes needs to be added is due to data ristrictions applied to the user id that uses the data. I don’t fully understand it myself, but it has something to do with the way that Oracle Financials works and it ultimately stops unauthorised people from creating db accounts and accessing data they are not permitted to see. Apparently with BO and Oracle Financials, the values are different and if you don’t sync them, your query simply returns no data.

Not a great explanation, but then I’m no DBA and don’t know a lot about Oracle Financials.

plessiusa :netherlands: (BOB member since 2004-03-22)

#5

Well I tried that and when I test the universe connection, I get the error ‘Invalid SQL statement’.

I have tried putting in both lines of code, but also tried them individually and got the same result. Might just be doing it wrong, but do you have any other ideas?

plessiusa :netherlands: (BOB member since 2004-03-22)

#6

Hi.
You can create a store procedure with: dbms_application_info.set_client_info(906)
and try to exec from ConnectInit.
I think that works fine.
Good luck

Jaimepe :es: (BOB member since 2006-12-10)

#7

I agree with Jaimepe, it works with a STORED PROCEDURE.

Also check this recent thread… and look for Frederick’s 2nd post.
https://bobj-board.org/t/99622

BO_Chief :us: (BOB member since 2004-06-06)

#8

Thanks guys for your help, below is the code that worked.

begin dbms_application_info.set_client_info(906);  execute immediate 'alter session set nls_language="AMERICAN"'; end;

plessiusa :netherlands: (BOB member since 2004-03-22)

#9

Hi All

Thanks for sharing knowledge on this issue.

I tried giving following values in the BO Universe Connection->Custom Parameters->ConnectInit parameter but getting following error.


begin execute immediate 'alter session set NLS_CHARACTERSET="AL32UTF8"'; end;

Error-

Exception: DBD, ORA-00922: missing or invalid option ORA-06512: at line 1 State: N/A

But when using the nls_language parameter instead of nls_characterset then there is no issue in executing BO query.

Appreciate your thoughts on this issue.

patneel :india: (BOB member since 2006-11-02)