Unable To Insert Numeric Values Over Db Link (Doc ID 1922004.1)

Last updated on AUGUST 04, 2018

Applies to:

Symptoms

inserting character data into table of the linked DB works:
insert into tartsta@vt (artnr,artgrp,acode) values ('A','B','C');
but when a numeric value ist to be inserted it does not work
insert into tartsta@vt (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 artsta
       where artsta.artnr in (select artkatalog.klasse from artkatalog) or
          nvl(ersatzteil,'N')='J';
       
 BEGIN
--    delete artsta@vt;
--    delete vt_arttext;  --notwendig wegen lob file workaround siehe send_doku
   nsatz := 0;
   for i in c1 loop
      insert into tartsta@vt (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 vt_arttext (artnr,sprache,artbez,history,langtext) (select artnr,sprache,artbez,history,langtext from arttext where artnr = i.artnr);
      nsatz := nsatz+1;  
   end loop;
--    delete arttext@vt;
--    insert into arttext@vt (artnr,sprache,artbez,history,langtext) (select artnr,sprache,artbez,history,langtext from vt_arttext);
 
 END send_artsta;

Then i tested this:
insert into tartsta@vt (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 artsta
       where artsta.artnr in (select artkatalog.klasse from artkatalog) 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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.