Oracle JDBC receiver error

[jacob_ellsworth]

Hi,

I am trying to setup a file to Oracle JDBC receiver scenario. In this case I am trying to just insert a single line to Oracle. I get no error in SXMB_MONI, no error in the message mapping test, and no error in the Test configuration in the ID.

Here is the error I get in the RWB comm channel monitoring for my adapter:

<i>No "action" attribute found in XML document ("action" attribute missing or wrong XML structure)

MP: exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: No 'action' attribute found in XML document (attribute "action" missing or wrong XML structure)

Exception caught by adapter framework: Error processing request in sax parser: No 'action' attribute found in XML document (attribute "action" missing or wrong XML structure)

Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: No 'action' attribute found in XML document (attribute "action" missing or wrong XML structure).</i>

Here is my structure for the oracle receiver:

DTOracleReceiver_INSERT
    TEMP_DATA_XI                                             1
         action   (attribute)          xsd:string
         access
               NCODE                  xsd:string              1
               VNAME                  xsd:integer            1
               DDATE                   xsd:date                1
               NPRICE                  xsd:decimal           1
               LSTATES               xsd:string               1

TEMP_DATA_XI is my table name in Oracle

action is set to constant "INSERT" in the message mapping.

When I look at the payload in the OracleReceiver adapter, this is what shows up:

<?xml version="1.0" encoding="UTF-8" ?> 
- <ns0:MTOracleReceiver_INSERT xmlns:ns0="http://test.com/jdbc">
- <TEMP_DATA_XI action="INSERT">
    - <access>
        <NCODE>200</NCODE> 
        <VNAME>Inserttest</VNAME> 
        <DDATE>2007-06-15 09:25:43.0</DDATE> 
        <NPRICE>20.00</NPRICE> 
        <LSTATES>test of JDBC receiver</LSTATES> 
     </access>
  </TEMP_DATA_XI>
  </ns0:MTOracleReceiver_INSERT>

Any thoughts or ideas on how to fix this error? Any help would be greatly appreciated.

Thanks,

Jake

[jacob_ellsworth · Accepted solution]

I now get a different error:

Unable to execute statement for table or stored procedure. 'TEMP_DATA_XI' (Structure 'STATEMENTNAME') due to java.sql.SQLException: ORA-00928: missing SELECT keyword

2007-06-19 10:03:54 Error MP: exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TEMP_DATA_XI' (structure 'STATEMENTNAME'): java.sql.SQLException: ORA-00928: missing SELECT keyword

2007-06-19 10:03:54 Error Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TEMP_DATA_XI' (structure 'STATEMENTNAME'): java.sql.SQLException: ORA-00928: missing SELECT keyword

2007-06-19 10:03:54 Error Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TEMP_DATA_XI' (structure 'STATEMENTNAME'): java.sql.SQLException: ORA-00928: missing SELECT keyword.

My current datatype for the receiver is:

DTOracleReceiver_INSERT                                                    1
     STATEMENTNAME                                                          1
             TEMP_DATA_XI                                                       1
                        action   (attribute)          xsd:string                  required
                        Table                            xsd:string                  1 
                        access                                                         0:unbounded
                               NCODE                  xsd:string              1
                               VNAME                  xsd:integer            1
                               DDATE                   xsd:date                1
                               NPRICE                  xsd:decimal           1
                               LSTATES               xsd:string               1

action is mapped to "INSERT"

Table is mapped to "TEMP_DATA_XI"

[justin_santhanam · Accepted solution]

Jacob,

Please try the below.

Map Constant[] to StatementName

Map Constant[] to TEMP_DATA_XI

Map Constant[INSERT] to action

Map tablename to Table [ Please see whether Table is sub-node of TEMP_DATA_XI]

Access [Make sure its sub-node of Table].

Sorry, Please correct it Access <b>[Make sure its same level as of Table].</b>

Thanks Prashanthi for pointing out the same.

Best regards,

raj.

Message was edited by:

Raj

[prasanthi_chavala · Accepted solution]

Hi Jacob,

Map constants[] to STATEMENTNAME ,TEMP_DATA_XI and access.Also map root node of the file to the DTOracleReceiver_INSERT . Access element should be at the level of table only.

Also go through this blog:

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

Hope it will resolve ur problem.

Regards,

Prasanthi.

[jacob_ellsworth]

This design you gave me above ended up working. I was trying to pass a DATE field from a file to Oracle, and the formats were different. I put in a hardcoded to_date ('20070620','MM/DD/YYYY') for the Oracle date, and then it worked.

Thanks for all your help

[jacob_ellsworth]

I seem to now be getting to the Oracle database, but am getting an error in regards to the DATE field I am trying to pass. I am getting an error "ORA-01861: literal does not match format string" which seems to be a conversion issue between XI and Oracle, so I will continue looking at this and update later.

thanks

[justin_santhanam]

Jacob,

Just take the source field and do the date formatting with std function and map to the target.

Best regards,

raj.

[jacob_ellsworth]

I currently have constant [] = STATEMENTNAME
                       constant [] = TEMP_DATA_XI
                       constant [] = access
                       constant [INSERT] = action
                       constant [TEMP_DATA_XI] = TABLE
         access is on the same level as TABLE.
I also have my root Message type = the root message type MTOracleReceiver_INSERT as was suggested.

I also reviewed the blog that you attached the link for and that is one I had been following along with.

I unfortunately am still get the same ..."missing SELECT keyword"... error.

[jacob_ellsworth]

Should I be using StatementName as the name of my Datatype?

I do have action currently under StatementName and will change this back to be under the tablename.

[justin_santhanam]

Jacob,

DTOracleReceiver_INSERT

STATEMENTNAME 1

TEMP_DATA_XI xsd:string 1

action (attribute) xsd:string

Table xsd:string 1

access 0:unbounded

NCODE xsd:string 1

VNAME xsd:integer 1

DDATE xsd:date 1

NPRICE xsd:decimal 1

LSTATES xsd:string 1

Best regards,

raj.

[Former Member]

>>Should I be using StatementName as the name of my Datatype?

No. All you need is a Statement <b>level</b> tag. It can have any name. It can be StatementName or SNAME. It doesn't matter.

>>I do have action currently under StatementName and will change this back to be under the tablename.

The attribute action should be maintained for the Statement level tag. Have an element table and fill this with the real table name in mapping instead of using the table name as tag name and give it a try.

Regards,

Jai Shankar

[jacob_ellsworth]

Sorry, I am using XI 3.0 on SP 19

[justin_santhanam]

Jacob,

Can u check the data type, u are using action as attribute for statement name ,is it correct?

Please check with the below format.

<StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

</dbTableName>

</StatementName2>

Best regards,

raj.

[Former Member]

Are you using the PI 7.0 or XI 3.0, In PI 7.0 the JDBC canonical structure is changed... Check in the help here:

http://help.sap.com/saphelp_nw70/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

[jacob_ellsworth]

Thanks for the link, I double checked and all my ID mappings seem to be in place. The interface determination was filled in with my expected maps. I don't use the wizard, so I didn't have the problem with them not being filled in. I also tried changing my datatype to be this:

DTOracleReceiver_INSERT
    STATEMENTNAME                                          1
         action   (attribute)          xsd:string
         TEMP_DATA_XI            xsd:string              1
         access                                                     0:unbounded
               NCODE                  xsd:string              1
               VNAME                  xsd:integer            1
               DDATE                   xsd:date                1
               NPRICE                  xsd:decimal           1
               LSTATES               xsd:string               1

this still gives me the same action missing error.

[justin_santhanam]

Jacob,

Please see the below thread.

Best regards,

raj.