Oracle output: cursor, file, or very long string?
- by Klay
First, the setup: I have a table in an Oracle10g database with spatial columns. I need to be able to pass in a spatial reference so that I can reproject the geometry into an arbitrary coordinate system. Ultimately, I need to compress the results of this projection to a zip file and make it available for download through a Silverlight project.
I would really appreciate ideas as to the best way to accomplish this. In the examples below, the SRID is the Spatial reference ID integer used to convert the geometric points into a new coordinate system.
In particular, I can see a couple of possibilities. There are many more, but this is an idea of how I'm thinking:
a) Pass SRID to a dynamic view -- perform projection, output a cursor -- send cursor to UTL_COMPRESS -- write output to a file (somehow) -- send URL to Silverlight app
b) Use SRID to call Oracle function from Silverlight app -- perform projection, output a string -- build strings into a file -- compress file using SharpZipLib library in .NET -- send bytestream back to Silverlight app
I've done the first two steps of b), and the conversion of 100 points took about 7 seconds, which is unacceptably slow. I'm hoping it would be faster doing the processing totally in Oracle.
If anyone can see potential problems with either way of doing this, or can suggest a better way, it would be very helpful.
Thanks!
