> From: Martin Evans [mailto:[EMAIL PROTECTED]
> 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.
> >>
> > 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.
> >
> >
>
> You are right, you can do it all in perl. However, what I forgot to
> mention is the user connected to the database has no select permission
> on the tables and so the code needs to be in a proc that runs with
> definer rights. None of our perl has any access to the database other
> than through procedures and functions in packages with definer rights.
>
Ah :)
