Skip to Content
0

SQL_QUERY in Receiver JDBC Adapter - Error: ORA-00911:invalid character

Feb 03, 2017 at 09:01 PM

164

avatar image
Former Member

Hi ,

I have a synchronous scenario SOAP to JDBC where i want to retrieve value from multiple tables using the JDBC adapter.

I have created the target message structure as mentioned in the SAP documentation below is the same.

Below is the SQL query which i am passing in the 'access' tag

select w.well_location well_location,SUM(dwt.measured_oil) produced_oil,SUM(dwt.measured_water) produced_water,SUM(dwt.measured_sand) produced_sand,SUM(dwt.measured_gas) produced_gas, SUM(dwt.hours_on_production) operating_hours from fdc2_twell w,fdc2_tbattery b,fdc2_tdaily_well_tnx dwt where dwt.company_id = w.company_id and dwt.battery_id = w.battery_id and dwt.well_id = w.well_id and b.company_id = w.company_id and b.battery_id = w.battery_id and w.well_location = '$WELL_LOCATION$' and dwt.production_date between '$START_DATE and '$END_DATE$' group by w.well_location;

There are 3 variables whose value would be determined on runtime which are part of the tag key.

When i run the interface i get below error

Unable to execute statement for table or stored procedure. Volumes (Structure StatementName1) due to java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

I checked the audit log to get the SQL query at runtime and below is the same which i see

http://huskyenergy.ca/datamart/dpvr"> action="SQL_QUERY"> select w.well_location well_location, SUM(dwt.measured_oil) produced_oil, SUM(dwt.measured_water) produced_water, SUM(dwt.measured_sand) produced_sand, SUM(dwt.measured_gas) produced_gas, SUM(dwt.hours_on_production) operating_hours from fdc2_twell w,fdc2_tbattery b,fdc2_tdaily_well_tnx dwt where dwt.company_id = w.company_id and dwt.battery_id = w.battery_id and dwt.well_id = w.well_id and b.company_id = w.company_id and b.battery_id = w.battery_id and w.well_location ='$WELL_LOCATION$' and dwt.production_date between '$START_DATE$' and '$END_DATE$' group by w.well_location; 100151104604W400 21-JAN-06 28-JAN-06

The placeholder values are not getting replaced in the message and the quotes are getting converted to ' which is what is causing issues.

Can anyone please help me ?

message-type.png (12.2 kB)
10 |10000 characters needed characters left characters exceeded
Former Member

Adding the target XML which is generated

<?xml version="1.0" encoding="UTF-8"?> <ns1:MT_UDM_DPVR_EOJ xmlns:ns1="http://XXXXX/datamart/dpvr"><StatementName1><Volumes action="SQL_QUERY"><access>select w.well_location well_location, SUM(dwt.measured_oil) produced_oil, SUM(dwt.measured_water) produced_water, SUM(dwt.measured_sand) produced_sand, SUM(dwt.measured_gas) produced_gas, SUM(dwt.hours_on_production) operating_hours from fdc2_twell w,fdc2_tbattery b,fdc2_tdaily_well_tnx dwt where dwt.company_id = w.company_id and dwt.battery_id = w.battery_id and dwt.well_id = w.well_id and b.company_id = w.company_id and b.battery_id = w.battery_id and w.well_location = '$WELL_LOCATION{replace1}amp;apos; group by w.well_location; </access><key><WELL_LOCATION>100151104604W400</WELL_LOCATION><START_DATE>21-JAN-06</START_DATE><END_DATE>28-JAN-06</END_DATE></key></Volumes></StatementName1></ns1:MT_UDM_DPVR_EOJ>

0
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Evgeniy Kolmakov Feb 06, 2017 at 01:49 PM
0

Hi Husain!

'$WELL_LOCATION{replace1}amp;apos; group by w.well_location;

You have wrong syntax in your target XML message.

Please check your mapping.

Regards, Evgeniy.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Evgeniy,

This is what I am getting as target XML

<?xml version="1.0" encoding="UTF-8"?> <ns1:MT_UDM_DPVR_EOJ xmlns:ns1="http://XXXXX/datamart/dpvr"><StatementName1><Volumes action="SQL_QUERY"><access>select w.well_location well_location, SUM(dwt.measured_oil) produced_oil, SUM(dwt.measured_water) produced_water, SUM(dwt.measured_sand) produced_sand, SUM(dwt.measured_gas) produced_gas, SUM(dwt.hours_on_production) operating_hours from fdc2_twell w,fdc2_tbattery b,fdc2_tdaily_well_tnx dwt where dwt.company_id = w.company_id and dwt.battery_id = w.battery_id and dwt.well_id = w.well_id and b.company_id = w.company_id and b.battery_id = w.battery_id and w.well_location = '$WELL_LOCATION$' group by w.well_location; </access><key><WELL_LOCATION>100151104604W400</WELL_LOCATION><START_DATE>21-JAN-06</START_DATE><END_DATE>28-JAN-06</END_DATE></key></Volumes></StatementName1></ns1:MT_UDM_DPVR_EOJ>

The ' is getting converted to ' when i check in the RWB but when i display the XML in a browser it shows correctly.

So not sure if there is something missing in my message type structure ?

Do i need to provide the quotes on the placeholder variable?

Thanks

husain

0
Iñaki Vila Feb 06, 2017 at 02:57 PM
0

Hi Husain,

Check your variables are all filled. Also you can try with an easier select and later you can try to introduce more complexity.

Regards.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Inaki,

The variables are getting filled. Is there something wrong on the query as i need to use multiple tables

0
avatar image
Former Member Feb 06, 2017 at 05:55 PM
0

Below is the target XML which I see in the RWB.

The strange thing which i see is the quote which i give in my mapping for the placeholder variable '$WELL_LOCATION$' is converted to '.

So this is what i feel is causing the invalid character error. But if i remove the quotes it gives me a different erorr ORA-00933: SQL command not properly ended.

Below is the Target XML generated for the same

If anyone can help me with this.


Share
10 |10000 characters needed characters left characters exceeded
Evgeniy Kolmakov Feb 06, 2017 at 06:06 PM
0

Hi Husain!

As I've pointed you before, remove ";" sign from the end of your query.

And there is nothing wrong with your quotes. It's just escape sequence for its representation in well-formed XML document.

Regards, Evgeniy.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Evgeniy,

I removed the ';' from the end of my query but still getting the same error 00933: SQL command not properly ended.

Thanks

husain

0
avatar image
Former Member Feb 07, 2017 at 05:35 PM
0

Hi All,

Any comments or suggestions?

Thanks

Husain

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 08, 2017 at 02:15 AM
0

Hi Hussain,

Have you tested your SQL query (Access tag) directly within the DB you are trying to get the data from?

Also you are not making use of your start_date and end_date keys. Ensure you have "Key Tags Mandatory" off in your configuration, or do not map these values if you are not using them. Undefined placeholders are usually fine in the sql string but you want to make sure you arent getting any syntax errors

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Dan,

I tested the query using the Pl.SQL client it is working fine and i also got the data.

I purposefully removed the date fields for testing if it might be due to date format issue. I have mapped it again and passing the correct values.

One thing i wanted to understand is when i check the RWB log i see the placeholders do not get replaced in the query. The access tag just has the query which i defined above.

Is there some specific way the query needs to written for multiple table join?

Thanks

Husain

0
avatar image
Former Member Feb 14, 2017 at 12:05 AM
0

Hello evryone,

The SQL query worked fine now i am getting response from the database but it is not triggering my response mapping .

The scenario is synchronous REST to JDBC .

The response I am getting is as below from the JDBC

<?xml version="1.0" encoding="utf-8" ?> - <ns1:MT_UDM_DPVR_EOJ_response xmlns:ns1=" http:/XXXX/"> - <StatementName1_response> - <row> <WELL_LOCATION>100070104705W400</WELL_LOCATION> <PRODUCED_OIL>0</PRODUCED_OIL> <PRODUCED_WATER>109.88</PRODUCED_WATER> <PRODUCED_SAND>0</PRODUCED_SAND> <PRODUCED_GAS>49.26</PRODUCED_GAS> <OPERATING_HOURS>216</OPERATING_HOURS> </row> </StatementName1_response>

</ns1:MT_UDM_DPVR_EOJ_response>

I have my response mapping defined for the above message type

The message monitor shows no errors.

Can you please let me know what could i be missing for the response mapping?

Thanks

husain


mm-resp.jpg (121.6 kB)
mm-monitor.jpg (81.5 kB)
Share
10 |10000 characters needed characters left characters exceeded