JDBC Issues....

[Former Member]

We have an issue where we are trying to call stored procedure <b>f_delete_sca</b> which is contained within the package <b>p_dist_wm</b>. This package is belongs to the staging owner <b>mp_sd_staging_owner</b>.

We are trying to connect to the Database using th JDBC adapter and keep getting errors. We have tried with different variations to call this stored procedure but nothing work. The following have been tried input parameters for the table element have been tried:

<b>f_delete_sca

p_dist_wm.f_delete_sca

mp_sd_staging_owner.p_dist_wm.f_delete_sca</b>

we have also tried these variations using 2 different users to connect to the database – Interface User and also the staging owner user. Both of these are producing errors. I have to maintain that it is not yet a problem with the structure of the payload more the structure of the call maybe? As if it was an issue with the format I think we may get a slightly different error. We have also compared this to another stored procedure call that is apparently working and as far as we can tell the format of the message is also correct comparing to the already functional call structure so we are running out of answers.

We have since tried to connect to the database with a simple select query and this works....

I have enclosed below the difference error messages we are receiving with the relevant payload that caused the error. As mentioned above we tried 3 different variations with 2 users so there are a total of 6 errors below with relevant payloads.

Please if anyone has any idea as to why this could be please let me know ASAP.

<b><i><u>Error Messages when connecting as the Interface User</u></i></b>

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'f_delete_sca' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'F_DELETE_SCA' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <storedProcedureName action="execute">
  <table>f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </storedProcedureName>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'p_dist_wm.f_delete_sca' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'F_DELETE_SCA' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <storedProcedureName action="execute">
  <table>p_dist_wm.f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </storedProcedureName>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'mp_sd_staging_owner.p_dist_wm.f_delete_sca' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'F_DELETE_SCA' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <storedProcedureName action="execute">
  <table>mp_sd_staging_owner.p_dist_wm.f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </storedProcedureName>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

<b><i><u>Error Messages when connecting as the Staging Owner User</u></i></b>

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'f_delete_sca' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00905: object MP_SD_STAGING_OWNER.F_DELETE_SCA is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <storedProcedureName action="execute">
  <table>f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </storedProcedureName>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'p_dist_wm.f_delete_sca' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'F_DELETE_SCA' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <storedProcedureName action="execute">
  <table> p_dist_wm.f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </storedProcedureName>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'mp_sd_staging_owner.p_dist_wm.f_delete_sca' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'F_DELETE_SCA' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

<?xml version="1.0" encoding="UTF-8" ?> 
<ns0:MT_F_ZMETADATA_DELETE xmlns:ns0="http://qimonda.com/CMD/MetaData">
 <Statement>
  <storedProcedureName action="execute">
  <table>mp_sd_staging_owner.p_dist_wm.f_delete_sca</table> 
  <p_return type="Integer" isOutput="true" /> 
  <p_retmsg type="Varchar" isOutput="true" /> 
  </storedProcedureName>
 </Statement>
</ns0:MT_F_ZMETADATA_DELETE>

.

[Former Member]

Ok problem solved...