Trouble calling oracle stored procedure IS 61

, ,
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 <is>\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

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

2

Hrmm… bit hard to know where to start, but I’ll give it a shot:

  • have you supplied inputs for each of the values, so for any “in” parameters you have a pipeline input, and one for the output (by the looks of your information that means 3 inputs). It’s a pain because you seem to have to define things many times, but I’ve found sproc adapter template is notorious for letting you forget to add not only the parameter info, but then the pipeline mapping to get an actual value in there.
  • check the names of your inputs to the stored proc, might be a typo
  • check that you haven’t renamed the adapter service, that usually causes dramas because the input/output signatures get all messed up.
  • you’re using the StoredProcedure adapter service template I presume?

I’ve found sometimes it’s easier to start from scratch just to give yourself a second chance at not messing up the input variable names. Sprocs I always find are a pain cos they’re so slow to setup in wm :wink:

See if any of that helps,
regards,
Nathan Lee

3

There appears to be a bug in the JDBC Adapter Template for stored procedures (under IS). It generates an incorrect reference to a stored procedure.

If I set “enable procedure name lookup=true”, then Developer incorrectly sets the procedure reference as follows (note the TWO periods where there should be one), e.g.
SYS…DATABASE_NAME

However, if I set “enable procedure name lookup=false”, then I am able to type-in the correct name manually, e.g.
SYS.DATABASE_NAME

4

Hmm… well spotted.
Does it occur because of something strange with the “drilling down” to the stored proc? e.g. I always use “current catalogue, current schema” rather than wiring in to a particular one. Perhaps the bug pops up with a particular combination of those parts?

regards,
Nathan