CHAR Data Type of Oracle

, ,
1

I have writen a simple select statement using the template customSQL. The select statement is “select PO_VERSION_ID from PO where PO_NUM = ?”

The PO_NUM column is defined as CHAR(7) in the table.

I am passing PO_NUM as a string to this customSQL. But the select statement does not return me any output.

I am using IS 6.1

Any help would be much helpful.

2

Did you defined “PO_VERSION_ID” as AdapterService JDBCFieldType output? Also make sure your query returns based on PO_NUM and it exists in the DB…check your query with any db client tools also.

HTH,
RMG

3

Hi,

Thanks for the reply.

Did you defined “PO_VERSION_ID” as AdapterService JDBCFieldType output?

  • Yes Indeed I have defined…

Also make sure your query returns based on PO_NUM and it exists in the DB…check your query with any db client tools also

  • Yes I have run the same query using the client DB tools. It does indeed return me rows.

Also I did Try out the following too.

I created a dummy table with a single column PO with the data type being defined as CHAR(7) in oracle 10G.

Then I did create an adapter service using select SQL template. The where clause , I defined it as PO = ? with PO field mapped to java.lang.String.

The select returns me PO as the out put. But still no success.

Is the mapping between CHAR of oracle and java.lang.String datatype in webMethods failing?

Any help would be highly appreciated.

Regards,
byspeaks

4

“Is the mapping between CHAR of oracle and java.lang.String datatype in webMethods failing?”

No,I dont think so.

Also did you tried querying the table with out any criteria using AS? then is it outputs result set?

What is your IS/JDBC Adapter version? Any fixes were installed on the Adapter side?

HTH,
RMG

5

RMG,

Thanks for your help.

The version of IS that I am using is 6.1. These are all the SP that have been applied.

IS_6-1_FP2
IS_6-1_SP1
TNS_6-1_SP1

The JDBC version is 6.0.3 Feature Pack 1 and the JDK version is 1.4.2 from Sun.

If I try to execute the query without the where clause if works fine and the resultset is returned with values. If I change the where clause to have the selection based on any other field , then too the query returns me the resultset with values. The problem occurs only when the underlying datatype in oracle is of type CHAR.

-byspeaks

6

Hi Byspeaks,
Ensure in the customSQL service that you have defined as follows:

Input JDBC Type “CHAR”, Input Field Type “java.lang.String” and Input Field “PO_NUM”.

When you run this service supply the input PO_NUM as 7 chars.

HTH,
Bhawesh

7

byspeaks,

Thanks for your update…and good to know that your select query with out criteria AS works fine…Please follow and test with as Bhawesh mentioned above.

Let us know your result.

HTH,
RMG

8

Hello,

Thanks for your suggestions. Did an LTRIM(RTRIM(PO_NO)) and things seems to work like magic.

Thanks a lot for your help

Regards,
byspeaks.

9

Why do you need that TRIM functions?? Are you putting any spaces or data is orginally like that in DB??

Anyways glad it worked.

HTH,
RMG

10

Hello,

No I do have spaces in DB. The reason why I say is this . The same query was executed in the Oracle client and that went through fine. Kind of puzzling.

Thanks for your help.

Regards,
byspeaks.