Create federated store procedure for a SP with no arguments in
Oracle database may fail with -901:
2017-09-20-13.16.07.188754+480 E66394A1654 LEVEL:
Info (Origin)
PID : 16711756 TID : 3600 PROC :
db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB :
SAMPLE
APPHDL : 0-8 APPID:
*LOCAL.db2inst1.170920051147
AUTHID : DB2INST1 HOSTNAME: myhost
EDUID : 3600 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl,
probe:650
MESSAGE : ZRC=0x80260160=-2144992928=SQLQG_ERROR "Error
constant for gateway."
DATA #1 : String, 62 bytes
An unexpected error was detected during statement
compilation.
DATA #2 : Boolean, 1 bytes
false
DATA #3 : Boolean, 1 bytes
false
DATA #4 : Boolean, 1 bytes
true
DATA #5 : Boolean, 1 bytes
false
DATA #6 : Hex integer, 4 bytes
0x00000000
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901
sqlerrml: 0
sqlerrmc:
sqlerrp : SQLNN067
sqlerrd : (1) 0x80260160 (2) 0x00000160 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFDA8 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5)
(6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #8 : Hex integer, 4 bytes
0x00000040
DATA #9 : String with size, 165 bytes
CREATE OR REPLACE PROCEDURE ...
DATA #10: String, 171 bytes
Compiler error stack for rc = -2144992928:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[510]
sqlnp_smactn[100]
sqlnq_create_fedproc_stmt[40]
sqlnq_create_fedproc_one[130]
The root cause is that Federation Server uses a SQL against
sys.all_arguments incorrectly to verify if a particular
procedure exists - after bugfix 14675058 of Oracle, the SQL
always returns empty result indicating the SP doesn't exist.
Oracle changed this behavior starting from 12.1.0.2 release and
this change doesn't affect procedures without package, so this
problem only happens with data source Oracle 12.1.0.2 or later,
and this problem only happens when the procedure in the source
database is within a package. |
