Dear Forum,

I am searching for advice on how best to automate the transfer of Oracle data from one instance to another using a simple web interface. For example, this application will include a button to "import data" from one instance to another, and a button to "delete data" from the second instance. Can this be accomplished using shell scripting with a cgi wrapper, or is it better managed within the database (e.g., procedures, triggers, etc.). If anyone has an example of this type of simple application I would very much like to take a look.

Thanks in advance!
Alice

Since the databases can talk to each other directly over SQL*Net using database links, that would be the simplest and most efficient approach. Ideally you would just invoke a package procedure that would do all the work.

Thanks William. That narrows down the field a bit.

I was able to set up a database link but I'm having difficulty researching how to write the package. I'm not very familiar with Oracle db so any example scripts of similar packages would be of great help to me.

Please let me know if you can recommend any. Thanks again. A

An example of a package is here. I haven't revised it in a while and it could do with an update now I look at it, but maybe it will give you some ideas.

If for any reason you are after a rambling rant about PL/SQL coding standards, look no further.

For Oracle-specific questions you could try Quest Pipelines.

Thanks again, William.

I think I got most of the syntax right - can you see what I'm missing?

CREATE OR REPLACE PACKAGE DELETE_PKG AS
PROCEDURE delete_table;
END DELETE_PKG;

CREATE PROCEDURE delete_table
AS
BEGIN
my_table.DELETE;
END;

Thanks! Nice rant too.
A

Ok, I know this is the UNIX forum, but for the sake of closure here's my latest attempt at writing an oracle delete package (as opposed the shell and cgi wrapper idea). It seems to compile but does not delete any records from my test table (tabl). I'm also trying to make it commit after every 1000 rows. Any thoughts? Thanks!!!

create or replace package apk_delete is
procedure del_table;
end apk_delete;

declare
i number := 0;
cursor s1 is select * from tab1 where rownum > 0 for update;

create or replace package body apk_delete is

procedure del_table
as

begin

for rownum in s1 loop
update tab1 set rownum = 0
where current of s1;

i := i + 1; -- commit every x records
if i > 1000 then
commit;
i := 0;
end if;

end loop;
commit;

end del_table;

Everything should be inside either the package specification (CREATE PACKAGE...END;) or package body (CREATE PACKAGE BODY...END;).

For the deletion part, it might be better just to

DELETE yourtab WHERE rownum <= n;

until SQL%ROWCOUNT is 0.

1000 could be a bit small - you would expect the average laptop to be able to delete 100,000 rows or more these days, but you could test with different values to see what performs best. As a rule using COMMIT in an attempt to avoid running out of some resource is a last resort, and if this is a regular requirement then the application should have sufficient resources to do it in one shot. Committing within a loop like this can lead to further problems. You could also use TRUNCATE as a weapon of mass destruction if complex conditions, foreign keys etc are not an issue.

ROWNUM is a pseudocolumn Oracle generates when returning rows from a query. You can't update it to some other value.

One approach to package design is to create an API for each table or group of closely related tables. Such an encapsulation package might contain procedures/functions to insert and update records as well as just deleting. For that reason I would look for a more generic package name.