[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

Extract Contents Of Varray with SQL

Hi,
 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?
0
chrismarx
Asked:
chrismarx
  • 2
  • 2
1 Solution
 
sdstuberCommented:
go to http://asktom.oracle.com

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)
0
 
chrismarxAuthor Commented:
thanks,
 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
0
 
chrismarxAuthor Commented:
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
0
 
sdstuberCommented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now