I want to create a shapefile from a view in Oracle Spatial. View is created by another application by querying number of table. So schema of the view changes every time. I am able to create shapefile first time but once schema changes I am not able to create. What should I need to do to handle this dynamic schema.
Cheers
You gave the answer yoursefl
use the dynamic mode.Tha way the writer will read the schema and apply it while running the worksoace.
If it is not a MV then u can extract the SQLquery of a certain view by
SELECT text
FROM all_views
WHERE view_name = 'DGDTW_VW_FUG'
(replace 'DGDTW_VW_FUG' with view you want to read of course)
1 Then parse the txt record using stringsearcher. For example regexp=" \\(select (.*) from .*"
2 Then Use attributesplitter to put result in a list; splitcharacter = ",".
Explode list using listexploder.
3 etc. if and when nescessary
Stringsearcher or tester to filter out things like unions etc.
It depends on how complex the View is actualy.
If it is a simple select from then steps 1 and 2 will do.
If it is a very complex view, u need to finetune stringsearchering and regularexpressions.
I tried using dynamic mode but not able to create output.
Cheers
Cheers
I assume you are not tallking about materialised views.
So to get its schema, you can not use a schemareader, like you can with a materialised view or normal table.
The method above can read the schema.
Third column are the attributes.
Once you have the schema, you can use it to create a schemamap and then use this map to dynamicaly map it to the shape objects.
(if you're interested i can post the workbench i just made to create above table)
Yes view is created by other application depending on user selection on attributes. Now I am able to get the schema as suggested but still struggling to bind this schema with shape objects. Any pointers in this direction?
Cheers
On writer's general tab you can choose Dynamic properties and point to the schema resource.
Of course you have to write the extracted shema to a file first.
And of course you need the objects form the view...
With
SELECT text
FROM all_views
WHERE view_name = 'DGDTW_VW_FUG'
Connect a SQL executor and enter the "text' as sql. It should run and get the geoobjects.
Write them out to shape using the schema you created..
(of course take care running sql you dont know...lol)
If I understand corrrectly, this means I need 2 workbenches first to create schema resource and second main workbench. Is this correct or am I missing something.
Cheers.
Schema resource must be create first. (because view is unknown as well as its attributes)
You can have the one in wich you create the schema call the main trough a workspacecaller.
btw i tested these suggestions on some views here, and came across a view wich was larger then the maximum characters fo a text field...means it could not run the sql as it didnt fit. (most of the views fit though..)
You may want to keep an eye on this.