Extract Contents Of Varray with SQL

Posted on 2007-10-16
Last Modified: 2013-12-19
 In oracle spatial, I can do this to get a list of varrays for each geometry record

select t.geometry.sdo_ordinates from table t

and i can do this to return the individual coordinates for one of those varrays

select * from table(select t.geometry.sdo_ordinates from table t where rownum = 1)

but what i would really like is a string with those coordinates concatenated together, for each varray
I know i can do this in pl/sql, but is there is a way to do this just in sql?
Question by:chrismarx
    LVL 73

    Accepted Solution

    go to

    search for stragg and then try this...

    select stragg(to_char(column_value)) from table(select t.geometry.sdo_ordinates from table t where rownum = 1)

    Author Comment

     Im aware of stragg, but that really isnt my problem. I need to be able to do the above query but for the sub query in table without the use of the rownum

    Author Comment

    i guess the way to do this is really just create a pl/sql function to loop through the varray and then call it in sql
    LVL 73

    Expert Comment

    hmm, I'm not sure I understand what the problem is.  Can you post some insert statements and sample output of what you'd like it to do?  I'll see if I can figure out a SQL to do it.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now