Sender JDBC Adapter Supports Stored Procedures????

[Former Member]

Dear All,

I was trying to use the Sender JDBC adapter to call the Stored procedure in the Sender JDBC adapter is possible to do this???

I don't think Sender JDBC Adapter is supported to execute the Oracle Started Procedures. With my understanding only the Receiver JDBC Adapter will supported for the Stored Procedures using the message mapping Action = EXECUTE and Table = DBO.EmployeeMasterTable.

Please let me know how to achieve the below scenario.

My Scenario JDBC to RFC

Query SQL Statement:

select * from dbo.emp where flag = 'Insert' or flag = 'Update' or flag = 'Delete'

Update SQL Statement:

UPDATE dbo.emp SET flag = null WHERE flag IN ('Insert', 'Update');

DELETE dbo.emp WHERE flag = 'Delete';

In the my update statement I need to update few data flag with Insert , Update and need to delete the of the old records as mentioned above the query.

In the response mapping I am using the Action = INSERT and Table = emp.

Thanks,

Jane F.

[former_member187339 · Accepted solution]

Hi Jane,

>I don't think Sender JDBC Adapter is supported to execute the Oracle Started Procedures. With my understanding only the Receiver JDBC Adapter will supported for the Stored Procedures using the message mapping Action = EXECUTE and Table = DBO.EmployeeMasterTable.

Yes SP is now supported by Sender JDBC Adapter. Check this note:Note 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities

Coming to your problem, if you are using SP then why to separate Select and update/delete task... Do everything in SP and send back the result to PI

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

My requirment is to select from the DB and update and delete needs to be done. How I need to call this stored procedure in Sender JDBC adapter.

Is this need to be in the one stored procedure right. Can ou let me know how it should be handled in JDBC Adapter.

Thanks,

Jane F

[former_member187339 · Accepted solution]

Hi Jane,

>>My requirment is to select from the DB and update and delete needs to be done. How I need to call this stored procedure in Sender JDBC adapter.

An example of SP call is mentioned in the previous note Note 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities

SP Code

          ---------- pkg1 ----------
CREATE PACKAGE pkg1 AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/
CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END;
END pkg1;
/

In sender channel mention

>> SELECT * FROM TABLE(pkg1.f1(5));

>>Is this need to be in the one stored procedure right. Can ou let me know how it should be handled in JDBC Adapter.

Yes it shoudl be a single SP. Mention a select statement as given in above example.

What is your Oracle version?

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

Version Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit.

Query SQL Statement: I can write this SELECT * FROM TABLE(pkg1.f1(5));

Update SQL Statement: Is also a mandatory field what code needs to be used here?

Thanks,

Jane F.

[former_member187339 · Accepted solution]

Hi Jane,

In Update write <TEST>

Regards

Suraj

[Former Member · Accepted solution]

Hi Suraj,

It was very helpful answer and I am using in my case created a package and procedure. Calling the Package in the procedure

Example: call packageName.procedureName()

Thanks,

Jane F.