I'm trying to call a stored procedure in a DB2 IBM mainfraime from Oracle 8.1.7 using the gateway. When I called I received the error:
SQL> exec sysproc.zb987a00@STORED_DB2;
BEGIN sysproc.zb987a00@STORED_DB2; END;
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
TG4DRDA v188.8.131.52.1 grc=0, drc=-30020 (839C,0000), errp=GDJRFS1E errmc=1232
ORA-06512: at line 1
The DBlink is ok, because If I do a single select, it's ok.
SQL> select * from AXG0202.PADC001V_FILIAL@STORED_DB2;
PKND_001FILIAL ATSF_001NOME -------------- ------------------------------ 1 TESTE 400 COM PARM 300 TESTE 02 123 COM PARM 124 COM PARM 853 TESTE COM PARAMETRO
6 rows selected.
I've seen problems where a stored procedure does not execute across heterogenous database links. In these cases, I've employed one of two work arounds depending on the stored procedure in the non-Oracle database.
If your stored procedure just performs a function, and is not expected to return any rows of data (similar to Oracle's stored procedures), then create a function in the non-Oracle database that just calls the stored procedure and returns TRUE. Then create a view in that database which does something similar to the following:
CREATE VIEW my_view
SELECT my_function FROM my_table;
Where MY_TABLE is a single row table.
You can then execute this stored procedure simply by querying the view. Since the stored procedure does not return any data, you really don't care about the data returned by your query.
If your stored procedure returns rows of data (similar to many of SQL Server's stored procedures), then create a view on the non-Oracle database which just calls the stored procedure.