Problem solve Get help with specific problems with your technologies, process and projects.

Calling a stored procedure in DB2 from Oracle 8.1.7

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 v8. 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;

-------------- ------------------------------
             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:

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.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.