I cannot call an Oracle stored procedure using the JDBC template. I am successful with the insert, delete, select, update, and custom templates.
The following error is produced when I run the adapter service via Developer:
Cannot execute the SQL statement “{call SCOTT…“PREQUAL_PKG.FIND_PROSPECT” (?, ?, ?, ?) }”. "
(HY000/931) [wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00931: missing identifier
Here are the details:
- OS=Win/XP/Pro
- Integration Server 6.1 (FP2)
- JAVA_DIR=c:\webMethods61\jvm\win142
- JDBC adapter 6.0.3
- Oracle 9.2 (running on localhost)
- ojdbc14.jar placed in <is>\lib\jars
- Coding specifics follow
==================declaration of cursor type
create or replace package types
as
type ref_cursor is ref cursor;
end;
==================declaration of procedure’s package
create or replace package scott.prequal_pkg
as
procedure find_prospect(
pi_btn in number,
pi_last_name in varchar2,
pi_first_name in varchar2,
pi_ssn in varchar2,
po_prospect_refcur out types.ref_cursor
);
end;
================= declaration of procedure’s body
create or replace package body scott.prequal_pkg
AS
procedure find_prospect(
pi_btn in number,
pi_last_name in varchar2,
pi_first_name in varchar2,
pi_ssn in varchar2,
po_prospect_refcur out types.ref_cursor)
is
begin
open po_prospect_refcur for
select ‘Y’, ENAME
from EMP where rownum = 1;
end find_prospect;
end prequal_pkg;
======================script to call procedure from SQLPlus
connect scott/tiger
var k refcursor
exec prequal_pkg.find_prospect(1,‘2’,‘3’,‘4’, :k);
print k
======================results when procedure is called from SQLPlus
Connected.
PL/SQL procedure successfully completed.
’ ENAME
Y SMITH
1 row selected.
=======================error message from Developer when adapter service is executed
ADA.1.316] Cannot execute the SQL statement “{call SCOTT…“PREQUAL_PKG.FIND_PROSPECT” (?, ?, ?, ?) }”. "
(HY000/931) [wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 114
ORA-06512: at line 1
Another fact, I can successfully invoke a system-defined stored procedure via WmDB but not with the JDBC adapter template.
The stored procedure works with WmDB using the SQL statement
“select sys.database_name from dual”
The same procedure fails when called with JDBC adapter
Cannot execute the SQL statement “{call SYS…DATABASE_NAME}”. "
(HY000/931) [wm-cjdbc33-0009][Oracle JDBC Driver][Oracle]ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 114
ORA-06512: at line 1