Unable To Insert Numeric Values Over Db Link
(Doc ID 1922004.1)
Last updated on MARCH 05, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Inserting character data into table of the linked DB works:
insert into <table 1>@<dblink> (artnr,artgrp,acode) values ('A','B','C');
But when a numeric value ist to be inserted it does not work
insert into <table 1>@<dblink> (artnr,artgrp,acode,vk1) values ('A','B','C',1);
procedure send_artsta (nsatz out number,cerr out varchar2) AS
cursor c1 is
select distinct artsta.artnr,artsta.artgrp,artsta.acode,
case when substr(artsta.artbez,1,1)='.' then
substr(artsta.artbez,4)
else
artsta.artbez
end artbez,
artsta.vk1,artsta.vkdat,artsta.qbm,artsta.kg,artsta.mwst,
artsta.vkeinh,artsta.per,artsta.history,artsta.art,artsta.datum,artsta.preisgrp,
artsta.arab,artsta.ek,artsta.mgk,artsta.vk2,artsta.vk3
from <table 1>
where artsta.artnr in (select artkatalog.klasse from <table 3>) or
nvl(ersatzteil,'N')='J';
BEGIN
-- delete <table 1>@<dblink>;
-- delete <table 2>; --notwendig wegen lob file workaround siehe send_doku
nsatz := 0;
for i in c1 loop
insert into <table 1>@<dblink> (artnr,artgrp,acode,artbez,vk1,vkdat,qbm,kg,mwst,vkeinh,per,history,art,datum,preisgrp,arab,ek,mgk,vk2,vk3)
values (i.artnr,i.artgrp,i.acode,i.artbez,i.vk1,i.vkdat,i.qbm,i.kg,i.mwst,i.vkeinh,i.per,i.history,i.art,i.datum,i.preisgrp,i.arab,i.ek,i.mgk,i.vk2,i.vk3);
--katalogartikel
-- insert into <table 2> (artnr,sprache,artbez,history,langtext) (select artnr,sprache,artbez,history,langtext from <table 1>where artnr = i.artnr);
nsatz := nsatz+1;
end loop;
-- delete<table 2>@<dblink>;
-- insert into <table 2>@<dblink> (artnr,sprache,artbez,history,langtext) (select artnr,sprache,artbez,history,langtext from <table 2>);
END send_artsta;
Then i tested this:
insert into <table 1>@<dblink> (artnr,artgrp,acode,artbez,vk1,vkdat,qbm,kg,mwst,vkeinh,per,history,art,datum,preisgrp,arab,ek,mgk,vk2,vk3)
(select distinct artsta.artnr,artsta.artgrp,artsta.acode,artsta.artbez,
artsta.vk1,artsta.vkdat,artsta.qbm,artsta.kg,artsta.mwst,
artsta.vkeinh,artsta.per,artsta.history,artsta.art,artsta.datum,artsta.preisgrp,
artsta.arab,artsta.ek,artsta.mgk,artsta.vk2,artsta.vk3
from <table 1>
where artsta.artnr in (select artkatalog.klasse from <table 3>) or
nvl(ersatzteil,'N')='J');
I got following error:
QL-Fehler: ORA-02070: Datenbank unterstützt in diesem Zusammenhang nicht
02070. 00000 - "database %s%s does not support %s in this context"
*Cause: The remote database does not support the named capability in the context in which it is used.
*Action: Simplify the SQL statement.
00022. 00000 - "invalid session ID; access denied"
*Cause: Either the session specified does not exist or the caller does not have the privilege to access it.
*Action: Specify a valid session ID that you have privilege to access,that is either you own it or you have the CHANGE_USER privilege.
Changes
It worked in 10.2.0.4
After upgrade to 11.2.0.4.0,procedure throws error.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |