Trouble calling Oracle stored procedure with JDBC template (

, ,
1

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:

1. OS=Win/XP/Pro
2. Integration Server 6.1 (FP2)
3. JAVA_DIR=c:\webMethods61\jvm\win142
4. JDBC adapter 6.0.3
5. Oracle 9.2 (running on localhost)
6. ojdbc14.jar placed in \lib\jars
7. 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
2

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

3

I found the problem.
If I get lazy and set “enable procedure name lookup=true”, then Developer sets the procedure reference as follows (note the two periods where there should be one)
SYS…DATABASE_NAME

If I set “enable procedure name lookup=false”, then I am able to type-in the correct name
SYS.DATABASE_NAME