Re: suggestions sought on returning rows from oracle proc and deleting them in same proc

Hi Martin,

this is not Perl stuff but Oracle PL/SQL.

Just take your 'get' procedure:
>>>> 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 try this -untested- code to delete the items immediately after working with them:

PROCEDURE doit AS
CURSOR curs IS
SELECT * FROM mytable
FOR UPDATE NOWAIT;
BEGIN
FOR rec IN curs
LOOP
EXIT WHEN rec%NOTFOUND;
-- do something useful with rec
-- ...
-- delete item
DELETE FROM mytable
WHERE CURRENT OF curs;
END LOOP;
-- COMMIT; -- if not done outside
-- EXCEPTION WHEN OTHERS THEN ROLLBACK;
-- probaly you would write a message in your error_log table ...?
END;

BTW: You should clerify your needs. The following is rather unspecific:
>>>> ... 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)...

SELECT DISTINCT(id) results in sets of IDs rather than single records. But you can only treat the latter ones in a LOOP.

cu, Christian


From: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Sent: 22 October 2008 17:19
To: Stewart Anderson
Cc: dbi-users
Subject: Re: suggestions sought on returning rows from oracle proc and
deleting them in same proc

Stewart Anderson wrote:
Subject: suggestions sought on returning rows from oracle proc and
deleting them in same proc

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.

.

Relevant Pages

  • Re: suggestions sought on returning rows from oracle proc and deleting them in same proc
    ... this is not Perl stuff but Oracle PL/SQL. ... The reason it is particularly applicable to DBD::Oracle is that it would be possible to solve my problem very easily IF DBD::Oracle supported the returning of oracle table types without pipelining - it doesn't. ... I also needed the gathering of the ids and the deletion done in the same proc. ... create mytable (id int, created timestamp); ...
    (perl.dbi.users)
  • Re: suggestions sought on returning rows from oracle proc anddeleting them in same proc
    ... end loop ... your perl code would call pkg1.f1 which would populate the package ... create mytable (id int, created timestamp); ... oracle table type to perl. ...
    (perl.dbi.users)
  • Re: suggestions sought on returning rows from oracle proc anddeleting them in same proc
    ... end loop ... rows are deleted in the first proc which is what I was after. ... I have a table with a simple integer and a timestamp: ... 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. ...
    (perl.dbi.users)
  • Re: suggestions sought on returning rows from oracle proc and deleting them in same proc
    ... create mytable (id int, created timestamp); ... Perl calls the first one to get the unique ids and the timestamp used ... milliseconds then the timestamp pumped in to the second proc is ... perl and simultaneously deleting same rows. ...
    (perl.dbi.users)
  • Re: suggestions sought on returning rows from oracle proc and deleting them in same proc
    ... I have a table with a simple integer and a timestamp: ... 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. ... As a result of this problem and it is 2 procs, ... 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. ...
    (perl.dbi.users)