Forum Stats

  • 3,728,079 Users
  • 2,245,538 Discussions
  • 7,853,304 Comments

Discussions

Select-Statements with Shape-Fields (Oracle Spatial) fail with ORA-12570

dpal17
dpal17 Member Posts: 10 Green Ribbon

All select-statements that include a shape-field fail with

ORA-12570: Network Session: Unexpected packet read error

within VS Code.

Answers

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee
    edited July 2020

    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.

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon
    edited July 2020

    Just follow the instructions here in order to create testdata:

    https://docs.oracle.com/database/121/SPATL/simple-example-inserting-indexing-and-querying-spatial-data.htm#SPATL486

    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>).

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon

    Any updates on this subjects? It is a major problem in using VSCode in our organisation. Will it be considered to be fixed?

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee

    Hi Dpal, is this still happening with the new 19.3.3 release?

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon

    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.

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon

    Hello!

    Anything new on this subject? Still cannot use VS Code for my developement.

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee
    edited February 11

    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?

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon

    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.

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon

    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.

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee

    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.

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon
    edited February 24

    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.

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee

    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.

  • dpal17
    dpal17 Member Posts: 10 Green Ribbon

    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.

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 1,852 Employee
    edited March 3

    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.

Sign In or Register to comment.