Created a storedProcedure which returns xml output as CLOB Object and able to successfully compiled. When running the adapter service getting below error msg:
[ADA.1.316] Cannot execute the SQL statement “{call WM82.EMP_PROC( ?)}”. "
(42000/932) ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at “WM82.EMP_PROC”, line 7
ORA-06512: at line 1
"
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at “WM82.EMP_PROC”, line 7
ORA-06512: at line 1
Did anyone get a chance to call storedProcedurewithSignature which is having output of type CLOB … Kindly through some light to resolve.
Please change the Java-Outputtype to java.lang.String to avoid any casting issue upon usage of the service.
Can you check the signature of the StoredProcedure to check the datatypes?
Additionally please provide your wM-Version with applied Fixes.
Maybe some Fixes are outdated and when you apply newer Fixes the issue might get resolved by this.
create or replace procedure emp_proc(OUTPUT OUT CLOB)
AS …
I successfully compiled it without any errors in Oracle DB.
I tried to change the data Type as you suggested but still getting the below error:
42000/932) ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at “WM82.EMP_PROC”, line 7
ORA-06512: at line 1
"
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at “WM82.EMP_PROC”, line 7
ORA-06512: at line 1
I am trying this in my local and details are as below:
Below is my SP Code:
create or replace procedure emp_proc(OUTPUT OUT CLOB)
AS
XMLOUTPUT XMLTYPE;
v_query CLOB;
begin
v_query:=‘select * from emp’;
EXECUTE IMMEDIATE TO_CHAR(v_query) into XMLOUTPUT;
OUTPUT:=XMLOUTPUT.getClobVal();
END emp_proc;
/
insert into emp values(101,‘xxx’,103,15000,10);
commit;
insert into emp values(102,‘yyy’,101,15000,10);
commit;
insert into emp values(103,‘zzz’,102,20000,20);
commit;
StoredProcedure code:
create or replace procedure emp_proc(OUTPUT OUT CLOB)
AS
XMLOUTPUT XMLTYPE;
v_query CLOB;
begin
v_query:=‘select * from emp’;
EXECUTE IMMEDIATE TO_CHAR(v_query) into XMLOUTPUT;
OUTPUT:=XMLOUTPUT.getClobVal();
END emp_proc;
/
create a service which used StoredProcedureWithSignature and call the above StorProc and see how it works.
I have a similar problem. The output which i’m getting from DB is of collection type. When I have used StoredProcedureWithSignature, collection variable is reflecting as STRUCT type variable. But when I execute the adapter service getting the below error:
Could not run ‘sample’ com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service provider.adapter:sample.
[ADA.1.316] Cannot execute the SQL statement “call GETASSETDETAIL( ?, ?, ?, ?, ?, ?, ?)”. "
(65000/6550) ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘GETASSETDETAIL’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Attached is input output structure from webMethods.
DB SP signature defined as:
PROCEDURE getAssetDetail (
io_KA_TYPE_CD IN OUT VARCHAR2,
io_KA_ASST_VAL IN OUT VARCHAR2,
io_SOURCE IN OUT VARCHAR2,
o_STATUS OUT VARCHAR2,
o_KA_DESC OUT VARCHAR2,
o_KA_ATTR_COLLECTION OUT ASSET_ATTR_COLLECTION,
o_RELATION_COLLECTION OUT RELATIONSHIP_COLLECTION);
Where ASSET_ATTR_COLLECTION defined as:
create or replace TYPE ASSET_ATTR_COLLECTION AS TABLE OF ASSET_ATTR;
And ASSET_ATTR is defined as:
create or replace TYPE ASSET_ATTR AS OBJECT (ATTR_NAME VARCHAR2(50), ATTR_VAL_TXT VARCHAR (4000))
Request you to guide us what are the steps we need to do while creating the adapters to handle such output types from DB.
