FutureDBA-
asked on
inserting data into remote SQL server.
insert into cxprddta.RMOTRI@E (select * from rmotri)
Fails, with
SQL Error: ORA-02025: all tables in the SQL statement must be at the remote database
02025. 00000 - "all tables in the SQL statement must be at the remote database"
*Cause: The user's SQL statement references tables from multiple databases.
The remote database is not Oracle V7 or above, and can perform
updates only if it can reference all tables in the SQL statement.
*Action:
Does anyone know of a work around.
Note: the remote table has fields that cannot be null
ASKER
I've also tried the following, which does not work either, the block completes, but no data at remote table, my local and remote tables are have identical column names and types.
DECLARE
CURSOR remote_insert IS SELECT * FROM RMOTRI;
BEGIN
FOR rec IN remote_insert LOOP
INSERT INTO cxprddta.rmotri@E (OTRCUSCHN,OTRCUSNUM,OTRSUBCUS,OTRDELDTE,
OTRLODDTE,OTRPCKDTE,OTRRTENUM,OTRSUBRTE,
OTRPURNUM,OTRITMNUM,OTRCOMTYP,OTRSLSTYP,
OTRSEQNUM,OTRTRNQTY,OTRORWQTY,OTRTRNPRC,
OTRORDTYP,OTRDYSSTR,OTRUPCNUM,OTRCUSITM,
OTRUPCTYP,OTRSTATUS,OTRERRMSG,OTRPRCUSR,
OTRPRCDTE,OTRPRCTIM,X_UPID,X_RRNO)
VALUES (
rec.OTRCUSCHN,rec.OTRCUSNUM,
rec.OTRSUBCUS,rec.OTRDELDTE,
rec.OTRLODDTE,rec.OTRPCKDTE,
rec.OTRRTENUM,rec.OTRSUBRTE,
rec.OTRPURNUM,rec.OTRITMNUM,
rec.OTRCOMTYP,rec.OTRSLSTYP,
rec.OTRSEQNUM,rec.OTRTRNQTY,
rec.OTRORWQTY,rec.OTRTRNPRC,
rec.OTRORDTYP,rec.OTRDYSSTR,
rec.OTRUPCNUM,rec.OTRCUSITM,
rec.OTRUPCTYP,rec.OTRSTATUS,
rec.OTRERRMSG,rec.OTRPRCUSR,
rec.OTRPRCDTE,rec.OTRPRCTIM,
rec.X_UPID, rec.X_RRNO
);
END loop;
END;
ASKER
however,
DOES WORK
insert into cxprddta.RMOTRI@E values (9007, 10538, 1, 20131128, 20131127, 0, 29, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1300, 63);
DOES WORK
are you doing a commit when you run the pl/sql block?
As sdstuber mentioned, did you commit after the PL/SQL block.
From the documentation, this is a restriction on remote calls - the issue is with what they call CALLBACK LINKS:
http://docs.oracle.com/cd/E11882_01/server.112/e11050/majfeat.htm#CIHECAIE
The work around they suggest is exactly what you've done with the PL/SQL block. If it wasn't committed, it wouldn't appear in the remote database.
You don't say if you're using the Generic ODBC Gateway or the SQL Server specific Gateway, but they but they both say the same thing:
Generic ODBC: http://docs.oracle.com/cd/E11882_01/gateways.112/e12070/feature.htm#ODBCU764
SQL Server: http://docs.oracle.com/cd/E11882_01/gateways.112/e12069/ch3.htm#GMSWN839
From the documentation, this is a restriction on remote calls - the issue is with what they call CALLBACK LINKS:
http://docs.oracle.com/cd/E11882_01/server.112/e11050/majfeat.htm#CIHECAIE
The work around they suggest is exactly what you've done with the PL/SQL block. If it wasn't committed, it wouldn't appear in the remote database.
You don't say if you're using the Generic ODBC Gateway or the SQL Server specific Gateway, but they but they both say the same thing:
Generic ODBC: http://docs.oracle.com/cd/E11882_01/gateways.112/e12070/feature.htm#ODBCU764
SQL Server: http://docs.oracle.com/cd/E11882_01/gateways.112/e12069/ch3.htm#GMSWN839
ASKER
its sql server gateway.. it seems to work, but the performance is a huge issue. on an insert, for me to insert 1200 records it would take me a few seconds,
same insert with the pl/sql block is taking about a minute.
same insert with the pl/sql block is taking about a minute.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
doesn't seem to be permitted on remote tables either.
Error report -
ORA-06550: line 10, column 5:
PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
bulk operations if possible, iterate if necessary