建立dblink,dblink使用参考http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htmDefining a Fixed-User Database Link: ExampleIn the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';Once this database link is created, hr can query tables in the schema hr on the local database in this manner:SELECT * FROM employees@local;User hr can also use DML statements to modify data on the local database:INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK');UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK';DELETE FROM employees@local WHERE employee_id = 999;Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:SELECT * FROM oe.customers@local;然后使用 create or replace procedure sp_export is begin insert into table1@dblink1 select * from table1@dblink2; end ;
如果不能建dblink就只能先把输出导成文本,然后SQL*Loader进去了
我试了下,为什么会出现这种问题:Compilation errors for PROCEDURE COGNOS.P_GETDATA_LCPRODSETError: PLS-00103: 出现符号 "end-of-file"在需要下列之一时: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe Line: 5 怎么解决啊?说详细点,谢谢了!!!存储过程是: create or replace procedure p_getData_LCProdSet is begin insert into LCProdSet select * from LCProdSet@UATDB end ;
create or replace procedure p_getData_LCProdSet is begin insert into LCProdSet select * from LCProdSet@UATDB; --这里少了分号 end ;
CONNECT TO hr IDENTIFIED BY hr
USING 'local';Once this database link is created, hr can query tables in the schema hr on the local database in this manner:SELECT * FROM employees@local;User hr can also use DML statements to modify data on the local database:INSERT INTO employees@local
(employee_id, last_name, email, hire_date, job_id)
VALUES (999, 'Claus', '[email protected]', SYSDATE, 'SH_CLERK');UPDATE jobs@local SET min_salary = 3000
WHERE job_id = 'SH_CLERK';DELETE FROM employees@local
WHERE employee_id = 999;Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:SELECT * FROM oe.customers@local;然后使用
create or replace procedure sp_export is
begin
insert into table1@dblink1 select * from table1@dblink2;
end ;
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Line: 5
怎么解决啊?说详细点,谢谢了!!!存储过程是:
create or replace procedure p_getData_LCProdSet is
begin
insert into LCProdSet select * from LCProdSet@UATDB
end ;
begin
insert into LCProdSet select * from LCProdSet@UATDB; --这里少了分号
end ;