Steve Baldwin wrote:
Martin,
Personally I would use a global temporary table in this scenario.
However if that is considered offensive or morally dubious, you could do
something like :
package pkg1
type t1 is table of mytable%rowtype index by binary_integer
g_t t1
g_i pls_integer
function f1 return pls_integer
g_t.delete
g_i := null
i := 1
for rec in (
select t.*, t.rowid
from mytable t
where ... for update
) loop
g_t(i) := rec
i := i + 1
delete from mytable where rowid = rec.rowid
end loop
return i
procedure p1(
i_subscript in pls_integer
,o_id out mytable.id%type
,o_created mytable.created%type)
if i_subscript between g_t.first and g_t.last then
o_id := g_t(i_subscript).id
o_created := g_t(i_subscript).created
end if
So, your perl code would call pkg1.f1 which would populate the package
level pl/sql table, delete the rows, and return the number of 'rows' in
that table. Your code would then call pkg1.p1 passing in the subscript
(1 .. n_rows).
Hope this helps,
Steve
Nice solution Steve - thanks. Still 2 procs but ensures the selected
rows are deleted in the first proc which is what I was after.
The avoiding global temporary tables is because they are either "delete
on commit" which requires them to be in a transaction or you have to
manually delete the rows from the gtt. We've hit a number of bugs caused
by this and since attempted to avoid them.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
On Wed, 2008-10-22 at 16:28 +0100, Martin Evans wrote:
Hi,
I am hoping someone might have had to do something like this and have a
good solution. I am using DBD::Oracle. I have a table with a simple
integer and a timestamp:
create mytable (id int, created timestamp);
The integer value may appear more than once and the timestamp is the
timestamp when the row was created (it is actually created via a
trigger). Other procedures write "id"s into the table at indeterminate
times. The perl code wants to select the current distinct values of the
"id" field, do something with them and then all rows which were present
when the select was issued need to be deleted (since then more rows may
have been added which must not be touched).
This is easily achieved if I have two procs:
PROCEDURE get(
pdt OUT mytable.created%TYPE,
pcur OUT SYS_REFCURSOR) AS
BEGIN
pdt := utc_timestamp();
OPEN pcur FOR
SELECT DISTINCT(id) FROM mytable WHERE created <= pdt;
END;
and:
PROCEDURE delete(
pdt IN mytable.created%TYPE,
puid IN mytable.id%TYPE) AS
BEGIN
DELETE FROM mytable WHERE id = puid AND created <= pdt;
END;
Perl calls the first one to get the unique ids and the timestamp used to
retrieve them, does something with the returned ids and then calls the
second procedure one for each id to delete the rows. It does however,
rely on the timestamp_format as if it is changed to remove milliseconds
for instance the delete above may fail to find any rows (e.g. timestamp
used was 2008-10-01 14:20:10.5555 but timestamp format omits the
milliseconds then the timestamp pumped in to the second proc is
2008-10-01 14:20:10). As a result of this problem and it is 2 procs,
this one ruled out.
Ideally I'd like to achieve this in one procedure so I can guarantee no
matter what happens in the perl the returned rows are deleted from the
table. At first I thought that as the number of distinct ids will not be
that many I could issue my select, loop through them putting them into
an oracle table type, delete the rows in the real table then return the
oracle table type to perl. However, DBD::Oracle does not support
returning oracle table/array types other than via piped functions and as
I found, you cannot issue a delete statement in a piped function e.g.,
the following does not work:
type mytype_t IS table of mytable.id%TYPE NOT NULL;
type mytype_a IS TABLE OF mytable.id%TYPE NOT NULL
INDEX BY BINARY_INTEGER;
FUNCTION f_xxx RETURN mytable_t PIPELINED AS
CURSOR cur(param_dt mytable.created%TYPE)
IS SELECT DISTINCT(id) FROM mytable WHERE
created <= param_dt;
i integer := 1;
dt mytable.created%TYPE;
vu mytable_a;
BEGIN
dt := utc_timestamp();
FOR row in cur(dt) LOOP
vu(i) := row.id;
i := i+1;
END LOOP;
-- following delete generates an error
DELETE FROM mytable where created <= dt;
FOR i in vu.first .. vu.last LOOP
PIPE row(vu(i));
END LOOP;
END;
The problem is returning multiple values from rows in a table back to
perl and simultaneously deleting same rows. It can be achieved if I
introduce a temporary table because the proc can select the rows into
the temp table and delete the selected rows in the original table and
the perl reads the temporary table but we try very hard to avoid
temporary tables.
If DBD::Oracle supported the return of oracle table types this would be
easy.
Anyone have any other ideas?
Thanks
Martin
******************************************************************
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
******************************************************************
