JDBC Adapter - Sender - Stored Procedure - SQLException Error

[jegathees_waran]

Hi,

I have created one stored procedure in our Oracle database. I give below that stored procedure.

-

---

CREATE OR REPLACE PROCEDURE sp_stud

IS

l_row student%ROWTYPE;

TYPE t_ref_cursor IS REF CURSOR RETURN l_row%TYPE;

c_cursor t_ref_cursor;

BEGIN

OPEN c_cursor FOR

SELECT *

FROM student where readflag= ' ';

LOOP

FETCH c_cursor into l_row;

EXIT WHEN c_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(l_row.id || ' : ' || l_row.name);

END LOOP;

close c_cursor;

END;

/

-

---

This is a procedure to retreive rows from the table student which is having the field read_flag = ' '; (Student table contains the fields ID, NAME, BIRTHYEAR, BIRTHMONTH, READFLAG).

In JDBC Sender Adapter, I set the values for the following fields under Parameters tab.

Query SQL Statement: EXECUTE sp_stud

Update SQL Statement: UPDATE student SET readflag = 'Y' where readflag = ' '

The scenario is every 5 minutes JDBC adapter checks the table whether any new row is inserted, if it is inserted and commit, it will send that record to File. This is the Scenario.

In Runtime Workbench, Communication Channel Monitoring, it shows the following error, when JDBC Polls the table.

-

---

Database-level error reported by JDBC driver while executing statement 'EXECUTE sp_stud'. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-00900: invalid SQL statement '. For details, contact your database server vendor.

-

---

Note : If we execute the Stored Procedure ad SQL command level, it works fine.

Kindly help friends to solve this, where the error is happened.

Thanking you,

Kind regards,

Jegatheeswaran P.

[Former Member · Accepted solution]

Hi,

I hope ..

In you stored procedure.. you trying to generate output .. it will work in SQL command level .. because it will try to generate the output.. by this line..

DBMS_OUTPUT.PUT_LINE(l_row.id || ' : ' || l_row.name);

eliminate this line ..try..donot generate any output in your stored procedure..

OR try to use one update query with the updating the readflag=' ';

Regards

Chilla..

[jegathees_waran · Accepted solution]

Hi ChandraSekar,

Eventhough I removed the DBMS output line, again the same problem comes ... Could you kindly help me ...

Thanks in adavnce

Regards,

Jeg

[Former Member · Accepted solution]

Hi Jeg,

Like pointed out already, XI can process only resultset and not cursors. Are you sure, your SP is returning resultset?

Regards,

Jai Shankar

[jegathees_waran · Accepted solution]

Hi

Could you kindly give me some idea to create stored procedure with resultset instead of cursor in orace for the simple select command ... ? (eq to cursor thing). If you give me this idea, my problem will solve.

Thanking you.

Regards,

Jeg

[Former Member · Accepted solution]

Hi ,

See If it helps you...

http://asktom.oracle.com/tkyte/ResultSets/

Regards,

[Former Member · Accepted solution]

Hi,

Just try without Stored procedure ,first if it working fine , then use stored procedure..

Without stored procedure use your query whatever you mentioned above.

and check the connection string and other perameters are correct.

Regards

Chilla..

[jegathees_waran · Accepted solution]

Hi

Without stored procedure, it works fine with SQL Query. But, we want to try with Stored Procedure.

Thanking you.

Regards

Jeg.

[bhavesh_kantilal · Accepted solution]

Hi,

Like pinted by others, Sender JDBC adapter does not support Stored Procedures as they return Cursors and not resultsets.

You cannot use Stored Procedures in Sender JDBC adapter.

Regards

Bhavesh

[Former Member · Accepted solution]

Hi,

From Sap help for sender JDBC adapter...

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

Query SQL Statement

You have the following options:

● Specify a valid SQL SELECT statement to select the data to be sent from the specified database.

● Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.

Thanks.

[bhavesh_kantilal · Accepted solution]

Deepu,

SAP help might state that, but Stored Procedures do not work with Sender JDBC adapters for Oracle Database.

Reason like i told is the fact that Oracle Stored Procedures return Cursors while XI expects a Resultset.

Stored Procedure works for other databases on the Sender JDBC adapter but not for Oracle.

Regards

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh..

I have not worked any such scenarios, and was just referring to documentation....sorry for the mistake made...

Anyways i was searching OSS and came acorss the note 941317...check section 6.

May be it comes handy....

Thanks.

Message was edited by:

Deepu Venugopal

[bhavesh_kantilal · Accepted solution]

Deepu,

This is an open forum and all of us make our mistakes ( I have made mine as well )

Was not aware of that OSS note. Very interesting info. Thanks for pointing the same.

Regards

Bhavesh

[Former Member]

Hi Jegatheeswaran,

i am facing the same problem now. i have to execute a stored proc from xi and it should return a resultset.

you mentioned in this blog( threadid=330212) that Through Oracle Table Function concept u acheived

can u send me the code to write that function and wat query we have to write in xi comm channel. it will be a great help for me

and another thing is we r using oracle 9i and xi- version 3.0

thanks

jhansi

[Former Member]

Hi Jhansi,

Have a look into the SAP Note pointed by Deepu. It clearly says, you can execute SPs in ORacle from Sender JDBC adapter only if your Oracle version is >10.2. Since your Oracle version is 9.1, you <b>can not</b> use SP here.

Regards,

Jai Shankar

[jegathees_waran]

Hi Jai Shankar,

Through Oracle Table Function concept I acheived this. SAP Note 941317 helps to give tips to me.

Regards,

Jegatheeswaran P

[jegathees_waran]

All friends help me a lot. Especially, Prabhu S & Bhavesh Kantilal has given the hint JDBC sender adapter does not support Stored Procedure. Then another hint is given by Deepu to go thru the SAP Note 941317. Those are many helpful. Now my problem is solved. I thank you very much to all.

Kind Regards,

Jegatheeswaran P.

[Former Member]

Hi,

Can you let us know wht approach you used to solve this issue? It will help our fellow SDNers too.

Regards,

Jai Shanakr

[jegathees_waran]

Hi friends,

As Bhavesh told I created resultset in our Oracle. The code segment is given below.

-

---

SQL>CREATE OR REPLACE PACKAGE types AS

TYPE Cursortype IS REF CURSOR;

END;

SQL>create or replace procedure sp_stud

as

p_cursor types.Cursortype;

begin

open p_cursor for select * from student where readflag = ' ';

end;

-

---

Those are created successfully. It contains no errors, compiled & executed successfully at SQL command level.

But, given this SP Name in Sender Side JDBC Adapter - Parameters like below

Query SQL Statement : EXECUTE sp_stud

Update SQL Statement: <test>

After give this parameters, and activate the CC, it shows again the same error.

comes

-

---

Database-level error reported by JDBC driver while executing statement 'EXECUTE sp_stud'. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-00900: invalid SQL statement '. For details, contact your database server vendor.

-

---

Friends, still I am not able to solve this problem. Could you kindly try this ? and help me to solve this.

Expecting your reply.

Regards,

Jeg

[bhavesh_kantilal]

Hi,

Not a Database expert. But , in case you have not seen the note pointed by Deepu,

<i> The JDK 1.1.x, 1.2 and 1.3 versions (classes111. zip, classes12.zip, classes12.jar) of the driver are not compatible with the SAP XI JDBC Adapter. Use the JDK 1.4 driver (ojdbc14.jar) instead. For details, refer to Oracle MetaLink note # 203849.1.

<b>Invoking Oracle stored procedures from within a JDBC sender channel is only possible for Oracle DBMS versions >= 10.2.x using so-called table functions:</b>

Example:

-

---

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;

/

-

---

pkg1 -

---

<b> This function has to be invoked from the sender channel configuration (SELECT statement) as follows:

SELECT * FROM TABLE(pkg1.f1(5));</i></b>

Check this, confirm your Oracle DB version and then use the Table Functions as described in this note.

A Database Expert wil be able to crack this for sure.

Regards

Bhavesh

[Former Member]

Hi,

<i> >>SQL>CREATE OR REPLACE PACKAGE types AS

TYPE Cursortype IS REF CURSOR;

END;

SQL>create or replace procedure sp_stud

as

p_cursor types.Cursortype;

begin

open p_cursor for select * from student where readflag = ' ';

end;</i>

Is this the SP you are using now? This SP returns a cursor. Rite?

I am not an ORACLE specialist, just talked to a friend of mine working in ORACLE and he suggested the use of VARRAYS as OUT parameter in SP. He thinks this approach will return a result set.

Am not sure of this approach. But worth a try..

Regards,

Jai Shankar

[prabhu_s2]

Jega

Below are some examples cited, not for sender CC:

<a href="/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi stored procs in MaxDb using SAP Xi - Recv CC</a>

<a href="/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures Stored Procedures : Synchronous request to a DB</a>

You can invoke Stored Procedures on SQL server using XI but only limitation is that you can have only one SELECT statement in the Stored Procedure.

Ref: [url=http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm]http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm[/url]

[prabhu_s2]

also note that the sender JDBC adapter does not support Oracle's store procedure/function

[prabhu_s2]

only resultset will be validated when using SP in sender CC and not cursor

[jegathees_waran]

Hi Prabhu,

Could you kindly help me how to modify the query using resultset instead of cursor ?

Thanks in adavance

Regards,

Jegatheeswaran P.

[prabhu_s2]

issue with using cursors...