Skip to Content
0
Feb 26, 2007 at 06:56 AM

JDBC Adapter - Sender - Stored Procedure - SQLException Error

608 Views

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.