| Copy from -- append [message #4386] |
Wed, 04 December 2002 08:33  |
jerome
Messages: 13
Registered: May 2000
|
Junior Member |
|
|
Hi all,
I am using copy -- append to copy data from a Db into another one.
No problem, it works fine from SQLPlus Worksheet.
However, as this process is to be run back end, I want to run it from a procedure, but I am not able to do it, the procedure is as follow:
<<<
create or replace procedure sp0_GerRates
(
XRateSource in varchar2,
GetDate in date
)
as
begin
set copycommit 1
set arraysize 2000
copy from User/pwd@Server -
append urf_XRATE -
using select * from table where source = XRateSource and rateDT = GetDate;
end;
>>>
Unfortunately I am not able to compile it without error...
Does anyone know how to use Copy -- append within a procedure?
Thanks in advance
Jerome
|
|
|
|
| Re: Copy from -- append [message #4391 is a reply to message #4386] |
Wed, 04 December 2002 11:31  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
COPY and the SET commands are part of SQL*Plus syntax, not PL/SQL syntax. In PL/SQL, you would need a defined database link to the source data, and then in the target database:
insert /*+ append */ -- hint optional
into urf_xrate
select *
from table@dblink_name
where source = XRateSource
and rateDT = GetDate; |
|
|
|
|
|
| Re: Copy from -- append [message #4409 is a reply to message #4395] |
Thu, 05 December 2002 10:31 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Dynamic SQL is not needed at all for this - it is just a matter of using the appropriate syntax for your environment (SQL*Plus or PL/SQL).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
