- 3,728,079 Users
- 2,245,538 Discussions
- 7,853,304 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.6K All Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.4K Databases
- 694 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 499 MySQL Community Space
- 10 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 426 SQLcl
- 69 SQL Developer Data Modeler
- 185.2K SQL & PL/SQL
- 21.1K SQL Developer
- 2.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.9K Development Tools
- 18 DevOps
- 3K QA/Testing
- 388 Java
- 16 Java Learning Subscription
- 14 Database Connectivity
- 72 Java Community Process
- 3 Java 25
- 14 Java APIs
- 141.2K Java Development Tools
- 15 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 18 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 158 LiveLabs
- 36 Workshops
- 13 Software
- 7 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 19 Español
- 1.9K Japanese
- 4 Portuguese
Select-Statements with Shape-Fields (Oracle Spatial) fail with ORA-12570
All select-statements that include a shape-field fail with
ORA-12570: Network Session: Unexpected packet read error
within VS Code.
Answers
-
Hi,
Could you please give me an example Select (as simple as possible)?
Since the shape field is likely a user defined type, I need to check to see how other tools return the value, as it likely a complex type, and we wouldn't be able to display it VS Code results.
-
Just follow the instructions here in order to create testdata:
Then this select fails:
select * from cola_markets;
This one works:
select MKT_ID from cola_markets;
In my oppinion there would be no need to show the shape itsself, it would be enough to show some text in this column, e.g. [MDSYS.SDO_GEOMETRY], just like the Oracle Developer does.
Allthoug a better solution would be to display the WKT-Representation by default (
<span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">SDO_UTIL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">TO_WKTGEOMETRY</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">(SHAPE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: var(--black-750);">)</span>). -
Any updates on this subjects? It is a major problem in using VSCode in our organisation. Will it be considered to be fixed?
-
Hi Dpal, is this still happening with the new 19.3.3 release?
-
Yes, it is still the same. Allthough the behaviour is now a bit different. Before the packet read error there is now another error:
Database connection is lost. Open transaction and session state is lost. Do you want to reconnect?
But the connection works, all queries without a shape-field get results.
I upgraded also to the latest VSCode. I am connecting to the database through a TNS-Connection.
-
Hello!
Anything new on this subject? Still cannot use VS Code for my developement.
-
As a workaround, can you please try wrapping the select statement in a PL/SQL block (eg BEGIN select... ; END; \) and in the PL/SQL code output the results without using the spatial type (flatten the spatial type)?
Or perhaps you could accomplish what you want by using a view?
-
I am working in the departement for geographic information systems and we are always working on programs ans scripts and we need to be able to write select satements, and these selects have more or less all shapefields. We do not have the rights to create views in the database.
We are working in a federal enterprise environment, and our goal is to have a single developing environment. So we do not want install and maintain the SQL Developer on every computer in our department, just because they need to write some select statements.
-
I am not shure what do you mean with this answer. As I pointed out in my second post, everything with the select-statements works fine when I skipp the shape-field in the select. What do you mean with "flatten the spatial type"?
But I need the shape-fields, because this is the main purpose of my work.
-
Hi Dpal,
What is going on here is ODT for VS Code does not currently support User Defined Types, which Spatial objects are. That is why you are getting this error and otherwise not seeing your data. To workaround you can use a BEGIN END block or stored procedure. With this workaround:
1) The execution of the select statement will be done by the PL/SQL engine in the server if you surround the SELECT with BEGIN and END or use a stored procdure. This is handled differently than a plain SELECT statement in a script and should avoid the error.
2) What I meant by "flatten" is to write PL/SQL (in that same BEGIN END) block or stored procedure to select from the table containing the spatial type and return a temporary table that contains components of the spatial type as columns in the select, but does not return the actual spatial type as a column).
Unfortunately, I don't have an example of this handy right this minute, so I hope you know what I mean.
-
Hi Christian,
thanks for your answer.
Do you mean something like this:
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( objectid NUMBER ) ON COMMIT PRESERVE ROWS; begin Insert into my_temp_table (objectid) select e.objectid from edge e where e.subnetid = 14103; end;
And select afterwards from my_temp_table
Using this approach would work, yes. But as I pointed out in my comment before, building the statements is part of the development-process. I would have to refactor every single statement before deploying the programm. This does not seem very practical to me.
Are there any plans to support the custom types in the future?
It seems to me that in my case I cannot use the Oracle extension for VSCode and have to stick to the SQL Developer.
-
Yes, that is more or less what I meant by "flattening"... converting the UDT to a forum these tools can currently deal with. Unfortunately, supporting UDTs is a pretty heavyweight feature request and so it is unlikely that we will be able to do so in the near future.
-
Thanks for your help. It seems, that I am stuck to SQL Developer for a while.
As I pointed out in my comment at the beginning, I would not need any big actions or interpretations with/of the UDTs, just that the query does not crash. :-)
So I will write my queries without the Shape-Fields and add them at the end of the development-process and test them in my GIS-program.
-
I will check to see if we can at least get rid of the error message when UDT's are in the results. Thanks for the feedback.