Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: Select on a VARRAY field

    Hello again,

    I've been starting to work with varray fields, and with the creation and inserting and updating records, I've got no problem. The situation is, how can I return a position of the array on a select.

    Ex:

    CREATE TYPE demo AS VARRAY(2) OF VARCHAR2(25);

    CREATE TABLE test
    (id NUMBER(10,0) NOT NULL,
    array DEMOS);

    INSERT INTO test VALUES (1,demo('John','Smith'));

    How can I get a result like this: 1, Smith.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can't:
    Piecewise DML is possible only on nested tables, not on varrays.
    - Oracle9i Application Developer's Guide - Object-Relational Features.

    You could write a PL/SQL function to do it and call that from SQL something like:

    select id, myfun(array,2) from test;

    In general, VARRAYs are about as useful as a chocolate teapot really.

  3. #3
    Join Date
    May 2004
    Posts
    95
    ok...
    cutting the varray of the picture. I was first trying with another kind of object, but the problem was the same so I wondered if the VARRAY was easier, but now I see it isn't.
    And if instead of a VARRAY, I've got something like:

    CREATE TYPE demo AS OBJECT (age NUMBER, birth_place VARCHAR(25));

    CREATE TABLE test
    (id NUMBER(10,0) NOT NULL,
    array DEMO);

    INSERT INTO test VALUES (1,demo(25,'London'));

    The result the same: 1, London.
    Last edited by xixo; 04-11-07 at 11:06.

  4. #4
    Join Date
    May 2004
    Posts
    95
    nevermind... I've find it how.

    Many thanks.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is the solution? Others may benefit from seeing it. I can't see how you can get a particular row (e.g. 2nd) from a nested table unless you have another column in it to number the rows. You can easily get all the rows, or the row where age=25, but not "the first row" or "the second row" in any meaningful sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •