Job Miller wrote:
From the docs:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci11obj.htm#sthref1617
OCI allows applications to access any of the datatypes found in the Oracle
database server, including scalar values, collections, and instances of any
object type. This includes all of the following:
objects
variable-length arrays (varrays)
nested tables (multisets)
references (REFs)
LOBs
Year I leaned that the hard way. Added support for reading objects,
varrays and multisets was last year add on to DBD::Oracle.
To take full advantage of Oracle server object capabilities, most applications
need to do more than just access objects. After an object has been retrieved,
the application must navigate through references from that object to other
objects. OCI provides the capability to do this. Through OCI's object
navigational calls, an application can perform any of the following functions
on objects:
creating, accessing, locking, deleting, copying, and flushing objects
getting references to the objects and their meta-objects
dynamically getting and setting values of objects' attributes
The OCI navigational calls are discussed in more detail later in this chapter.
This of course could be added to DBD::Oracle the main question being if
anyone one really wants to be able to do it. I have found that it is
usally much easer to get this form of data into Oracle easy with SQL but
a bugger to get it back out.
--- On Thu, 10/23/08, Martin Evans <[EMAIL PROTECTED]> wrote:
From: Martin Evans <[EMAIL PROTECTED]>
Subject: Re: suggestions sought on returning rows from oracle proc and deleting
them in same proc
To: "dbi-users" <[email protected]>
Date: Thursday, October 23, 2008, 8:40 AM
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.
Yeah that is the example from the pod do you have a very simple one of
what you want to return. Really just need the DDL and what you SQL
would be like
Cheers
John Scoles
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
