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.

Regarding,
Holger

Yes you should try change the output field type to java.lang.String and test it.

Follow the rest of the points/checks as Holger suggests if the above change doesn’t works.

HTH,
RMG

Thanks Holger & RMG for your help.

Signature of StoredProc is :

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:

version: 8.0.1.0/
Updates:IS_8-0_SP1

Please suggest to resolve this.

Thanks for your help.

You are not on the latest fix levels.

Question for you - What does your SP do? Can you share more details please?

OK at least that change is ruled out…

What is your JDBC Adapter fix levels?

Please provide your SP details as requested above:

HTH,
RMG

Hi,

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;
/

Even though the CLOB type why don’t output field type of java.lang.string doesn’t work for you as it supposed to work?

HTH,
R<G

RMG, as you specified in earlier threads, I changed output field Type to java.lang.String but still I am getting the earlier which I said earlier.

OK I saw it…but let us see what other user’s point the root cause:

Run this SP on oracle and check if it executing there without any errors. I see the same error running on sql developer.

Can you provide a screenshto from your /WmRoot/updates.dsp-Page from IS-Admin?

This would help toidentify missing fixes/service packs.

I have checked my database schemata for wM 9.5 as well as wM 7.1.
None of them contains a table or view named emp.

Can you explain the requirement to have such stored procedure/query available?

Which Oracle version are you using?
Which JDBC Driver are you using?
ojdbc5.jar or ojdbc6.jar I assume.
Which version of the driver are you using?

Regards,
Holger

Mahesh : Thanks, I didn’t get any issues, i was successfully compiled it in Oracle 10G. Are you using Oracle or SQL Server?

@ Holger: Thanks for your help.

I am using Oracle 10G Exp Edition and I am using ojdbc14.jar

I created a table emp my own, inserted data in it.

Kindly let me know any thoughts to resolve this.

ojdbc14.jar sounds fairly outdated.

please share the content of the manifest.mf. It contains the version of the driver.

You should download a newer version of the driver from OTN.
You can use the latest driver 11.2.x.x.

The number in the filename identifies the JavaVersion for which the driver was build.

There was an issue with LOBs in older version of the ojdbc-driver.

Regards,
Holger

Thanks Holger for your information.

in my wM8.0, MANIFEST.MF contains below content:

Manifest-Version: 1.0
Build-Timestamp: 150120111839
Implementation-Vendor: webMethods
Implementation-Title: webMethods Portal Web/Portal Application (SalesD epartment)
Specification-Vendor: webMethods
Class-Path:
Specification-Title: My webMethods Server
Specification-Version: 7.0

in my wM9.6, MANIFEST.MF contains below content:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.8.2
Created-By: 1.6.0-b105 (Sun Microsystems Inc.)

Name: epl-v10.html
SHA1-Digest: hNPRR3joO5UV8+u/L0qjKV/Y2EE=

Name: META-INF/eclipse.inf
SHA1-Digest: KyT9FF7C7t86NoBoa2kZT3ZJBfw=

Name: eclipse_update_120.jpg
SHA1-Digest: xstAqMgs/a5AsQXQZSdDQ79veOA=

Name: license.html
SHA1-Digest: /vLZjlHkZSXMSfPrWwNqOUDqqbM=

Name: feature.properties
SHA1-Digest: KPTSYN25IY1TULoBN5wn1XajupU=

Name: feature.xml
SHA1-Digest: ue37M59+/c7zDvPJy3Sm3+HKDcA=

I will download latest driver 11.2.x.x. as you specified and then will update you.

Thanks,

Holger,

I removed ojdbc14.jar, copied ojdbc6.jar but still getting the same error. Can you please execute the below code and observe the results.

create table emp(empId number(5),ename varchar2(20),mgr number(5),sal number(10,2),dept number(5));

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.

Thanks

Hi RP,

from which jar-file is this manifest taken from?

I meant the manifest from the ojdbc.jar.

Did you reload WmJDBCAdapter-Package or restart the IS after exchanging the jar?

A new question:
Why are you defining the v_query as a CLOB, when it is only a string?

So the issue might be located at the transform of the v_query from CLOB to CHAR, which might not be possible.

I will test the provided code on my local Oracle 10g XE, but this will take some time as we have some urgent issues on our application.

Regards,
Holger

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.

input_output.png864×540 18.3 KB

Can you please do the conversion in Database to get the Collection variable to Nvarchar and see how it works.