>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 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. --- 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. > > 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 > > > >
