JDBC Sender fetching huge number of records from Oracle DB

[Former Member]

Hello Experts,

I am presently having a scenario where-in I am expected to fetch huge number of records from JDBC adapter of SAP PO 7.4 single stack. Since JDBC doesn't by default provide message splitting option as File adapter, I am planning to use Oracle Stored procedure. I referred the following 2 blogs for the same.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/03/06/executing-stored-procedure-from-s...

http://scn.sap.com/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-ada...

I am planning to use concept of rownums to limit the number of records fetched per poll. However the problem is Oracle doesn't allow DML statement in PIPELINED functions as defined in the first blog. In absence of that, not getting how to prevent repeated polling of the same records. What I tried was passing the rownum as parameter to procedure, procedure returning those many number of rows and updating the same rows with different status so that in the next poll, the same records will not get fetched. Looks simple requirement, but Oracle PL/SQL making complex .

Anybody faced such issue resolved by using Stored procs? Thanks in advance.

Regards

[Former Member · Accepted solution]

Hello All,

This problem has been resolved by having 2 procedure, one for fetching the records and another to UPDATE.

The key thing was that to fetch the Oracle internal rowid of the fetched records and pass the same to another procedure for UPDATE. The another procedure is called as PRAGMA AUTONOMOUS procedure where it allows to have DML.

So in the query section i just make a call to the procedure e.g. SELECT * from PKG.PROC_NAME(<RECORD_STATUS>, <PACKET_SIZE>);

Rest is taken care by the procedure.

Regards

[AnilDandi]

Hi Ameet,

Haven't seen anyone use it, but if you can test it out.. SELECT statement has a LIMIT clause to limit the result set. See the Oracle documentation.

13.2.8 SELECT Syntax

[iaki_vila]

Hi Anil,

I think you can't use the LIMIT clause in the UPDATE and to assure to have the same records in the two SQL statements in JDBC sender.

Regards.

[naveen_chichili]

Hi Ameet,

As Inaki said sequentinal number filling would resolve your issue.

Thanks,

Naveen

[iaki_vila]

Hi Amet,

This problem i solved having a field that must be filled by a sequential number in ORACLE.

My stored procedure looks like:

CREATE OR REPLACE FUNCTION mySchema.myStoredprocecure (vi_nrows IN number) RETURN number IS
  --
  CURSOR selection IS SELECT(nrows NUMBER) sequential_number
                        FROM (SELECT sequential_number
                                FROM myTable
                                WHERE status= 'N'
                                ORDER BY sequential_number)
                        WHERE rownum <= vi_nrows;
  --
BEGIN
  --
  FOR data IN selection(vi_nrows) LOOP
     --
     UPDATE myTable
       SET status= 'X' 
       WHERE sequential_number  = data. sequential_number;
     --
  END LOOP;
  --
  COMMIT;
  --
  return (0);
  --
EXCEPTION
  --
  WHEN others THEN
     --
     return (1);
     --
END;
/

In the Query Sql Statement i used the same Select that in the SP.

In the Update Sql Stameent i set:

DECLARE RESULT NUMBER; BEGIN RESULT := mySchema.myStoredprocedure(1); END

If you will have rownums greater than 1, you will need to do your table Serializable to avoid this case:

rownums = 2

Adapter reads 1 registers (no more available although you have 2 in the rwonums)

New Insert in the table

Adapter update 2 registers

Hope this helps.

Regards.

[Former Member]

Thanks Iñaki for your reply.

So if I understood this correctly, i should add one additional field say sequential_number to my existing table and make use of the same in SP.

Actually not sure whether you returned Object_types from your procedures since I want to return the resultset and for that Oracle have PIPELINED functions that doesn't allow to have DML inside such functions.

Also rownums should be somewhere in 100s or 1000s since I want to retreive 100s of thousands records as monthly batch activity.

Regards

[iaki_vila]

HI Ameet,

First of all i have typo error vi_nrows instead of nrows.

So if I understood this correctly, i should add one additional field say sequential_number to my existing table and make use of the same in SP.

Yes, you understood correctly. With this field i assure to retrieve always the same records.

Actually not sure whether you returned Object_types from your procedures since I want to return the resultset and for that Oracle have PIPELINED functions that doesn't allow to have DML inside such functions.

May be i don't understand you, but you don't need to retrieve any resulset because in the first SQL statement data you have yet the resultet.

Also rownums should be somewhere in 100s or 1000s since I want to retreive 100s of thousands records as monthly batch activity.

No problem, take into account that my SP has the rownums has parameter. The key is to set you Oracle Database Serializable and to set in the jdbc sender your access serializable as well.

In my case a i poll one register each 5 seconds during office time. The registers are between 50kb to 100 Mb and between 50 to 150 per day and it works perfectly, with some tuning adjusts.

Regards,