How to Limit number of records fetched from Oracledb using jdbc adapter

[former_member444117]

Sender JDBC adapter is configured to fetch data from Oracle DB tables and sent to different MQ queues using JMS receiver adapter based on some identifier from the tables. Now when the adapter polls it fetches multiple rows/records in a single message.

In this scenario each and every row in the table contains a identifier field which determines the destination.Dynamic routing is configured in PI Mapping and value mapping is used to fetch the corresponding MQ Queue.

During testing we found an issue, if a message with two different identifiers is processed then the dynamic routing to corresponding queues is not happening. In this case both messages are going to same queue.

If a messages has multiple rows/records with same identifier then it works. but the above scenario doesn't work.

Is it possible to make the sender adapter to fetch one row at a time from Oracle db.If yes please describe the configs.

Is there any other option available to solve this.?

[manoj_khavatkopp]

Vini,

You can use ROWNUM function in your sql statment. Just google this function you will find a lots of example on this.

Br,

Manoj

[former_member444117]

Hi Manoj,

I am using Inner join in the select query and have to use ROWNUM function only on Table1, so that for each row/record in table1 i can fetch the corresponding multiple rows/records in Table2.

If i use the select query as, Select T1.f1,T2.f2,T3.f3. <..Inner Join...> WHERE T1.f2 = 1 AND T1.ROWNUM ='1' ORDER BY T2.f4

i am getting below error because of T1.ROWNUM in the query.

"The JDBC driver returned the following error message: 'java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification '. For details, contact your database server vendor".

Plz suggest how to perform ROWNUM function on Table1.

[manoj_khavatkopp]

Have you tried this :

Select T1.f1,T2.f2,T3.f3. <..Inner Join...> WHERE T1.f2 = 1 AND ROWNUM ='1' ORDER BY T2.f4.

I don't have ORACLE DB to test this now.

Br,

manoj

[former_member444117]

I tried this, But this one makes the query to fetch only one set of records from both T1 and T2 . But my requirement is row num function should perform only on T1 so that i can fetch multiple records from T2 for each record in T1.

[prithvirajr]

Hi

Please try as below...

Select *

from (select T1.f1,T2.f2,T3.f3. <..Inner Join...> WHERE T1.f2 = 1 ORDER BY T2.f4)

where ROWNUM = 1;

Regards,

Prithviraj

[former_member444117]

Hi Prithviraj,

This query works similar to the one manoj has proposed. It fetches only one row from both tables but my requirement is for example if foreign key in T1 is F1, so for each f1 record i have to fetch corresponding many records in T2 associated with the t1.f1.

[prithvirajr]

Hi

The below query will fetch all the records from Table2 corresponding to the primary key of Table1 in where condition. (Table1 primary key is a foreign key in Table2)

select Table2.* from Table1 INNER JOIN Table2 on Table1.Col1 = Table2.Col1 where Table1.Col1 = 1;

Regards,

Prithviraj