topic Re: How to change nls_sort in tOracle_Output component. in Talend Studio

How to change nls_sort in tOracle_Output component.

_AnonymousUser — Thu, 10 Jan 2013 13:43:51 GMT

In same Database, I use a tOracle_Input component to select rows in table with a comand like 'select * from table'. Then I use a tMap component without doing any transformation. And, so I use a tOracle_Output component to insert or update other table with input data. The input and output tables are identical and have a Primary Key with datatype varchar2. The problem is: when the tOracle_Output component works to insert or update the row, it sends a comand to database with a convert clause like 'filter(NLSSORT("IG",'nls_sort=''WEST_EUROPEAN''')=NLSSORT(:1,'nls_sort=''WEST_EUROPEAN'''))'. This clause avoid the use of index, which forces the database to make a full table scan.
As I'm talking about the same database, and then, I'm talking about the same 'nls_sort', I think that, when the tOracle_Input component gets the rows, then it also converts to a nls_sort 'WEST_EUROPEAN'. If I can change the default nls_sort of the talend or its component, I can solve the performance problem. Do you have any other suggestions ? How can I change the nls_sort default of the talend or its component ?
Regards,
Marcio.

Re: How to change nls_sort in tOracle_Output component.

Anonymous — Thu, 10 Jan 2013 14:58:23 GMT

You can set this parameter for the connection. I would recommend using one connection for all database components within a job and at first using tOracleRow to set the nls_sort parameter (sorry, I don't how, but I know there are a SQL command to do that).
tOracleConnection -> OnSubjobOk -> tOracleRow (changing the connection parameter) -> OnSubjobOk -> your work to do....

Re: How to change nls_sort in tOracle_Output component.

_AnonymousUser — Fri, 11 Jan 2013 16:16:07 GMT

_________________________________________________________________________
You can set this parameter for the connection. I would recommend using one connection for all database components within a job and at first using tOracleRow to set the nls_sort parameter (sorry, I don't how, but I know there are a SQL command to do that).
tOracleConnection -> OnSubjobOk -> tOracleRow (changing the connection parameter) -> OnSubjobOk -> your work to do....
Last edited by jlolling (Yesterday 15:59:21)
Talend Certified working for cimt objects AG in Berlin
_________________________________________________________________________
I tried to make this but, it didn´t work. First I need to have 2 connection, 1 for input and other to output, because of the grant of each user connections in each table, of each schema. I have same database but, differents schemas and tables.
So, I tried with: tOracleConnection (for tOracleOutput) -> OnSubjobOk -> tOracleRow (alter session set nls_sort='BINARY') -> OnSubjobOk -> tOracleConnection (for tOracleInput) -> OnSubjobOk -> tOracleRow (alter session set nls_sort='BINARY') -> tOracleInput (for input data) -> tMap -> tOracleOutput.
Seeing the Oracle database I found the sql insert command and its execution plan with the clause :
filter(NLSSORT("IG",'nls_sort=''WEST_EUROPEAN''')=NLSSORT(:1,'nls_sort=''WEST_EUROPEAN''')), although I have put 'NLS_SORT=Binary', with a 'index full scan' using the primary key with datatype varchar2(9). It causes a bad performance.
Any suggestions ?

Re: How to change nls_sort in tOracle_Output component.

Anonymous — Fri, 11 Jan 2013 22:22:16 GMT

I cannot imagine why the tOracleOutput component should create this kind of statements.
In the code of the tOracleOutput component there is no sql code like this.
Can you please post the picture of your job and the properties of the tOracleOutput component?

Re: How to change nls_sort in tOracle_Output component.

_AnonymousUser — Mon, 14 Jan 2013 12:06:25 GMT

Hi, I uploaded the picture of my job in:
https://plus.google.com/photos/115901751524062895350/albums/5833268675260405105
I think it happens when the tOracleOutput component makes connection with the Oracle database. Perhaps the component tOracleOutput may had changed the nlssort parameter of the session that has been opened.

Re: How to change nls_sort in tOracle_Output component.

_AnonymousUser — Mon, 14 Jan 2013 17:22:29 GMT

I think the problema is:
When I do the follow command in other than TOS (like sqlplus/toad/sqltools):
"select * from nls_session_parameters;"
I get this result:
PARAMETER VALUE
------------------------------ ---------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT dd/mm/yyyy
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
When I do the follow command in TOS:
"select * from nls_session_parameters"
I get other result:
NLS_LANGUAGE BRAZILIAN PORTUGUESE
NLS_TERRITORY BRAZIL
NLS_CURRENCY R$
NLS_ISO_CURRENCY BRAZIL
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE BRAZILIAN PORTUGUESE
NLS_SORT WEST_EUROPEAN
NLS_TIME_FORMAT HH24:MI SXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI SXFF
NLS_TIME_TZ_FORMAT HH24:MI SXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI SXFF TZR
NLS_DUAL_CURRENCY Cr$
NLS_COMP ANSI
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
The nls_comp and nls_sort parameter are not the same. So Oracle DB does 'full table scan'.
Can you help with : How can I change it in oracle connections in TOS ?