Martin Evans wrote:
Christian Merz wrote:
Hi Martin,
this is not Perl stuff but Oracle PL/SQL.
I disagree. I'm using DBD::Oracle and I need the values selected in
the procedure returned to perl. 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.
Martin I would write the patch for you on that but I am not sure OCI is
aeven able to do that. If you can come up with a very simple (I mean
very simple) test case I will see what I can to tomorrow. (it is my DBD
day) Do other DBD support this sort of type???
I know it allows for the return of ref cursors is this what you want?
cheers
John
Cheers
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
this bit needs to be in perl
-- ...
-- 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:
Ok, may be it was not clear enough and that is shown in that your
example does not return any values to perl.
>>>> ... 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.
That is one of the points. I needed the sets of IDs returned to perl
but all the rows that would be scanned to get the set deleted. I also
needed the gathering of the ids and the deletion done in the same proc.
Steve gave me a possible solution.
Thanks for your input anyway.
cu, Christian
Martin
