> 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.
>
Do you actually need to do it simultaneously?
It strikes me that you could do it all in perl.
Prepare your select statement and bind any params
Execture it.
Run through the returned rows adding each to an array.
You now have an array with all the items you need to delete
Prepare delete statement
While array {
Bind param
Delete
}
If you get any DB errors issue the rollback and you can try again.
The DBI docs are very easy to follow.
Stewart
Information in this email including any attachments may be privileged,
confidential and is intended exclusively for the addressee. The views expressed
may not be official policy, but the personal views of the originator. If you
have received it in error, please notify the sender by return e-mail and delete
it from your system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and
are used under licence. British Sky Broadcasting Limited (Registration No.
2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home
Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of British Sky
Broadcasting Group plc (Registration No. 2247735). All of the companies
mentioned in this paragraph are incorporated in England and Wales and share the
same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
