John,
John Scoles wrote:
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.
Neither am I - I've asked our resident OCI expert but he has not come
back to me yet.
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?
I use reference cursors extensively already since the connected user
has no read permission on any tables. The example below for function
f_xxx is one I don't believe you can call from DBD::Oracle.
cheers
John
I wasn't attempting to provoke someone (you?) into adding support for
oracle table/array types to DBD::Oracle but the fact that they may only
be obtained via pipelined functions and you cannot perform DML
statements in a pipelined function meant I had a problem I could not see
a solution to. I only mentioned the lack of support for oracle table
types (without pipelined functions) because it was that which made it a
perl DBD::Oracle issue and not just a pl/sql issue.
I believe the following demonstrates:
CREATE OR REPLACE PACKAGE test IS
type a_t IS TABLE OF integer NOT NULL;
type a_a IS TABLE OF integer NOT NULL INDEX BY BINARY_INTEGER;
FUNCTION f_xxx RETURN a_a;
FUNCTION f_xxx_pipe RETURN a_t PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY test IS
FUNCTION f_xxx RETURN a_a AS
vv a_a;
BEGIN
vv(1) := 1;
vv(2) := 2;
RETURN vv;
END;
FUNCTION f_xxx_pipe RETURN a_t PIPELINED AS
vv a_a;
i integer;
BEGIN
vv := f_xxx();
FOR i IN vv.first .. vv.last LOOP
PIPE row(vv(i));
END LOOP;
END;
END;
/
show errors;
QUIT
perl -e 'use DBI;my $h = DBI->connect("dbi:Oracle:XX","xx","xx");my $s =
$h->prepare(q/select * from table(test.f_xxx_pipe)/);$s->execute;print
DBI::dump_results($s);'
"1"
"2"
2 rows
As far as I know
(http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Returning_A_Recordset)
there is no way to get that table back from f_xxx other than pipelining.
Don't knock yourself out for my sake on this one; I have an acceptable,
workable alternative. However, if it might be useful for anyone else
that is a different matter.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
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
