I created a script in Oracle that runs on one owner, makes the select on several others and makes insert or update on another owner on another server through a linkedserver.
Due to the fact that I select in several owners, I created the selects in this way:
vSQL_STMT := 'SELECT campo1, campo2... FROM empresa.TBLTABELA A WHERE CDNIVELCTL = 0 AND STCOTEJ = 'S' ...'
vSQL_STMT2 := REPLACE( vSQL_STMT, 'empresa', 'XXXX' || rec.CDBASEDADOS );
EXECUTE IMMEDIATE vSQL_STMT2 into vNRITENSCOT,vNRITENSSOBRASCTB,vNRITENSSOBRASFIS,vNRCOTDIRETO,...
The variable empresa is changed at runtime to make the select in the correct owner.
And I run INSERT through LINKEDSERVER.
INSERT INTO TABELA@LINKEDSERVER VALUES( vCDJOB, vNRITENSCOT,....
If you run the script manually in SQLDeveloper, everything runs normally and works as expected.
However, if you transform this script into a procedure to run it automatically through a program, even though there is no error, the script does not work, that is, the server table is not fed.
Does anyone know how to resolve this issue?