Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles

    Tip: Drop that Spatial Index!
    Convert Single Geometry to Multi-part Geometry in Oracle Spatial
    Optimized Rectangle to 5 Point Polygon
    Centroid Package now supports Y ordinate seeding
    Convert GeoJSON document to Sdo_Geometry objects
    Implementation Of Travelling Salesman Problem
    Create Polygon From Bearings And Distances
    Function That Returns a Compass Point From a Whole Circle Bearing
    Playing around with Centroids by using different seed values
    GeoRaptor 4.x Update 2
    Simple Oracle C Sprintf or Java String.format
    Some Oriented Point Functions
    Extracting Inner Rings Changed Ordinate Ordering: A Trap For Players Who Don't Read Documentation!
    PLS-00306: wrong number or types of arguments in call to 'SDO_GEOMETRY'
    Converting Google Earth Formatted Longitude/Latitude points to decimal degrees
    Oracle Business Intelligence Warehousing and Analytics - Spatial Summit
    How far inside, is inside? Measuring actual distance.
    Noding and building a polygon from single, overlapping linestrings
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison
    ST_VertexN / ST_PointN - Extracting a specific point from any geometry
    Convert Single Point stored in SDO_ORDINATES to SDO_POINT_TYPE
    Aggregate APPEND Islands and XOR polygons
    Circular Arcs in Geodetic Polygons
    Some SDO_GEOMETRY/DIMINFO handling functions
    Applying And Extending Oracle Spatial - Book Released
    Changing all DIMINFO sdo_tolerance values for all metadata records in one go.
    Building Polygons from Incomplete Linestrings using ST_PolygonBuilder
    Computing Cardinal Directions to nearby geometries
    Intersecting two aggregated polygon layers with SC4O
    Spatial and Oracle 12c
    Update Triggers and SDO_GEOMETRY Equality
    Duplicate Geometry data and Data Models
    CENTROID package update
    How to calculate cumulative length of a linestring
    Useful Package of Wrapper Functions for Sdo_Util.AffineTransforms
    Compute Location from known Lat/Long point using delta easting and northing in miles
    SDO_AGGR_SET_UNION
    Sorting SDO_GEOMETRY data using the ORDER BY clause of a SELECT statement
    Creating linestrings from points
    Rounding Coordinates or Ordinates in SDO_GEOMETRY
    Effects of Sdo_Geometry Ordinate Precision on Performance
    Effects of Sdo_Geometry Ordinate Precision on Storage
    The Spatial filtering of geometries: The effect of tolerances on relationships
    Application of Delaunay Triangulation and Inverse Distance Weighting (IDW) in Oracle for Soils Interpolation
    Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema
    CENTROID package - Tips for Use
    Announcing the Spatial Companion For Oracle (SC4O)
    Filtering Rings (Oracle Spatial)
    Splitting a polygon using one or more linestrings
    isValid, isSimple, Dimension and CoordDim methods for SDO_Geometry
    Line Merging or Collecting lines together: ST_LineMerger
    ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS
    3D/4D and SRID aware Conversion functions for SDO_Geometry: WKT and EWKT
    Topological vs Non-Topological Simplification/Generalization of Aggregated Area Geometies in Oracle
    Filtering very short linestrings via bitmap function index
    CENTROID For Oracle
    Gridding a sdo_geometry line/polygon object (Oracle)
    Finding centre and radius of a circular geometry
    Constraining geometry type for sdo_geometry column in a table.
    CASE Statements and SDO_GEOMETRY
    The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points
    Replacement for SDO_GEOM.RELATE - ST_Relate based on JTS
    Changing Oracle Spatial Index Parameters on existing index
    Writing Excel Spreadsheets files from within the Oracle database using Java and PL/SQL
    Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL
    A simple spike finder for Spatial/Locator
    JTS Java class compilation for 11g and above
    Random Spatial Search Procedure
    Geometry Snapping using JTS in Oracle
    Exposing JTS's MinimumBoundingCircle functionality
    Exposing JTS's Densifier functionality
    Using JTS's Comparison Functions - HausdorffSimilarityMeasure & AreaSimilarityMeasure with SDO_GEOMETRY
    Free JTS-based Area/Length Functions
    Handy way of systematically fixing polygon geometries with 13349 and other errors
    Standalone CENTROID package now available for download
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 4 Processing Geodetic data
    Configurable Buffer: JTS and Oracle
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 3
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 2
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 1
    Building Lines into Polygons in Oracle Locator / Spatial
    Finding Intersection Points between Line and Polygon
    SDO2GeoJSON
    Free version of sdo_length
    Alternative to my SQL based GetNumRings function
    External Tables and SDO_Geometry data.
    layer_gtype keyword issue when indexing linear data on 11g
    String Tokenizer for Oracle
    Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g
    Reducing 5 Vertex Polygon to Optimized Rectangle
    Square Buffer
    Converting decimal seconds to string
    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT - 13356 Issues
    Valid conversion unit values for Oracle sdo_geom.sdo_length()
    Removing Steps in Gridded Vector Data - SmoothGrid for Oracle
    Oracle Spatial DISJOINT search/filtering
    Creating SDO_Geometry from geometric data recorded in the columns of a table
    Concave Hull Geometries in Oracle 11gR2
    Projecting SDO_GEOM_METADATA DIMINFO XY ordinates
    Instantiating MDSYS.VERTEX_TYPE
    New PL/SQL Packages - Rotate oriented point
    GeoRaptor Development Team
    Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function
    Performance of PL/SQL Functions using SQL vs Pure Code
    Implementing the BEST VicGrid Projection in Oracle 10gR2
    Making Sdo Geometry Metadata Update Generic Code
    ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
    Extract Polygons from Compound Polygon
    Detecting sdo_geometries with compound (3-point Arcs) segments
    GEOMETRY_COLUMNS for Oracle Spatial
    Convert GML to SDO_Geometry in Oracle 10gR2
    Spatial Sorting of Data via Morton Key
    Swapping Ordinates in an SDO_GEOMETRY object
    New To_3D Function
    Extend (Reduce/Contract/Skrink) Function for Oracle
    Loading and Processing GPX 1.1 files using Oracle XMLDB
    Loading Spatial Data from an external CSV file in Oracle
    Calling the Oracle Spatial shapefile loader from within the Oracle database itself
    Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle
    Implementing SDO_RemovePoint/ST_RemovePoint for Oracle
    Implementing SDO_AddPoint/ST_AddPoint for Oracle
    ESRI ArcSDE Exverted and Inverted Polygons and Oracle Spatial
    Funky Fix Ordinates By Formula
    Implementing a SetPoint/ST_SetPoint function in Oracle
    Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial
    Generating random point data
    Implementing an Affine/ST_Affine function for Oracle Spatial
    Implementing a Scale/ST_Scale function for Oracle Spatial
    Implementing a Parallel/ST_Parallel function for linestring data for Oracle Spatial
    Implementing a Rotate/ST_Rotate function for Oracle Spatial
    Limiting table list returned when connecting to Oracle Database using ODBC
    ST_Azimuth for Oracle: AKA Cogo.Bearing
    Implementing a Translate/ST_Translate/Move function for Oracle Spatial
    Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself
    Minumum Bounding Rectangle (MBR) Object Type for Oracle
    How to extract elements from the result of an sdo_intersection of two polygons.
    How to restart a database after failed parameter change
    Fixing failed spatial indexes after import using data pump
    generate_series: an Oracle implementation in light of SQL Design Patterns
    Multi-Centroid Shootout
    Oracle Spatial Centroid Shootout
    On the use of ROLLUP in Oracle SELECT statements
    Surrounding Parcels
    Spatial Pipelining
    Using Oracle's SDO_NN Operator - Some examples
    Converting distances and units of measure in Oracle Locator
    Split Sdo_Geometry Linestring at a known point
    Forcing an Sdo_Geometry object to contain only points, lines or areas
    Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure using SQL
    Generating multi-points from single point records in Oracle Spatial
    Object Tables of Sdo_Geometry
    Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package
    FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns
    Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data
    Loading Shapefiles (SHP) into Oracle Spatial
    Oracle Spatial Mapping and Map Rendering Performance Tips
    The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?
    Oracle Spatial Forum - Melbourne April 2007
    Layer_GTypes for spatial indexes
    Oracle's SQL/MM Compliant Types
    Tips and Tricks

Search

    Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

PLS-00306: wrong number or types of arguments in call to 'SDO_GEOMETRY'

Sunday December 20 2015 at 09:15

Keywordsconstruct large sdo_geometry object Sql Developer SqlPlus Pls-00306 Error
Summary

Constructing large sdo_geometry objects in Sql Developer and SqlPlus. Pls-00306 Error

At the business I am currently contracted to to provide database modeling, data quality analysis, PLSQL development and spatial expertise, an application was having problems querying a WFS server
(MapGuide, using FDO to access an Oracle 11gR2 database) with a WFS filter using a very large polygon. (All the issues seem to be with getting the right settings for the Apache webserver to handle
large post packages).

The application developers, to ensure they knew what the right answer should be when the WFS filter query executed correctly (against a table of point objects), a programmer took the GML polygon,
reformatted it as a SDO_GEOMETRY object and tried to use it to determine how many point objects should be returned within SQL Developer.

They could not get the SQL to run getting the following error:

  1. SQL Error: ORA-00939: too many arguments FOR FUNCTION

This is a SQL level restriction that shows itself mainly when constructing big geometries using ad-hoc queries executed via a SQL Developer Worksheet, SQL Plus etc.
The restriction is one that does not allow the SDO_ORDINATE_ARRAY to be instantiated with more than 999 values (ie 499 2D coordinates). This restriction is a SQL restriction
it does not apply to programmatic interfaces such as exposed though Oracle Spatial’s sdoapi.jar for Java programmers.

We can show how this restriction turns around the number of vertices.

Firstly, find two US state polygons whose vertices are less than and greater than 500 vertices:

  1. SELECT Gid, State,
  2.        Sdo_Util.Getnumvertices(Geometry)
  3.   FROM Us_States
  4.  ORDER BY 3 DESC;
  5. .....
  6. 17,KS,514
  7. 53,WY,421

Firstly, extract the geometry for Wyoming:

  1. SELECT Sdo_Util.Getnumvertices(Geometry) AS numVertices,
  2.        codesys.linear.st_roundOrdinates(geometry,5,5,2,2) AS geometry
  3.   FROM Us_States
  4.  WHERE State = 'WY';
  5. NUMVERTICES GEOMETRY
  6. ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7.         421 SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-111.05514,44.66688,-111.04873,44.47723,-111.04873,44.4772,-111.04816,44.24178,-111.04813,44.22693,-111.04792,44.13511,-111.0477,44.11022,-111.04702,44.03275,-111.04662,43.98854,-111.04662,43.98554,-111.04613,43.88206,-111.04549,43.72747,-111.04539,43.65012,-111.04528,43.56948,-111.04501,43.55532,-111.04491,43.55032,-111.04498,43.50614,-111.04494,43.50049,-111.04391,43.49977,-111.04362,43.31358,-111.04387,43.28166,-111.04454,43.19794,-111.04454,43.17014,-111.04454,43.1442,-111.04452,43.14106,-111.04442,43.08366,-111.04428,43.05187,-111.04414,43.01995,-111.04413,43.0182,-111.04446,42.9263,-111.04446,42.84705,-111.04453,42.84123,-111.04459,42.81796,-111.04465,42.78892,-111.04546,42.73518,-111.04591,42.70563,-111.04655,42.65152,-111.04683,42.61197,-111.04692,42.51373,-111.0475,42.51359,-111.04769,42.4469,-111.04693,42.43872,-111.04721,42.42599,-111.04675,42.29326,-111.0467,42.2769,-111.04647,42.21101,-111.04633,42.1701,-111.0465,42.14002,-111.04674,42.09921,-111.04626,42.01016,-111.04628,42.00142,-111.04628,42.00048,-111.04637,41.93655,-111.04647,41.86251,-111.04651,41.83344,-111.04655,41.80573,-111.04665,41.73257,-111.04631,41.57817,-111.04626,41.53873,-111.04613,41.45112,-111.04609,41.42299,-111.04607,41.40845,-111.04604,41.39439,-111.04602,41.37896,-111.04598,41.34945,-111.04596,41.33493,-111.04594,41.32086,-111.04585,41.25143,-111.04584,41.24261,-111.04559,40.99778,-110.81728,40.99775,-110.77522,40.99765,-110.64185,40.99733,-110.62235,40.99728,-110.60347,40.99723,-110.58357,40.99718,-110.58337,40.99718,-110.57851,40.99717,-110.57362,40.99716,-110.56511,40.99714,-110.54715,40.9971,-110.54621,40.9971,-110.52733,40.99705,-110.50847,40.99701,-110.49374,40.99697,-110.49277,40.99695,-110.48896,40.99693,-110.27641,40.99627,-110.27238,40.99634,-110.21799,40.99723,-110.19503,40.99756,-110.16077,40.99792,-110.05729,40.99876,-110.0485,40.99883,-110.00014,40.99923,-109.94201,40.99935,-109.8862,40.99942,-109.81077,40.99952,-109.72911,40.99957,-109.68892,40.99967,-109.66943,40.99972,-109.65546,40.99968,-109.6433,40.99964,-109.62624,40.99959,-109.59092,40.99948,-109.58846,40.99947,-109.50869,40.9997,-109.50504,40.99971,-109.47078,40.99981,-109.4685,40.99983,-109.44231,41.00003,-109.23518,41.00158,-109.21952,41.00169,-109.21873,41.00169,-109.1962,41.00154,-109.11826,41.00072,-109.10261,41.00056,-109.08112,41.00058,-109.04901,41.00003,-108.97291,41.00018,-108.93151,41.00013,-108.91203,41.0001,-108.76553,41.00019,-108.74603,41.00016,-108.65163,41.00052,-108.63214,41.00048,-108.56988,41.00029,-108.3781,40.99971,-108.3745,40.99973,-108.3118,41.00004,-108.2923,41.00014,-108.26303,41.00029,-108.2186,41.00066,-108.18085,41.00098,-108.00069,41.00249,-107.96598,41.00277,-107.9154,41.00294,-107.88805,41.00294,-107.88009,41.00288,-107.85211,41.00286,-107.83914,41.00285,-107.83263,41.00284,-107.8206,41.00283,-107.81314,41.00283,-107.7845,41.00283,-107.70779,41.00282,-107.67674,41.00281,-107.60489,41.0028,-107.52878,41.00256,-107.51358,41.00262,-107.50928,41.00262,-107.49471,41.00264,-107.45753,41.00266,-107.4137,41.00291,-107.39483,41.00303,-107.36745,41.00319,-107.34371,41.00332,-107.31812,41.00346,-107.30596,41.00341,-107.22985,41.00352,-107.13548,41.00365,-107.02586,41.00428,-107.00211,41.00442,-106.86391,41.0041,-106.59114,41.00346,-106.58201,41.00375,-106.57226,41.00377,-106.54365,41.00381,-106.48642,41.00328,-106.45597,41.00347,-106.44805,41.00352,-106.37926,41.00264,-106.35186,41.00246,-106.33178,41.00251,-106.3263,41.00253,-106.32574,41.00235,-106.32148,41.00098,-106.31714,40.99938,-106.20329,40.99889,-106.19669,40.99886,-106.19598,40.99886,-106.08701,40.99798,-106.06814,40.99783,-106.06032,40.99777,-106.02856,40.99766,-106.02379,40.99765,-105.99635,40.99845,-105.97196,40.99916,-105.93848,40.99994,-105.92629,41.00003,-105.9001,40.99986,-105.88063,40.99972,-105.69796,40.9998,-105.69003,40.9998,-105.6712,40.9998,-105.52073,40.99966,-105.47701,40.99953,-105.35509,40.99929,-105.28949,40.9992,-105.27631,40.9998,-105.11086,40.99932,-104.94246,40.99948,-104.93848,40.99948,-104.83409,40.99956,-104.83003,40.99957,-104.70135,41.00352,-104.68366,41.00407,-104.66476,41.00465,-104.63366,41.00561,-104.61342,41.00476,-104.60869,41.00456,-104.56796,41.00285,-104.56065,41.00284,-104.48755,41.00271,-104.4725,41.00268,-104.13991,41.0019,-104.05284,41.0017,-104.05258,41.02359,-104.05199,41.39493,-104.05212,41.56544,-104.05225,41.6975,-104.05223,41.70037,-104.05247,41.99803,-104.05247,42.00245,-104.05247,42.08791,-104.05248,42.6128,-104.05277,42.93345,-104.05265,43.00138,-104.05265,43.0028,-104.05342,43.0028,-104.05363,43.12841,-104.05305,43.1976,-104.05309,43.21216,-104.05326,43.29731,-104.05355,43.38927,-104.05365,43.42211,-104.0541,43.45354,-104.05397,43.46766,-104.05389,43.47716,-104.05408,43.48269,-104.05421,43.50365,-104.0546,43.56742,-104.05456,43.57415,-104.05462,43.64658,-104.0547,43.67133,-104.05484,43.71325,-104.05488,43.7251,-104.05492,43.7377,-104.05471,43.757,-104.0546,43.76687,-104.05466,43.8012,-104.05483,43.85089,-104.05484,43.853,-104.05466,43.87277,-104.0547,43.88735,-104.05479,43.91515,-104.05484,43.9293,-104.05506,43.99656,-104.05508,44.00134,-104.05507,44.01592,-104.05501,44.09574,-104.055,44.10211,-104.05499,44.11123,-104.05497,44.13676,-104.05496,44.14155,-104.05503,44.14407,-104.05505,44.15183,-104.05512,44.18148,-104.05584,44.47406,-104.05621,44.53922,-104.05639,44.5717,-104.05711,44.98175,-104.05713,44.99866,-104.08103,44.99845,-104.1004,44.99902,-104.1656,44.99881,-104.17958,44.99876,-104.20026,44.99899,-104.22305,44.99924,-104.29463,44.99945,-104.39809,44.99917,-104.50175,44.99878,-104.57722,44.99866,-104.5917,44.99864,-104.59815,44.9987,-104.62953,44.99876,-104.65022,44.99881,-104.68965,44.99889,-104.70013,44.99891,-104.76078,44.99927,-104.79368,44.99947,-104.8036,44.9994,-104.81432,44.99934,-104.82147,44.99937,-104.85505,44.99949,-104.85827,44.9995,-104.87573,44.99944,-104.88472,44.99954,-104.89639,44.9
  8.             9964,-104.90797,44.99987,-105.0392,44.99982,-105.04153,44.99983,-105.08736,45.00008,-105.22677,45.00085,-105.23814,45.00091,-105.24677,45.0009,-105.26743,45.00087,-105.28338,45.00086,-105.28745,45.00086,-105.58763,45.0013,-105.67679,45.00158,-105.69398,45.00163,-105.69743,45.00168,-105.76009,45.00163,-105.89703,45.00167,-105.91964,45.00168,-105.92261,45.00071,-105.93306,44.99728,-106.00071,44.99665,-106.01679,44.99678,-106.03686,44.99694,-106.03743,44.99695,-106.04104,44.99697,-106.14208,44.99767,-106.26479,44.99747,-106.26777,44.99748,-106.68256,44.99759,-106.74519,44.99761,-106.80523,44.99755,-106.99052,44.99739,-107.00086,44.9972,-107.23051,45.00071,-107.29616,45.00082,-107.56905,45.00105,-107.64518,45.00082,-107.84908,45.00088,-107.8955,45.00079,-107.91039,45.0008,-107.96454,45.00082,-108.00116,45.00092,-108.06195,45.00107,-108.10195,45.00124,-108.10439,45.00125,-108.12393,45.0013,-108.22582,45.00116,-108.24507,45.00113,-108.24901,45.0015,-108.2496,45.00155,-108.26056,45.00174,-108.28895,45.00175,-108.35542,45.00143,-108.38775,45.00163,-108.40776,45.00157,-108.42841,45.00154,-108.47417,45.00136,-108.49482,45.00128,-108.49808,45.0013,-108.51808,45.00143,-108.53872,45.00156,-108.55872,45.00148,-108.57937,45.00144,-108.59418,45.0014,-108.61549,45.00124,-108.62066,45.00124,-108.64195,45.0012,-108.65678,45.00117,-108.68956,45.0013,-108.71616,45.00141,-108.74195,45.00151,-108.76195,45.0014,-108.83941,45.00093,-108.88065,45.00098,-108.9013,45.00101,-108.9213,45.00104,-108.94195,45.00107,-108.96195,45.0011,-108.96264,45.00109,-108.97275,45.00101,-108.98523,45.00091,-109.00135,45.00077,-109.04717,45.00113,-109.08266,45.00105,-109.08944,45.00129,-109.15558,45.00365,-109.17228,45.00411,-109.17557,45.0042,-109.18676,45.00404,-109.19099,45.0041,-109.19621,45.00407,-109.21686,45.00399,-109.21822,45.00399,-109.23294,45.00401,-109.25816,45.00398,-109.30854,45.00391,-109.32655,45.00388,-109.33813,45.00387,-109.40914,45.00377,-109.42631,45.00375,-109.46913,45.00368,-109.47168,45.
  9.             00369,-109.48914,45.00371,-109.55427,45.00383,-109.69427,45.00338,-109.7259,45.00311,-109.79687,45.00304,-109.85236,45.00286,-109.99882,45.00257,-110.13246,45.00215,-110.13302,45.00208,-110.20061,44.9942,-110.37171,44.99722,-110.40206,44.99206,-110.43024,44.99213,-110.58062,44.99252,-110.70718,44.99285,-110.7756,45.00185,-110.78234,45.00182,-110.80042,45.00174,-111.04203,44.99998,-111.053,44.99989,-111.05514,44.66688))  

Then copy the geometry in its entirety into a SQL statement and execute within SQL Developer:

  1. SELECT COUNT(*)  
  2.   FROM G_Place G
  3.   WHERE sdo_anyinteract(G.Geog4326,
  4.                         SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-111.05514,44.66688, -111.04873,44.47723, -111.04873,44.4772, -111.04816,44.24178, -111.04813,44.22693, -111.04792,44.13511, -111.0477,44.11022, -111.04702,44.03275, -111.04662,43.98854, -111.04662,43.98554, -111.04613,43.88206, -111.04549,43.72747, -111.04539,43.65012, -111.04528,43.56948, -111.04501,43.55532, -111.04491,43.55032, -111.04498,43.50614, -111.04494,43.50049, -111.04391,43.49977, -111.04362,43.31358, -111.04387,43.28166, -111.04454,43.19794, -111.04454,43.17014, -111.04454,43.1442, -111.04452,43.14106, -111.04442,43.08366, -111.04428,43.05187, -111.04414,43.01995, -111.04413,43.0182, -111.04446,42.9263, -111.04446,42.84705, -111.04453,42.84123, -111.04459,42.81796, -111.04465,42.78892, -111.04546,42.73518, -111.04591,42.70563, -111.04655,42.65152, -111.04683,42.61197, -111.04692,42.51373, -111.0475,42.51359, -111.04769,42.4469, -111.04693,42.43872, -111.04721,42.42599, -111.04675,42.29326, -111.0467,42.2769, -111.04647,42.21101, -111.04633,42.1701, -111.0465,42.14002, -111.04674,42.09921, -111.04626,42.01016, -111.04628,42.00142, -111.04628,42.00048, -111.04637,41.93655, -111.04647,41.86251, -111.04651,41.83344, -111.04655,41.80573, -111.04665,41.73257, -111.04631,41.57817, -111.04626,41.53873, -111.04613,41.45112, -111.04609,41.42299, -111.04607,41.40845, -111.04604,41.39439, -111.04602,41.37896, -111.04598,41.34945, -111.04596,41.33493, -111.04594,41.32086, -111.04585,41.25143, -111.04584,41.24261, -111.04559,40.99778, -110.81728,40.99775, -110.77522,40.99765, -110.64185,40.99733, -110.62235,40.99728, -110.60347,40.99723, -110.58357,40.99718, -110.58337,40.99718, -110.57851,40.99717, -110.57362,40.99716, -110.56511,40.99714, -110.54715,40.9971, -110.54621,40.9971, -110.52733,40.99705, -110.50847,40.99701, -110.49374,40.99697, -110.49277,40.99695, -110.48896,40.99693, -110.27641,40.99627, -110.27238,40.99634, -110.21799,40.99723, -110.19503,40.99756, -110.16077,40.99792, -110.05729,40.99876, -110.0485,40.99883, -110.00014,40.99923, -109.94201,40.99935, -109.8862,40.99942, -109.81077,40.99952, -109.72911,40.99957, -109.68892,40.99967, -109.66943,40.99972, -109.65546,40.99968, -109.6433,40.99964, -109.62624,40.99959, -109.59092,40.99948, -109.58846,40.99947, -109.50869,40.9997, -109.50504,40.99971, -109.47078,40.99981, -109.4685,40.99983, -109.44231,41.00003, -109.23518,41.00158, -109.21952,41.00169, -109.21873,41.00169, -109.1962,41.00154, -109.11826,41.00072, -109.10261,41.00056, -109.08112,41.00058, -109.04901,41.00003, -108.97291,41.00018, -108.93151,41.00013, -108.91203,41.0001, -108.76553,41.00019, -108.74603,41.00016, -108.65163,41.00052, -108.63214,41.00048, -108.56988,41.00029, -108.3781,40.99971, -108.3745,40.99973, -108.3118,41.00004, -108.2923,41.00014, -108.26303,41.00029, -108.2186,41.00066, -108.18085,41.00098, -108.00069,41.00249, -107.96598,41.00277, -107.9154,41.00294, -107.88805,41.00294, -107.88009,41.00288, -107.85211,41.00286, -107.83914,41.00285, -107.83263,41.00284, -107.8206,41.00283, -107.81314,41.00283, -107.7845,41.00283, -107.70779,41.00282, -107.67674,41.00281, -107.60489,41.0028, -107.52878,41.00256, -107.51358,41.00262, -107.50928,41.00262, -107.49471,41.00264, -107.45753,41.00266, -107.4137,41.00291, -107.39483,41.00303, -107.36745,41.00319, -107.34371,41.00332, -107.31812,41.00346, -107.30596,41.00341, -107.22985,41.00352, -107.13548,41.00365, -107.02586,41.00428, -107.00211,41.00442, -106.86391,41.0041, -106.59114,41.00346, -106.58201,41.00375, -106.57226,41.00377, -106.54365,41.00381, -106.48642,41.00328, -106.45597,41.00347, -106.44805,41.00352, -106.37926,41.00264, -106.35186,41.00246, -106.33178,41.00251, -106.3263,41.00253, -106.32574,41.00235, -106.32148,41.00098, -106.31714,40.99938, -106.20329,40.99889, -106.19669,40.99886, -106.19598,40.99886, -106.08701,40.99798, -106.06814,40.99783, -106.06032,40.99777, -106.02856,40.99766, -106.02379,40.99765, -105.99635,40.99845, -105.97196,40.99916, -105.93848,40.99994, -105.92629,41.00003, -105.9001,40.99986, -105.88063,40.99972, -105.69796,40.9998, -105.69003,40.9998, -105.6712,40.9998, -105.52073,40.99966, -105.47701,40.99953, -105.35509,40.99929, -105.28949,40.9992, -105.27631,40.9998, -105.11086,40.99932, -104.94246,40.99948, -104.93848,40.99948, -104.83409,40.99956, -104.83003,40.99957, -104.70135,41.00352, -104.68366,41.00407, -104.66476,41.00465, -104.63366,41.00561, -104.61342,41.00476, -104.60869,41.00456, -104.56796,41.00285, -104.56065,41.00284, -104.48755,41.00271, -104.4725,41.00268, -104.13991,41.0019, -104.05284,41.0017, -104.05258,41.02359, -104.05199,41.39493, -104.05212,41.56544, -104.05225,41.6975, -104.05223,41.70037, -104.05247,41.99803, -104.05247,42.00245, -104.05247,42.08791, -104.05248,42.6128, -104.05277,42.93345, -104.05265,43.00138, -104.05265,43.0028, -104.05342,43.0028, -104.05363,43.12841, -104.05305,43.1976, -104.05309,43.21216, -104.05326,43.29731, -104.05355,43.38927, -104.05365,43.42211, -104.0541,43.45354, -104.05397,43.46766, -104.05389,43.47716, -104.05408,43.48269, -104.05421,43.50365, -104.0546,43.56742, -104.05456,43.57415, -104.05462,43.64658, -104.0547,43.67133, -104.05484,43.71325, -104.05488,43.7251, -104.05492,43.7377, -104.05471,43.757, -104.0546,43.76687, -104.05466,43.8012, -104.05483,43.85089, -104.05484,43.853, -104.05466,43.87277, -104.0547,43.88735, -104.05479,43.91515, -104.05484,43.9293, -104.05506,43.99656, -104.05508,44.00134, -104.05507,44.01592, -104.05501,44.09574, -104.055,44.10211, -104.05499,44.11123, -104.05497,44.13676, -104.05496,44.14155, -104.05503,44.14407, -104.05505,44.15183, -104.05512,44.18148, -104.05584,44.47406, -104.05621,44.53922, -104.05639,44.5717, -104.05711,44.98175, -104.05713,44.99866, -104.08103,44.99845, -104.1004,44.99902, -104.1656,44.99881, -104.17958,44.99876, -104.20026,44.99899, -104.22305,44.99924, -104.29463,44.99945, -104.39809,44.99917, -104.50175,44.99878, -104.57722,44.99866, -104.5917,44.99864, -104.59815,44.9987, -104.62953,44.99876, -104.65022,44.99881, -104.68965,44.99889, -104.70013,44.99891, -104.76078,44.99927, -104.79368,44.99947, -104.8036,44.9994, -104.81432,44.99934, -104.82147,44.99937, -104.85505,44.99949, -104.85827,44.9995, -104.87573,44.99944, -104.88472,44.99954, -104.89639,44.99964, -104.90797,44.99987, -105.0392,44.99982, -105.04153,44.99983, -105.08736,45.00008, -105.22677,45.00085, -105.23814,45.00091, -105.24677,45.0009, -105.26743,45.00087, -105.28338,45.00086, -105.28745,45.00086, -105.58763,45.0013, -105.67679,45.00158, -105.69398,45.00163, -105.69743,45.00168, -105.76009,45.00163, -105.89703,45.00167, -105.91964,45.00168, -105.92261,45.00071, -105.93306,44.99728, -106.00071,44.99665, -106.01679,44.99678, -106.03686,44.99694, -106.03743,44.99695, -106.04104,44.99697, -106.14208,44.99767, -106.26479,44.99747, -106.26777,44.99748, -106.68256,44.99759, -106.74519,44.99761, -106.80523,44.99755, -106.99052,44.99739, -107.00086,44.9972, -107.23051,45.00071, -107.29616,45.00082, -107.56905,45.00105, -107.64518,45.00082, -107.84908,45.00088, -107.8955,45.00079, -107.91039,45.0008, -107.96454,45.00082, -108.00116,45.00092, -108.06195,45.00107, -108.10195,45.00124, -108.10439,45.00125, -108.12393,45.0013, -108.22582,45.00116, -108.24507,45.00113, -108.24901,45.0015, -108.2496,45.00155, -108.26056,45.00174, -108.28895,45.00175, -108.35542,45.00143, -108.38775,45.00163, -108.40776,45.00157, -108.42841,45.00154, -108.47417,45.00136, -108.49482,45.00128, -108.49808,45.0013, -108.51808,45.00143, -108.53872,45.00156, -108.55872,45.00148, -108.57937,45.00144, -108.59418,45.0014, -108.61549,45.00124, -108.62066,45.00124, -108.64195,45.0012, -108.65678,45.00117, -108.68956,45.0013, -108.71616,45.00141, -108.74195,45.00151, -108.76195,45.0014, -108.83941,45.00093, -108.88065,45.00098, -108.9013,45.00101, -108.9213,45.00104, -108.94195,45.00107, -108.96195,45.0011, -108.96264,45.00109, -108.97275,45.00101, -108.98523,45.00091, -109.00135,45.00077, -109.04717,45.00113, -109.08266,45.00105, -109.08944,45.00129, -109.15558,45.00365, -109.17228,45.00411, -109.17557,45.0042, -109.18676,45.00404, -109.19099,45.0041, -109.19621,45.00407, -109.21686,45.00399, -109.21822,45.00399, -109.23294,45.00401, -109.25816,45.00398, -109.30854,45.00391, -109.32655,45.00388, -109.33813,45.00387, -109.40914,45.00377, -109.42631,45.00375, -109.46913,45.00368, -109.47168,45.00369, -109.48914,45.00371, -109.55427,45.00383, -109.69427,45.00338, -109.7259,45.00311, -109.79687,45.00304, -109.85236,45.00286, -109.99882,45.00257, -110.13246,45.00215, -110.13302,45.00208, -110.20061,44.9942, -110.37171,44.99722, -110.40206,44.99206, -110.43024,44.99213, -110.58062,44.99252, -110.70718,44.99285, -110.7756,45.00185, -110.78234,45.00182, -110.80042,45.00174, -111.04203,44.99998, -111.053,44.99989, -111.05514,44.66688))
  5.                         ) = 'TRUE';
  6. COUNT(*)
  7. --------
  8.     1635

Notice how the statement execute correctly.

Now let us try the Kansas geometry:

  1. SELECT Sdo_Util.Getnumvertices(Geometry) AS numVertices,
  2.        codesys.linear.st_roundOrdinates(geometry,5,5,2,2) geometry
  3.   FROM Us_States
  4.  WHERE State = 'KS';
  5. NUMVERTICES GEOMETRY
  6. ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7.         514 SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-102.04855,40.00391,-102.04756,39.57374,-102.04755,39.56886,-102.04635,39.1339,-102.0461,39.04563,-102.04546,38.69533,-102.04538,38.61375,-102.04431,38.26762,-102.0443,38.2627,-102.04324,37.73841,-102.04305,37.64289,-102.04253,37.38824,-102.04227,37.25625,-102.04225,37.244,-102.04219,37.21269,-102.04217,37.19999,-102.04211,37.17051,-102.04208,37.156,-102.04198,37.11017,-102.04227,36.99223,-102.04175,36.99222,-102.02805,36.99249,-101.98809,36.99253,-101.91798,36.99286,-101.55524,36.99513,-101.54396,36.99515,-101.26794,36.9967,-101.06711,36.99783,-100.95359,36.99877,-100.94408,36.99883,-100.63371,36.99865,-100.10789,36.99834,-100.09022,36.99832,-100.00243,36.99849,-100.00089,36.9985,-99.53989,36.99975,-99.45157,36.99996,-99.40044,37.00008,-99.00054,37.00076,-98.54572,37.00022,-98.50035,37.00024,-98.34823,37.00032,-98.32906,37.00031,-98.11642,37.00026,-98.05378,37.00027,-97.89861,37.00031,-97.80196,37.00035,-97.74242,37.00032,-97.53543,37.00024,-97.46236,37.00021,-97.45967,37.0002,-97.41106,37.00007,-97.27104,36.99971,-97.19783,36.99952,-97.14678,37.0001,-97.14677,36.99939,-97.02897,36.9995,-96.9287,36.9996,-96.75315,36.99978,-96.54362,37.00078,-96.54139,37.00079,-96.52834,37.00079,-96.30413,37.00063,-96.28107,37.0006,-96.2459,37.00054,-96.23669,37.00074,-96.1168,37.00071,-96.0949,37.00028,-96.00037,37.00018,-95.97443,37.00031,-95.96521,37.00035,-95.96426,37.00035,-95.92773,37.00047,-95.80035,37.00042,-95.78843,37.00041,-95.77198,37.0004,-95.52364,37.00027,-95.52364,37.00093,-95.40672,37.00047,-95.40027,37.00053,-95.07227,36.99872,-95.03362,36.99859,-95.03309,36.9992,-94.85946,36.99972,-94.61897,36.99996,-94.61871,37.05848,-94.61748,37.33839,-94.61739,37.36412,-94.61728,37.38238,-94.61642,37.63641,-94.61636,37.65386,-94.61623,37.67361,-94.61481,38.03646,-94.61472,38.05964,-94.61483,38.11402,-94.61345,38.38924,-94.61244,38.4673,-94.61234,38.47584,-94.61015,38.73758,-94.60838,38.83414,-94.60816,38.84628,-94.60753,39.03988,-94.60751,39.04369,-94.60825,39.11467,-94.60829,39.12037,-94.60508,39.12481,-94.6029,39.12745,-94.60126,39.12968,-94.59749,39.13364,-94.59423,39.13898,-94.5938,39.14121,-94.59274,39.14531,-94.5931,39.14893,-94.59416,39.15123,-94.59598,39.15477,-94.59844,39.15791,-94.60203,39.15964,-94.60445,39.15995,-94.60674,39.15995,-94.61269,39.15998,-94.61808,39.15924,-94.62277,39.15794,-94.62769,39.15727,-94.63161,39.15598,-94.63912,39.15445,-94.64564,39.15465,-94.6506,39.15565,-94.65421,39.15741,-94.65844,39.15958,-94.66126,39.16239,-94.6629,39.16516,-94.66278,39.16743,-94.66148,39.16966,-94.66071,39.17281,-94.66116,39.17554,-94.66282,39.17786,-94.66915,39.18072,-94.67396,39.18245,-94.67733,39.18239,-94.68158,39.18253,-94.68993,39.18187,-94.69418,39.18019,-94.69932,39.17702,-94.70279,39.17455,-94.70865,39.17141,-94.71607,39.17042,-94.72501,39.16979,-94.73354,39.17006,-94.74068,39.17084,-94.74864,39.17232,-94.75507,39.17511,-94.76022,39.17811,-94.76537,39.18218,-94.76991,39.18611,-94.77395,39.19064,-94.77648,39.19558,-94.77991,39.19957,-94.78212,39.20281,-94.7851,39.20539,-94.78722,39.2075,-94.79112,39.208,-94.79709,39.20881,-94.80242,39.20825,-94.80954,39.20812,-94.8167,39.20935,-94.82501,39.21225,-94.83058,39.21589,-94.83649,39.22029,-94.83739,39.22438,-94.83416,39.23111,-94.83041,39.23463,-94.82834,39.24046,-94.82806,39.24858,-94.83233,39.25555,-94.83677,39.26065,-94.84231,39.26645,-94.8508,39.27173,-94.86399,39.27644,-94.875,39.2822,-94.88766,39.29073,-94.8966,39.30233,-94.90264,39.31249,-94.90604,39.32372,-94.90704,39.33032,-94.90755,39.33367,-94.90835,39.33955,-94.90853,39.34499,-94.90733,39.34982,-94.90597,39.35341,-94.904,39.35698,-94.90244,39.35871,-94.89843,39.36135,-94.89369,39.36432,-94.88983,39.36655,-94.88613,39.36916,-94.88383,39.37221,-94.88217,39.37676,-94.88319,39.38042,-94.88604,39.38486,-94.88932,39.38876,-94.8934,39.39153,-94.89819,39.39234,-94.90175,39.39182,-94.90769,39.39066,-94.91354,39.38842,-94.91801,39.38687,-94.92415,39.38504,-94.93069,39.38491,-94.93401,39.38662,-94.93494,39.3871,-94.93875,39.38882,-94.94344,39.39192,-94.94597,39.3964,-94.94667,39.39956,-94.9462,39.40319,-94.94619,39.40718,-94.94943,39.41084,-94.95416,39.41466,-94.95855,39.4161,-94.96296,39.41763,-94.96825,39.41914,-94.97124,39.4213,-94.97459,39.4234,-94.97739,39.42711,-94.98054,39.43199,-94.98188,39.4365,-94.98318,39.43965,-94.98495,39.4432,-94.98756,39.44545,-94.99128,39.44791,-94.99834,39.44993,-95.00656,39.45243,-95.02476,39.45838,-95.03461,39.46174,-95.03993,39.46515,-95.04333,39.46887,-95.04637,39.4742,-95.04836,39.48005,-95.04909,39.48367,-95.04929,39.48956,-95.05103,39.49494,-95.05322,39.49945,-95.05658,39.50371,-95.06185,39.50744,-95.06917,39.51182,-95.07706,39.51574,-95.08621,39.52145,-95.09483,39.52744,-95.10105,39.53281,-95.10458,39.53587,-95.11033,39.54498,-95.11488,39.55532,-95.11567,39.56074,-95.11529,39.56664,-95.11372,39.57014,-95.11172,39.57462,-95.10796,39.57859,-95.10365,39.5812,-95.10032,39.58192,-95.09496,39.58249,-95.09018,39.58213,-95.08517,39.58025,-95.08112,39.57914,-95.07452,39.57837,-95.06908,39.57981,-95.06209,39.58135,-95.05755,39.58357,-95.05391,39.58651,-95.05046,39.5902,-95.04822,39.59433,-95.04674,39.59734,-95.04606,39.60072,-95.04738,39.60432,-95.04862,39.60782,-95.05081,39.61197,-95.05239,39.61654,-95.05517,39.62185,-95.05505,39.62387,-95.05492,39.62614,-95.05312,39.63142,-95.04951,39.63914,-95.04237,39.64887,-95.03669,39.65714,-95.03103,39.66313,-95.02354,39.66973,-95.01429,39.67537,-95.00513,39.67773,-94.99861,39.67922,-94.99257,39.6793,-94.98788,39.68034,-94.98289,39.68175,-94.97784,39.68372,-94.97377,39.68697,-94.97173,39.6896,-94.96967,39.69498,-94.96941,39.70042,-94.96967,39.707,-94.96998,39.71607,-94.96989,39.7272,-94.96899,39.73352,-94.96752,39.73893,-94.96489,39.74202,-94.96126,39.74464,-94.95784,39.74578,-94.9519,39.74726,-94.94767,39.74598,-94.94275,39.74367,-94.93365,39.73854,-94.92205,39.73151,-94.91122,39.7278,-94.90232,39.72618,-94.89314,39.72572,-94.88793,39.72668,-94.88129,39.72913,-94.87545,39.73332,-94.86964,39.73841,-94.86615,39.74329,-94.86411,39.74822,-94.86385,39.7532,-94.8643,39.75801,-94.86568,39.76389,-94.8673,39.76782,-94.87132,39.77148,-94.87662,39.77308,-94.8814,39.77322,-94.8857,39.77107,-94.88918,39.76842,-94.89499,39.76499,-94.90097,39.76321,-94.9095,39.76223,-94.91891,39.76287,-94.92487,39.7663,-94.92836,39.76821,-94.93051,39.76944,-94.93364,39.77346,-94.93493,39.77616,-94.93565,39.77977,-94.93517,39.78341,-94.93346,39.78605,-94.93036,39.78823,-94.92728,39.78996,-94.92126,39.79069,-94.91707,39.79057,-94.91147,39.79067,-94.90563,39.79161,-94.90016,39.7927,-94.89411,39.79445,-94.89066,39.79515,-94.88831,39.79656,-94.88429,39.79916,-94.88169,39.80317,-94.88029,39.80721,-94.87862,39.81125,-94.87866,39.81578,-94.87935,39.82079,-94.87954,39.82216,-94.88088,39.82527,-94.88339,39.82885,-94.88626,39.83143,-94.88972,39.83425,-94.89264,39.83569,-94.89861,39.83632,-94.90762,39.83655,-94.91293,39.83764,-94.91766,39.83868,-94.92119,39.84014,-94.92649,39.84211,-94.93067,39.84518,-94.93515,39.84871,-94.93736,39.8524,-94.93942,39.85589,-94.94118,39.86022,-94.9398,39.86381,-94.93784,39.86693,-94.93514,39.87094,-94.93212,39.87357,-94.93015,39.87714,-94.92881,39.87983,-94.9286,39.8839,-94.92969,39.88692,-94.93301,39.89174,-94.93645,39.89502,-94.94449,39.89888,-94.95116,39.90088,-94.95834,39.90154,-94.9643,39.90216,-94.97093,39.90144,-94.97631,39.90007,-94.98243,39.89949,-94.98904,39.89922,-94.99489,39.8997,-94.99741,39.89991,-95.00393,39.90145,-95.01114,39.9012,-95.01846,39.89868,-95.02345,39.89323,-95.02669,39.88984,-95.0268,39.88637,-95.02726,39.88227,-95.0283,39.87772,-95.02938,39.87452,-95.03403,39.87035,-95.0416,39.86666,-95.04829,39.86457,-95.05956,39.86348,-95.07633,39.86314,-95.0867,39.86381,-95.09147,39.86439,-95.09799,39.86541,-95.10389,39.8679,-95.10892,39.86927,-95.11512,39.87138,-95.11926,39.87313,-95.12278,39.87431,-95.12774,39.87568,-95.13163,39.87727,-95.13628,39.88057,-95.13882,39.88361,-95.14077,39.88765,-95.14152,39.89172,-95.14165,39.89534,-95.14235,39.89805,-95.14303,39.90298,-95.14563,39.90569,-95.15111,39.90784,-95.15649,39.90728,-95.16147,39.90619,-95.16656,39.90389,-95.17189,39.90148,-95.17957,39.89907,-95.18639,39.89914,-95.19342,39.90059,-95.19878,39.90355,-95.20321,39.90583,-95.20633,39.90893,-95.20622,39.91146,-95.20488,39.9146,-95.20291,39.91817,-95.20076,39.92039,-95.20028,39.92358,-95.19981,39.92722,-95.20058,39.92991,-95.20163,39.93311,-95.20379,39.93711,-95.20634,39.94114,-95.21001,39.94287,-95.21665,39.94409,-95.22203,39.94352,-95.22923,39.94336,-95.23594,39.94399,-95.24071,39.94538,-95.2468,39.94797,-95.25296,39.95192,-95.25641,39.95586,-95.26047,39.95992,-95.26551,39.96615,-95.27197,39.97033,-95.2778,39.9736,-95.28572,39.9766,-95.29437,39.97952,-95.30022,39.9833,-95.30601,39.98798,-95.30922,39.99377,-95.308,39.99989,-95.33968,40.00063,-95.43938,40.00082,-95.4508,40.00065,-95.54552,40.00159,-95.7865,40.0015,-95.99983,40.00131,-96.01006,40.0013,-96.23811,40.0014,-96.4636,40.00262,-96.4672,40.00262,-96.80672,40.00217,-96.9184,40.00227,-97.00124,40.00249,-97.33384,40.0034,-97.36868,40.00354,-97.37115,40.00353,-97.81775,40.00354,-97.82154,40.00353,-97.93002,40.00311,-97.9939,40.00286,-98.02944,40.00291,-98.27549,40.00379,-98.50454,40.00361,-98.69966,40.00333,-98.72883,40.00322,-98.99962,40.00214,-99.06706,40.00225,-99.17972,40.00243,-99.19975,40.00253,-99.28298,40.00261,-99.62601,40.00281,-99.62759,40.00281,-99.99561,40.00291,-100.1784,40.00277,-100.19192,40.00276,-100.1946,40.00276,-100.6854,40.00325,-100.73985,40.00332,-100.75684,40.00333,-100.75966,40.00335,-101.0006,40.0037,-101.299,40.00422,-101.32242,40.00425,-101.36938,40.00433,-101.41068,40.00451,-101.54526,40.0043,-101.62334,40.00417,-101.77873,40.00393,-101.86121,40.00393,-102.04855,40.00391))

Copy into the SQL statement and execute:

  1. SELECT COUNT(*)  
  2.   FROM G_Place G
  3.   WHERE sdo_anyinteract(G.Geog4326,
  4.                         SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-102.04855,40.00391, -102.04756,39.57374, -102.04755,39.56886, -102.04635,39.1339, -102.0461,39.04563, -102.04546,38.69533, -102.04538,38.61375, -102.04431,38.26762, -102.0443,38.2627, -102.04324,37.73841, -102.04305,37.64289, -102.04253,37.38824, -102.04227,37.25625, -102.04225,37.244, -102.04219,37.21269, -102.04217,37.19999, -102.04211,37.17051, -102.04208,37.156, -102.04198,37.11017, -102.04227,36.99223, -102.04175,36.99222, -102.02805,36.99249, -101.98809,36.99253, -101.91798,36.99286, -101.55524,36.99513, -101.54396,36.99515, -101.26794,36.9967, -101.06711,36.99783, -100.95359,36.99877, -100.94408,36.99883, -100.63371,36.99865, -100.10789,36.99834, -100.09022,36.99832, -100.00243,36.99849, -100.00089,36.9985, -99.53989,36.99975, -99.45157,36.99996, -99.40044,37.00008, -99.00054,37.00076, -98.54572,37.00022, -98.50035,37.00024, -98.34823,37.00032, -98.32906,37.00031, -98.11642,37.00026, -98.05378,37.00027, -97.89861,37.00031, -97.80196,37.00035, -97.74242,37.00032, -97.53543,37.00024, -97.46236,37.00021, -97.45967,37.0002, -97.41106,37.00007, -97.27104,36.99971, -97.19783,36.99952, -97.14678,37.0001, -97.14677,36.99939, -97.02897,36.9995, -96.9287,36.9996, -96.75315,36.99978, -96.54362,37.00078, -96.54139,37.00079, -96.52834,37.00079, -96.30413,37.00063, -96.28107,37.0006, -96.2459,37.00054, -96.23669,37.00074, -96.1168,37.00071, -96.0949,37.00028, -96.00037,37.00018, -95.97443,37.00031, -95.96521,37.00035, -95.96426,37.00035, -95.92773,37.00047, -95.80035,37.00042, -95.78843,37.00041, -95.77198,37.0004, -95.52364,37.00027, -95.52364,37.00093, -95.40672,37.00047, -95.40027,37.00053, -95.07227,36.99872, -95.03362,36.99859, -95.03309,36.9992, -94.85946,36.99972, -94.61897,36.99996, -94.61871,37.05848, -94.61748,37.33839, -94.61739,37.36412, -94.61728,37.38238, -94.61642,37.63641, -94.61636,37.65386, -94.61623,37.67361, -94.61481,38.03646, -94.61472,38.05964, -94.61483,38.11402, -94.61345,38.38924, -94.61244,38.4673, -94.61234,38.47584, -94.61015,38.73758, -94.60838,38.83414, -94.60816,38.84628, -94.60753,39.03988, -94.60751,39.04369, -94.60825,39.11467, -94.60829,39.12037, -94.60508,39.12481, -94.6029,39.12745, -94.60126,39.12968, -94.59749,39.13364, -94.59423,39.13898, -94.5938,39.14121, -94.59274,39.14531, -94.5931,39.14893, -94.59416,39.15123, -94.59598,39.15477, -94.59844,39.15791, -94.60203,39.15964, -94.60445,39.15995, -94.60674,39.15995, -94.61269,39.15998, -94.61808,39.15924, -94.62277,39.15794, -94.62769,39.15727, -94.63161,39.15598, -94.63912,39.15445, -94.64564,39.15465, -94.6506,39.15565, -94.65421,39.15741, -94.65844,39.15958, -94.66126,39.16239, -94.6629,39.16516, -94.66278,39.16743, -94.66148,39.16966, -94.66071,39.17281, -94.66116,39.17554, -94.66282,39.17786, -94.66915,39.18072, -94.67396,39.18245, -94.67733,39.18239, -94.68158,39.18253, -94.68993,39.18187, -94.69418,39.18019, -94.69932,39.17702, -94.70279,39.17455, -94.70865,39.17141, -94.71607,39.17042, -94.72501,39.16979, -94.73354,39.17006, -94.74068,39.17084, -94.74864,39.17232, -94.75507,39.17511, -94.76022,39.17811, -94.76537,39.18218, -94.76991,39.18611, -94.77395,39.19064, -94.77648,39.19558, -94.77991,39.19957, -94.78212,39.20281, -94.7851,39.20539, -94.78722,39.2075, -94.79112,39.208, -94.79709,39.20881, -94.80242,39.20825, -94.80954,39.20812, -94.8167,39.20935, -94.82501,39.21225, -94.83058,39.21589, -94.83649,39.22029, -94.83739,39.22438, -94.83416,39.23111, -94.83041,39.23463, -94.82834,39.24046, -94.82806,39.24858, -94.83233,39.25555, -94.83677,39.26065, -94.84231,39.26645, -94.8508,39.27173, -94.86399,39.27644, -94.875,39.2822, -94.88766,39.29073, -94.8966,39.30233, -94.90264,39.31249, -94.90604,39.32372, -94.90704,39.33032, -94.90755,39.33367, -94.90835,39.33955, -94.90853,39.34499, -94.90733,39.34982, -94.90597,39.35341, -94.904,39.35698, -94.90244,39.35871, -94.89843,39.36135, -94.89369,39.36432, -94.88983,39.36655, -94.88613,39.36916, -94.88383,39.37221, -94.88217,39.37676, -94.88319,39.38042, -94.88604,39.38486, -94.88932,39.38876, -94.8934,39.39153, -94.89819,39.39234, -94.90175,39.39182, -94.90769,39.39066, -94.91354,39.38842, -94.91801,39.38687, -94.92415,39.38504, -94.93069,39.38491, -94.93401,39.38662, -94.93494,39.3871, -94.93875,39.38882, -94.94344,39.39192, -94.94597,39.3964, -94.94667,39.39956, -94.9462,39.40319, -94.94619,39.40718, -94.94943,39.41084, -94.95416,39.41466, -94.95855,39.4161, -94.96296,39.41763, -94.96825,39.41914, -94.97124,39.4213, -94.97459,39.4234, -94.97739,39.42711, -94.98054,39.43199, -94.98188,39.4365, -94.98318,39.43965, -94.98495,39.4432, -94.98756,39.44545, -94.99128,39.44791, -94.99834,39.44993, -95.00656,39.45243, -95.02476,39.45838, -95.03461,39.46174, -95.03993,39.46515, -95.04333,39.46887, -95.04637,39.4742, -95.04836,39.48005, -95.04909,39.48367, -95.04929,39.48956, -95.05103,39.49494, -95.05322,39.49945, -95.05658,39.50371, -95.06185,39.50744, -95.06917,39.51182, -95.07706,39.51574, -95.08621,39.52145, -95.09483,39.52744, -95.10105,39.53281, -95.10458,39.53587, -95.11033,39.54498, -95.11488,39.55532, -95.11567,39.56074, -95.11529,39.56664, -95.11372,39.57014, -95.11172,39.57462, -95.10796,39.57859, -95.10365,39.5812, -95.10032,39.58192, -95.09496,39.58249, -95.09018,39.58213, -95.08517,39.58025, -95.08112,39.57914, -95.07452,39.57837, -95.06908,39.57981, -95.06209,39.58135, -95.05755,39.58357, -95.05391,39.58651, -95.05046,39.5902, -95.04822,39.59433, -95.04674,39.59734, -95.04606,39.60072, -95.04738,39.60432, -95.04862,39.60782, -95.05081,39.61197, -95.05239,39.61654, -95.05517,39.62185, -95.05505,39.62387, -95.05492,39.62614, -95.05312,39.63142, -95.04951,39.63914, -95.04237,39.64887, -95.03669,39.65714, -95.03103,39.66313, -95.02354,39.66973, -95.01429,39.67537, -95.00513,39.67773, -94.99861,39.67922, -94.99257,39.6793, -94.98788,39.68034, -94.98289,39.68175, -94.97784,39.68372, -94.97377,39.68697, -94.97173,39.6896, -94.96967,39.69498, -94.96941,39.70042, -94.96967,39.707, -94.96998,39.71607, -94.96989,39.7272, -94.96899,39.73352, -94.96752,39.73893, -94.96489,39.74202, -94.96126,39.74464, -94.95784,39.74578, -94.9519,39.74726, -94.94767,39.74598, -94.94275,39.74367, -94.93365,39.73854, -94.92205,39.73151, -94.91122,39.7278, -94.90232,39.72618, -94.89314,39.72572, -94.88793,39.72668, -94.88129,39.72913, -94.87545,39.73332, -94.86964,39.73841, -94.86615,39.74329, -94.86411,39.74822, -94.86385,39.7532, -94.8643,39.75801, -94.86568,39.76389, -94.8673,39.76782, -94.87132,39.77148, -94.87662,39.77308, -94.8814,39.77322, -94.8857,39.77107, -94.88918,39.76842, -94.89499,39.76499, -94.90097,39.76321, -94.9095,39.76223, -94.91891,39.76287, -94.92487,39.7663, -94.92836,39.76821, -94.93051,39.76944, -94.93364,39.77346, -94.93493,39.77616, -94.93565,39.77977, -94.93517,39.78341, -94.93346,39.78605, -94.93036,39.78823, -94.92728,39.78996, -94.92126,39.79069, -94.91707,39.79057, -94.91147,39.79067, -94.90563,39.79161, -94.90016,39.7927, -94.89411,39.79445, -94.89066,39.79515, -94.88831,39.79656, -94.88429,39.79916, -94.88169,39.80317, -94.88029,39.80721, -94.87862,39.81125, -94.87866,39.81578, -94.87935,39.82079, -94.87954,39.82216, -94.88088,39.82527, -94.88339,39.82885, -94.88626,39.83143, -94.88972,39.83425, -94.89264,39.83569, -94.89861,39.83632, -94.90762,39.83655, -94.91293,39.83764, -94.91766,39.83868, -94.92119,39.84014, -94.92649,39.84211, -94.93067,39.84518, -94.93515,39.84871, -94.93736,39.8524, -94.93942,39.85589, -94.94118,39.86022, -94.9398,39.86381, -94.93784,39.86693, -94.93514,39.87094, -94.93212,39.87357, -94.93015,39.87714, -94.92881,39.87983, -94.9286,39.8839, -94.92969,39.88692, -94.93301,39.89174, -94.93645,39.89502, -94.94449,39.89888, -94.95116,39.90088, -94.95834,39.90154, -94.9643,39.90216, -94.97093,39.90144, -94.97631,39.90007, -94.98243,39.89949, -94.98904,39.89922, -94.99489,39.8997, -94.99741,39.89991, -95.00393,39.90145, -95.01114,39.9012, -95.01846,39.89868, -95.02345,39.89323, -95.02669,39.88984, -95.0268,39.88637, -95.02726,39.88227, -95.0283,39.87772, -95.02938,39.87452, -95.03403,39.87035, -95.0416,39.86666, -95.04829,39.86457, -95.05956,39.86348, -95.07633,39.86314, -95.0867,39.86381, -95.09147,39.86439, -95.09799,39.86541, -95.10389,39.8679, -95.10892,39.86927, -95.11512,39.87138, -95.11926,39.87313, -95.12278,39.87431, -95.12774,39.87568, -95.13163,39.87727, -95.13628,39.88057, -95.13882,39.88361, -95.14077,39.88765, -95.14152,39.89172, -95.14165,39.89534, -95.14235,39.89805, -95.14303,39.90298, -95.14563,39.90569, -95.15111,39.90784, -95.15649,39.90728, -95.16147,39.90619, -95.16656,39.90389, -95.17189,39.90148, -95.17957,39.89907, -95.18639,39.89914, -95.19342,39.90059, -95.19878,39.90355, -95.20321,39.90583, -95.20633,39.90893, -95.20622,39.91146, -95.20488,39.9146, -95.20291,39.91817, -95.20076,39.92039, -95.20028,39.92358, -95.19981,39.92722, -95.20058,39.92991, -95.20163,39.93311, -95.20379,39.93711, -95.20634,39.94114, -95.21001,39.94287, -95.21665,39.94409, -95.22203,39.94352, -95.22923,39.94336, -95.23594,39.94399, -95.24071,39.94538, -95.2468,39.94797, -95.25296,39.95192, -95.25641,39.95586, -95.26047,39.95992, -95.26551,39.96615, -95.27197,39.97033, -95.2778,39.9736, -95.28572,39.9766, -95.29437,39.97952, -95.30022,39.9833, -95.30601,39.98798, -95.30922,39.99377, -95.308,39.99989, -95.33968,40.00063, -95.43938,40.00082, -95.4508,40.00065, -95.54552,40.00159, -95.7865,40.0015, -95.99983,40.00131, -96.01006,40.0013, -96.23811,40.0014, -96.4636,40.00262, -96.4672,40.00262, -96.80672,40.00217, -96.9184,40.00227, -97.00124,40.00249, -97.33384,40.0034, -97.36868,40.00354, -97.37115,40.00353, -97.81775,40.00354, -97.82154,40.00353, -97.93002,40.00311, -97.9939,40.00286, -98.02944,40.00291, -98.27549,40.00379, -98.50454,40.00361, -98.69966,40.00333, -98.72883,40.00322, -98.99962,40.00214, -99.06706,40.00225, -99.17972,40.00243, -99.19975,40.00253, -99.28298,40.00261, -99.62601,40.00281, -99.62759,40.00281, -99.99561,40.00291, -100.1784,40.00277, -100.19192,40.00276, -100.1946,40.00276, -100.6854,40.00325, -100.73985,40.00332, -100.75684,40.00333, -100.75966,40.00335, -101.0006,40.0037, -101.299,40.00422, -101.32242,40.00425, -101.36938,40.00433, -101.41068,40.00451, -101.54526,40.0043, -101.62334,40.00417, -101.77873,40.00393, -101.86121,40.00393, -102.04855,40.00391))
  5.                         ) = 'TRUE';
  6. Error at Command Line:16 COLUMN:56
  7. Error report:
  8. SQL Error: ORA-00939: too many arguments FOR FUNCTION
  9. 00939. 00000 -  "too many arguments for function"
  10. *Cause:    
  11. *Action:0

Note that we have hit the SQL level restriction previously described that mainly displays itself when using SQL Developer or SQLPlus.

OK, so how do we fix this?

Two ways:

1. Use PLSQL block.

  1. SET serveroutput ON SIZE unlimited
  2. DECLARE
  3.    v_geom  sdo_geometry; -- Note, you cannot set the value of the variable here as it will produce the same result.
  4.    v_count NUMBER;  
  5. BEGIN
  6.   -- fill geometry
  7.   v_geom := SDO_GEOMETRY(2003,4326,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-102.04855,40.00391, -102.04756,39.57374, -102.04755,39.56886, -102.04635,39.1339, -102.0461,39.04563, -102.04546,38.69533, -102.04538,38.61375, -102.04431,38.26762, -102.0443,38.2627, -102.04324,37.73841, -102.04305,37.64289, -102.04253,37.38824, -102.04227,37.25625, -102.04225,37.244, -102.04219,37.21269, -102.04217,37.19999, -102.04211,37.17051, -102.04208,37.156, -102.04198,37.11017, -102.04227,36.99223, -102.04175,36.99222, -102.02805,36.99249, -101.98809,36.99253, -101.91798,36.99286, -101.55524,36.99513, -101.54396,36.99515, -101.26794,36.9967, -101.06711,36.99783, -100.95359,36.99877, -100.94408,36.99883, -100.63371,36.99865, -100.10789,36.99834, -100.09022,36.99832, -100.00243,36.99849, -100.00089,36.9985, -99.53989,36.99975, -99.45157,36.99996, -99.40044,37.00008, -99.00054,37.00076, -98.54572,37.00022, -98.50035,37.00024, -98.34823,37.00032, -98.32906,37.00031, -98.11642,37.00026, -98.05378,37.00027, -97.89861,37.00031, -97.80196,37.00035, -97.74242,37.00032, -97.53543,37.00024, -97.46236,37.00021, -97.45967,37.0002, -97.41106,37.00007, -97.27104,36.99971, -97.19783,36.99952, -97.14678,37.0001, -97.14677,36.99939, -97.02897,36.9995, -96.9287,36.9996, -96.75315,36.99978, -96.54362,37.00078, -96.54139,37.00079, -96.52834,37.00079, -96.30413,37.00063, -96.28107,37.0006, -96.2459,37.00054, -96.23669,37.00074, -96.1168,37.00071, -96.0949,37.00028, -96.00037,37.00018, -95.97443,37.00031, -95.96521,37.00035, -95.96426,37.00035, -95.92773,37.00047, -95.80035,37.00042, -95.78843,37.00041, -95.77198,37.0004, -95.52364,37.00027, -95.52364,37.00093, -95.40672,37.00047, -95.40027,37.00053, -95.07227,36.99872, -95.03362,36.99859, -95.03309,36.9992, -94.85946,36.99972, -94.61897,36.99996, -94.61871,37.05848, -94.61748,37.33839, -94.61739,37.36412, -94.61728,37.38238, -94.61642,37.63641, -94.61636,37.65386, -94.61623,37.67361, -94.61481,38.03646, -94.61472,38.05964, -94.61483,38.11402, -94.61345,38.38924, -94.61244,38.4673, -94.61234,38.47584, -94.61015,38.73758, -94.60838,38.83414, -94.60816,38.84628, -94.60753,39.03988, -94.60751,39.04369, -94.60825,39.11467, -94.60829,39.12037, -94.60508,39.12481, -94.6029,39.12745, -94.60126,39.12968, -94.59749,39.13364, -94.59423,39.13898, -94.5938,39.14121, -94.59274,39.14531, -94.5931,39.14893, -94.59416,39.15123, -94.59598,39.15477, -94.59844,39.15791, -94.60203,39.15964, -94.60445,39.15995, -94.60674,39.15995, -94.61269,39.15998, -94.61808,39.15924, -94.62277,39.15794, -94.62769,39.15727, -94.63161,39.15598, -94.63912,39.15445, -94.64564,39.15465, -94.6506,39.15565, -94.65421,39.15741, -94.65844,39.15958, -94.66126,39.16239, -94.6629,39.16516, -94.66278,39.16743, -94.66148,39.16966, -94.66071,39.17281, -94.66116,39.17554, -94.66282,39.17786, -94.66915,39.18072, -94.67396,39.18245, -94.67733,39.18239, -94.68158,39.18253, -94.68993,39.18187, -94.69418,39.18019, -94.69932,39.17702, -94.70279,39.17455, -94.70865,39.17141, -94.71607,39.17042, -94.72501,39.16979, -94.73354,39.17006, -94.74068,39.17084, -94.74864,39.17232, -94.75507,39.17511, -94.76022,39.17811, -94.76537,39.18218, -94.76991,39.18611, -94.77395,39.19064, -94.77648,39.19558, -94.77991,39.19957, -94.78212,39.20281, -94.7851,39.20539, -94.78722,39.2075, -94.79112,39.208, -94.79709,39.20881, -94.80242,39.20825, -94.80954,39.20812, -94.8167,39.20935, -94.82501,39.21225, -94.83058,39.21589, -94.83649,39.22029, -94.83739,39.22438, -94.83416,39.23111, -94.83041,39.23463, -94.82834,39.24046, -94.82806,39.24858, -94.83233,39.25555, -94.83677,39.26065, -94.84231,39.26645, -94.8508,39.27173, -94.86399,39.27644, -94.875,39.2822, -94.88766,39.29073, -94.8966,39.30233, -94.90264,39.31249, -94.90604,39.32372, -94.90704,39.33032, -94.90755,39.33367, -94.90835,39.33955, -94.90853,39.34499, -94.90733,39.34982, -94.90597,39.35341, -94.904,39.35698, -94.90244,39.35871, -94.89843,39.36135, -94.89369,39.36432, -94.88983,39.36655, -94.88613,39.36916, -94.88383,39.37221, -94.88217,39.37676, -94.88319,39.38042, -94.88604,39.38486, -94.88932,39.38876, -94.8934,39.39153, -94.89819,39.39234, -94.90175,39.39182, -94.90769,39.39066, -94.91354,39.38842, -94.91801,39.38687, -94.92415,39.38504, -94.93069,39.38491, -94.93401,39.38662, -94.93494,39.3871, -94.93875,39.38882, -94.94344,39.39192, -94.94597,39.3964, -94.94667,39.39956, -94.9462,39.40319, -94.94619,39.40718, -94.94943,39.41084, -94.95416,39.41466, -94.95855,39.4161, -94.96296,39.41763, -94.96825,39.41914, -94.97124,39.4213, -94.97459,39.4234, -94.97739,39.42711, -94.98054,39.43199, -94.98188,39.4365, -94.98318,39.43965, -94.98495,39.4432, -94.98756,39.44545, -94.99128,39.44791, -94.99834,39.44993, -95.00656,39.45243, -95.02476,39.45838, -95.03461,39.46174, -95.03993,39.46515, -95.04333,39.46887, -95.04637,39.4742, -95.04836,39.48005, -95.04909,39.48367, -95.04929,39.48956, -95.05103,39.49494, -95.05322,39.49945, -95.05658,39.50371, -95.06185,39.50744, -95.06917,39.51182, -95.07706,39.51574, -95.08621,39.52145, -95.09483,39.52744, -95.10105,39.53281, -95.10458,39.53587, -95.11033,39.54498, -95.11488,39.55532, -95.11567,39.56074, -95.11529,39.56664, -95.11372,39.57014, -95.11172,39.57462, -95.10796,39.57859, -95.10365,39.5812, -95.10032,39.58192, -95.09496,39.58249, -95.09018,39.58213, -95.08517,39.58025, -95.08112,39.57914, -95.07452,39.57837, -95.06908,39.57981, -95.06209,39.58135, -95.05755,39.58357, -95.05391,39.58651, -95.05046,39.5902, -95.04822,39.59433, -95.04674,39.59734, -95.04606,39.60072, -95.04738,39.60432, -95.04862,39.60782, -95.05081,39.61197, -95.05239,39.61654, -95.05517,39.62185, -95.05505,39.62387, -95.05492,39.62614, -95.05312,39.63142, -95.04951,39.63914, -95.04237,39.64887, -95.03669,39.65714, -95.03103,39.66313, -95.02354,39.66973, -95.01429,39.67537, -95.00513,39.67773, -94.99861,39.67922, -94.99257,39.6793, -94.98788,39.68034, -94.98289,39.68175, -94.97784,39.68372, -94.97377,39.68697, -94.97173,39.6896, -94.96967,39.69498, -94.96941,39.70042, -94.96967,39.707, -94.96998,39.71607, -94.96989,39.7272, -94.96899,39.73352, -94.96752,39.73893, -94.96489,39.74202, -94.96126,39.74464, -94.95784,39.74578, -94.9519,39.74726, -94.94767,39.74598, -94.94275,39.74367, -94.93365,39.73854, -94.92205,39.73151, -94.91122,39.7278, -94.90232,39.72618, -94.89314,39.72572, -94.88793,39.72668, -94.88129,39.72913, -94.87545,39.73332, -94.86964,39.73841, -94.86615,39.74329, -94.86411,39.74822, -94.86385,39.7532, -94.8643,39.75801, -94.86568,39.76389, -94.8673,39.76782, -94.87132,39.77148, -94.87662,39.77308, -94.8814,39.77322, -94.8857,39.77107, -94.88918,39.76842, -94.89499,39.76499, -94.90097,39.76321, -94.9095,39.76223, -94.91891,39.76287, -94.92487,39.7663, -94.92836,39.76821, -94.93051,39.76944, -94.93364,39.77346, -94.93493,39.77616, -94.93565,39.77977, -94.93517,39.78341, -94.93346,39.78605, -94.93036,39.78823, -94.92728,39.78996, -94.92126,39.79069, -94.91707,39.79057, -94.91147,39.79067, -94.90563,39.79161, -94.90016,39.7927, -94.89411,39.79445, -94.89066,39.79515, -94.88831,39.79656, -94.88429,39.79916, -94.88169,39.80317, -94.88029,39.80721, -94.87862,39.81125, -94.87866,39.81578, -94.87935,39.82079, -94.87954,39.82216, -94.88088,39.82527, -94.88339,39.82885, -94.88626,39.83143, -94.88972,39.83425, -94.89264,39.83569, -94.89861,39.83632, -94.90762,39.83655, -94.91293,39.83764, -94.91766,39.83868, -94.92119,39.84014, -94.92649,39.84211, -94.93067,39.84518, -94.93515,39.84871, -94.93736,39.8524, -94.93942,39.85589, -94.94118,39.86022, -94.9398,39.86381, -94.93784,39.86693, -94.93514,39.87094, -94.93212,39.87357, -94.93015,39.87714, -94.92881,39.87983, -94.9286,39.8839, -94.92969,39.88692, -94.93301,39.89174, -94.93645,39.89502, -94.94449,39.89888, -94.95116,39.90088, -94.95834,39.90154, -94.9643,39.90216, -94.97093,39.90144, -94.97631,39.90007, -94.98243,39.89949, -94.98904,39.89922, -94.99489,39.8997, -94.99741,39.89991, -95.00393,39.90145, -95.01114,39.9012, -95.01846,39.89868, -95.02345,39.89323, -95.02669,39.88984, -95.0268,39.88637, -95.02726,39.88227, -95.0283,39.87772, -95.02938,39.87452, -95.03403,39.87035, -95.0416,39.86666, -95.04829,39.86457, -95.05956,39.86348, -95.07633,39.86314, -95.0867,39.86381, -95.09147,39.86439, -95.09799,39.86541, -95.10389,39.8679, -95.10892,39.86927, -95.11512,39.87138, -95.11926,39.87313, -95.12278,39.87431, -95.12774,39.87568, -95.13163,39.87727, -95.13628,39.88057, -95.13882,39.88361, -95.14077,39.88765, -95.14152,39.89172, -95.14165,39.89534, -95.14235,39.89805, -95.14303,39.90298, -95.14563,39.90569, -95.15111,39.90784, -95.15649,39.90728, -95.16147,39.90619, -95.16656,39.90389, -95.17189,39.90148, -95.17957,39.89907, -95.18639,39.89914, -95.19342,39.90059, -95.19878,39.90355, -95.20321,39.90583, -95.20633,39.90893, -95.20622,39.91146, -95.20488,39.9146, -95.20291,39.91817, -95.20076,39.92039, -95.20028,39.92358, -95.19981,39.92722, -95.20058,39.92991, -95.20163,39.93311, -95.20379,39.93711, -95.20634,39.94114, -95.21001,39.94287, -95.21665,39.94409, -95.22203,39.94352, -95.22923,39.94336, -95.23594,39.94399, -95.24071,39.94538, -95.2468,39.94797, -95.25296,39.95192, -95.25641,39.95586, -95.26047,39.95992, -95.26551,39.96615, -95.27197,39.97033, -95.2778,39.9736, -95.28572,39.9766, -95.29437,39.97952, -95.30022,39.9833, -95.30601,39.98798, -95.30922,39.99377, -95.308,39.99989, -95.33968,40.00063, -95.43938,40.00082, -95.4508,40.00065, -95.54552,40.00159, -95.7865,40.0015, -95.99983,40.00131, -96.01006,40.0013, -96.23811,40.0014, -96.4636,40.00262, -96.4672,40.00262, -96.80672,40.00217, -96.9184,40.00227, -97.00124,40.00249, -97.33384,40.0034, -97.36868,40.00354, -97.37115,40.00353, -97.81775,40.00354, -97.82154,40.00353, -97.93002,40.00311, -97.9939,40.00286, -98.02944,40.00291, -98.27549,40.00379, -98.50454,40.00361, -98.69966,40.00333, -98.72883,40.00322, -98.99962,40.00214, -99.06706,40.00225, -99.17972,40.00243, -99.19975,40.00253, -99.28298,40.00261, -99.62601,40.00281, -99.62759,40.00281, -99.99561,40.00291, -100.1784,40.00277, -100.19192,40.00276, -100.1946,40.00276, -100.6854,40.00325, -100.73985,40.00332, -100.75684,40.00333, -100.75966,40.00335, -101.0006,40.0037, -101.299,40.00422, -101.32242,40.00425, -101.36938,40.00433, -101.41068,40.00451, -101.54526,40.0043, -101.62334,40.00417, -101.77873,40.00393, -101.86121,40.00393, -102.04855,40.00391));
  8.   SELECT COUNT(*)  
  9.     INTO v_count
  10.    FROM G_Place G
  11.   WHERE sdo_anyinteract(G.Geog4326,v_geom) = 'TRUE';
  12.   dbms_output.put_line(v_count);
  13. END;
  14. /
  15. SHOW errors
  16. anonymous block completed
  17. 9886

2. Or we can use a CLOB, assign an WKTEXT POLYGON object to it and then use the SDO_GEOMETRY clob constructor:

  1. SET serveroutput ON SIZE unlimited
  2. DECLARE
  3.    -- Note we can construct the sdo_geometry at variable declaration time as the number of parameters to the constructor is 2 (not 514 514+1)
  4.    v_geom  sdo_geometry := sdo_geometry('POLYGON((-102.04855 40.00391,-102.04756 39.57374,-102.04755 39.56886,-102.04635 39.1339,-102.0461 39.04563,-102.04546 38.69533,-102.04538 38.61375,-102.04431 38.26762,-102.0443 38.2627,-102.04324 37.73841,-102.04305 37.64289,-102.04253 37.38824,-102.04227 37.25625,-102.04225 37.244,-102.04219 37.21269,-102.04217 37.19999,-102.04211 37.17051,-102.04208 37.156,-102.04198 37.11017,-102.04227 36.99223,-102.04175 36.99222,-102.02805 36.99249,-101.98809 36.99253,-101.91798 36.99286,-101.55524 36.99513,-101.54396 36.99515,-101.26794 36.9967,-101.06711 36.99783,-100.95359 36.99877,-100.94408 36.99883,-100.63371 36.99865,-100.10789 36.99834,-100.09022 36.99832,-100.00243 36.99849,-100.00089 36.9985,-99.53989 36.99975,-99.45157 36.99996,-99.40044 37.00008,-99.00054 37.00076,-98.54572 37.00022,-98.50035 37.00024,-98.34823 37.00032,-98.32906 37.00031,-98.11642 37.00026,-98.05378 37.00027,-97.89861 37.00031,-97.80196 37.00035,-97.74242 37.00032,-97.53543 37.00024,-97.46236 37.00021,-97.45967 37.0002,-97.41106 37.00007,-97.27104 36.99971,-97.19783 36.99952,-97.14678 37.0001,-97.14677 36.99939,-97.02897 36.9995,-96.9287 36.9996,-96.75315 36.99978,-96.54362 37.00078,-96.54139 37.00079,-96.52834 37.00079,-96.30413 37.00063,-96.28107 37.0006,-96.2459 37.00054,-96.23669 37.00074,-96.1168 37.00071,-96.0949 37.00028,-96.00037 37.00018,-95.97443 37.00031,-95.96521 37.00035,-95.96426 37.00035,-95.92773 37.00047,-95.80035 37.00042,-95.78843 37.00041,-95.77198 37.0004,-95.52364 37.00027,-95.52364 37.00093,-95.40672 37.00047,-95.40027 37.00053,-95.07227 36.99872,-95.03362 36.99859,-95.03309 36.9992,-94.85946 36.99972,-94.61897 36.99996,-94.61871 37.05848,-94.61748 37.33839,-94.61739 37.36412,-94.61728 37.38238,-94.61642 37.63641,-94.61636 37.65386,-94.61623 37.67361,-94.61481 38.03646,-94.61472 38.05964,-94.61483 38.11402,-94.61345 38.38924,-94.61244 38.4673,-94.61234 38.47584,-94.61015 38.73758,-94.60838 38.83414,-94.60816 38.84628,-94.60753 39.03988,-94.60751 39.04369,-94.60825 39.11467,-94.60829 39.12037,-94.60508 39.12481,-94.6029 39.12745,-94.60126 39.12968,-94.59749 39.13364,-94.59423 39.13898,-94.5938 39.14121,-94.59274 39.14531,-94.5931 39.14893,-94.59416 39.15123,-94.59598 39.15477,-94.59844 39.15791,-94.60203 39.15964,-94.60445 39.15995,-94.60674 39.15995,-94.61269 39.15998,-94.61808 39.15924,-94.62277 39.15794,-94.62769 39.15727,-94.63161 39.15598,-94.63912 39.15445,-94.64564 39.15465,-94.6506 39.15565,-94.65421 39.15741,-94.65844 39.15958,-94.66126 39.16239,-94.6629 39.16516,-94.66278 39.16743,-94.66148 39.16966,-94.66071 39.17281,-94.66116 39.17554,-94.66282 39.17786,-94.66915 39.18072,-94.67396 39.18245,-94.67733 39.18239,-94.68158 39.18253,-94.68993 39.18187,-94.69418 39.18019,-94.69932 39.17702,-94.70279 39.17455,-94.70865 39.17141,-94.71607 39.17042,-94.72501 39.16979,-94.73354 39.17006,-94.74068 39.17084,-94.74864 39.17232,-94.75507 39.17511,-94.76022 39.17811,-94.76537 39.18218,-94.76991 39.18611,-94.77395 39.19064,-94.77648 39.19558,-94.77991 39.19957,-94.78212 39.20281,-94.7851 39.20539,-94.78722 39.2075,-94.79112 39.208,-94.79709 39.20881,-94.80242 39.20825,-94.80954 39.20812,-94.8167 39.20935,-94.82501 39.21225,-94.83058 39.21589,-94.83649 39.22029,-94.83739 39.22438,-94.83416 39.23111,-94.83041 39.23463,-94.82834 39.24046,-94.82806 39.24858,-94.83233 39.25555,-94.83677 39.26065,-94.84231 39.26645,-94.8508 39.27173,-94.86399 39.27644,-94.875 39.2822,-94.88766 39.29073,-94.8966 39.30233,-94.90264 39.31249,-94.90604 39.32372,-94.90704 39.33032,-94.90755 39.33367,-94.90835 39.33955,-94.90853 39.34499,-94.90733 39.34982,-94.90597 39.35341,-94.904 39.35698,-94.90244 39.35871,-94.89843 39.36135,-94.89369 39.36432,-94.88983 39.36655,-94.88613 39.36916,-94.88383 39.37221,-94.88217 39.37676,-94.88319 39.38042,-94.88604 39.38486,-94.88932 39.38876,-94.8934 39.39153,-94.89819 39.39234,-94.90175 39.39182,-94.90769 39.39066,-94.91354 39.38842,-94.91801 39.38687,-94.92415 39.38504,-94.93069 39.38491,-94.93401 39.38662,-94.93494 39.3871,-94.93875 39.38882,-94.94344 39.39192,-94.94597 39.3964,-94.94667 39.39956,-94.9462 39.40319,-94.94619 39.40718,-94.94943 39.41084,-94.95416 39.41466,-94.95855 39.4161,-94.96296 39.41763,-94.96825 39.41914,-94.97124 39.4213,-94.97459 39.4234,-94.97739 39.42711,-94.98054 39.43199,-94.98188 39.4365,-94.98318 39.43965,-94.98495 39.4432,-94.98756 39.44545,-94.99128 39.44791,-94.99834 39.44993,-95.00656 39.45243,-95.02476 39.45838,-95.03461 39.46174,-95.03993 39.46515,-95.04333 39.46887,-95.04637 39.4742,-95.04836 39.48005,-95.04909 39.48367,-95.04929 39.48956,-95.05103 39.49494,-95.05322 39.49945,-95.05658 39.50371,-95.06185 39.50744,-95.06917 39.51182,-95.07706 39.51574,-95.08621 39.52145,-95.09483 39.52744,-95.10105 39.53281,-95.10458 39.53587,-95.11033 39.54498,-95.11488 39.55532,-95.11567 39.56074,-95.11529 39.56664,-95.11372 39.57014,-95.11172 39.57462,-95.10796 39.57859,-95.10365 39.5812,-95.10032 39.58192,-95.09496 39.58249,-95.09018 39.58213,-95.08517 39.58025,-95.08112 39.57914,-95.07452 39.57837,-95.06908 39.57981,-95.06209 39.58135,-95.05755 39.58357,-95.05391 39.58651,-95.05046 39.5902,-95.04822 39.59433,-95.04674 39.59734,-95.04606 39.60072,-95.04738 39.60432,-95.04862 39.60782,-95.05081 39.61197,-95.05239 39.61654,-95.05517 39.62185,-95.05505 39.62387,-95.05492 39.62614,-95.05312 39.63142,-95.04951 39.63914,-95.04237 39.64887,-95.03669 39.65714,-95.03103 39.66313,-95.02354 39.66973,-95.01429 39.67537,-95.00513 39.67773,-94.99861 39.67922,-94.99257 39.6793,-94.98788 39.68034,-94.98289 39.68175,-94.97784 39.68372,-94.97377 39.68697,-94.97173 39.6896,-94.96967 39.69498,-94.96941 39.70042,-94.96967 39.707,-94.96998 39.71607,-94.96989 39.7272,-94.96899 39.73352,-94.96752 39.73893,-94.96489 39.74202,-94.96126 39.74464,-94.95784 39.74578,-94.9519 39.74726,-94.94767 39.74598,-94.94275 39.74367,-94.93365 39.73854,-94.92205 39.73151,-94.91122 39.7278,-94.90232 39.72618,-94.89314 39.72572,-94.88793 39.72668,-94.88129 39.72913,-94.87545 39.73332,-94.86964 39.73841,-94.86615 39.74329,-94.86411 39.74822,-94.86385 39.7532,-94.8643 39.75801,-94.86568 39.76389,-94.8673 39.76782,-94.87132 39.77148,-94.87662 39.77308,-94.8814 39.77322,-94.8857 39.77107,-94.88918 39.76842,-94.89499 39.76499,-94.90097 39.76321,-94.9095 39.76223,-94.91891 39.76287,-94.92487 39.7663,-94.92836 39.76821,-94.93051 39.76944,-94.93364 39.77346,-94.93493 39.77616,-94.93565 39.77977,-94.93517 39.78341,-94.93346 39.78605,-94.93036 39.78823,-94.92728 39.78996,-94.92126 39.79069,-94.91707 39.79057,-94.91147 39.79067,-94.90563 39.79161,-94.90016 39.7927,-94.89411 39.79445,-94.89066 39.79515,-94.88831 39.79656,-94.88429 39.79916,-94.88169 39.80317,-94.88029 39.80721,-94.87862 39.81125,-94.87866 39.81578,-94.87935 39.82079,-94.87954 39.82216,-94.88088 39.82527,-94.88339 39.82885,-94.88626 39.83143,-94.88972 39.83425,-94.89264 39.83569,-94.89861 39.83632,-94.90762 39.83655,-94.91293 39.83764,-94.91766 39.83868,-94.92119 39.84014,-94.92649 39.84211,-94.93067 39.84518,-94.93515 39.84871,-94.93736 39.8524,-94.93942 39.85589,-94.94118 39.86022,-94.9398 39.86381,-94.93784 39.86693,-94.93514 39.87094,-94.93212 39.87357,-94.93015 39.87714,-94.92881 39.87983,-94.9286 39.8839,-94.92969 39.88692,-94.93301 39.89174,-94.93645 39.89502,-94.94449 39.89888,-94.95116 39.90088,-94.95834 39.90154,-94.9643 39.90216,-94.97093 39.90144,-94.97631 39.90007,-94.98243 39.89949,-94.98904 39.89922,-94.99489 39.8997,-94.99741 39.89991,-95.00393 39.90145,-95.01114 39.9012,-95.01846 39.89868,-95.02345 39.89323,-95.02669 39.88984,-95.0268 39.88637,-95.02726 39.88227,-95.0283 39.87772,-95.02938 39.87452,-95.03403 39.87035,-95.0416 39.86666,-95.04829 39.86457,-95.05956 39.86348,-95.07633 39.86314,-95.0867 39.86381,-95.09147 39.86439,-95.09799 39.86541,-95.10389 39.8679,-95.10892 39.86927,-95.11512 39.87138,-95.11926 39.87313,-95.12278 39.87431,-95.12774 39.87568,-95.13163 39.87727,-95.13628 39.88057,-95.13882 39.88361,-95.14077 39.88765,-95.14152 39.89172,-95.14165 39.89534,-95.14235 39.89805,-95.14303 39.90298,-95.14563 39.90569,-95.15111 39.90784,-95.15649 39.90728,-95.16147 39.90619,-95.16656 39.90389,-95.17189 39.90148,-95.17957 39.89907,-95.18639 39.89914,-95.19342 39.90059,-95.19878 39.90355,-95.20321 39.90583,-95.20633 39.90893,-95.20622 39.91146,-95.20488 39.9146,-95.20291 39.91817,-95.20076 39.92039,-95.20028 39.92358,-95.19981 39.92722,-95.20058 39.92991,-95.20163 39.93311,-95.20379 39.93711,-95.20634 39.94114,-95.21001 39.94287,-95.21665 39.94409,-95.22203 39.94352,-95.22923 39.94336,-95.23594 39.94399,-95.24071 39.94538,-95.2468 39.94797,-95.25296 39.95192,-95.25641 39.95586,-95.26047 39.95992,-95.26551 39.96615,-95.27197 39.97033,-95.2778 39.9736,-95.28572 39.9766,-95.29437 39.97952,-95.30022 39.9833,-95.30601 39.98798,-95.30922 39.99377,-95.308 39.99989,-95.33968 40.00063,-95.43938 40.00082,-95.4508 40.00065,-95.54552 40.00159,-95.7865 40.0015,-95.99983 40.00131,-96.01006 40.0013,-96.23811 40.0014,-96.4636 40.00262,-96.4672 40.00262,-96.80672 40.00217,-96.9184 40.00227,-97.00124 40.00249,-97.33384 40.0034,-97.36868 40.00354,-97.37115 40.00353,-97.81775 40.00354,-97.82154 40.00353,-97.93002 40.00311,-97.9939 40.00286,-98.02944 40.00291,-98.27549 40.00379,-98.50454 40.00361,-98.69966 40.00333,-98.72883 40.00322,-98.99962 40.00214,-99.06706 40.00225,-99.17972 40.00243,-99.19975 40.00253,-99.28298 40.00261,-99.62601 40.00281,-99.62759 40.00281,-99.99561 40.00291,-100.1784 40.00277,-100.19192 40.00276,-100.1946 40.00276,-100.6854 40.00325,-100.73985 40.00332,-100.75684 40.00333,-100.75966 40.00335,-101.0006 40.0037,-101.299 40.00422,-101.32242 40.00425,-101.36938 40.00433,-101.41068 40.00451,-101.54526 40.0043,-101.62334 40.00417,-101.77873 40.00393,-101.86121 40.00393,-102.04855 40.00391))',
  5.                                         4326);
  6.    v_count NUMBER;  
  7. BEGIN
  8.   SELECT COUNT(*)
  9.     INTO v_count
  10.    FROM G_Place G
  11.   WHERE sdo_anyinteract(G.Geog4326,v_geom)= 'TRUE';
  12.   dbms_output.put_line(v_count);
  13. END;
  14. /
  15. anonymous block completed
  16. 9886

Notice that with CLOB constructor executed natively rather than via PLSQL we now get a different error:

  1. SELECT COUNT(*)
  2.   FROM G_Place G
  3.  WHERE sdo_anyinteract(G.Geog4326,
  4.                        sdo_geometry('POLYGON((-102.04855 40.00391,-102.04756 39.57374,-102.04755 39.56886,-102.04635 39.1339,-102.0461 39.04563,-102.04546 38.69533,-102.04538 38.61375,-102.04431 38.26762,-102.0443 38.2627,-102.04324 37.73841,-102.04305 37.64289,-102.04253 37.38824,-102.04227 37.25625,-102.04225 37.244,-102.04219 37.21269,-102.04217 37.19999,-102.04211 37.17051,-102.04208 37.156,-102.04198 37.11017,-102.04227 36.99223,-102.04175 36.99222,-102.02805 36.99249,-101.98809 36.99253,-101.91798 36.99286,-101.55524 36.99513,-101.54396 36.99515,-101.26794 36.9967,-101.06711 36.99783,-100.95359 36.99877,-100.94408 36.99883,-100.63371 36.99865,-100.10789 36.99834,-100.09022 36.99832,-100.00243 36.99849,-100.00089 36.9985,-99.53989 36.99975,-99.45157 36.99996,-99.40044 37.00008,-99.00054 37.00076,-98.54572 37.00022,-98.50035 37.00024,-98.34823 37.00032,-98.32906 37.00031,-98.11642 37.00026,-98.05378 37.00027,-97.89861 37.00031,-97.80196 37.00035,-97.74242 37.00032,-97.53543 37.00024,-97.46236 37.00021,-97.45967 37.0002,-97.41106 37.00007,-97.27104 36.99971,-97.19783 36.99952,-97.14678 37.0001,-97.14677 36.99939,-97.02897 36.9995,-96.9287 36.9996,-96.75315 36.99978,-96.54362 37.00078,-96.54139 37.00079,-96.52834 37.00079,-96.30413 37.00063,-96.28107 37.0006,-96.2459 37.00054,-96.23669 37.00074,-96.1168 37.00071,-96.0949 37.00028,-96.00037 37.00018,-95.97443 37.00031,-95.96521 37.00035,-95.96426 37.00035,-95.92773 37.00047,-95.80035 37.00042,-95.78843 37.00041,-95.77198 37.0004,-95.52364 37.00027,-95.52364 37.00093,-95.40672 37.00047,-95.40027 37.00053,-95.07227 36.99872,-95.03362 36.99859,-95.03309 36.9992,-94.85946 36.99972,-94.61897 36.99996,-94.61871 37.05848,-94.61748 37.33839,-94.61739 37.36412,-94.61728 37.38238,-94.61642 37.63641,-94.61636 37.65386,-94.61623 37.67361,-94.61481 38.03646,-94.61472 38.05964,-94.61483 38.11402,-94.61345 38.38924,-94.61244 38.4673,-94.61234 38.47584,-94.61015 38.73758,-94.60838 38.83414,-94.60816 38.84628,-94.60753 39.03988,-94.60751 39.04369,-94.60825 39.11467,-94.60829 39.12037,-94.60508 39.12481,-94.6029 39.12745,-94.60126 39.12968,-94.59749 39.13364,-94.59423 39.13898,-94.5938 39.14121,-94.59274 39.14531,-94.5931 39.14893,-94.59416 39.15123,-94.59598 39.15477,-94.59844 39.15791,-94.60203 39.15964,-94.60445 39.15995,-94.60674 39.15995,-94.61269 39.15998,-94.61808 39.15924,-94.62277 39.15794,-94.62769 39.15727,-94.63161 39.15598,-94.63912 39.15445,-94.64564 39.15465,-94.6506 39.15565,-94.65421 39.15741,-94.65844 39.15958,-94.66126 39.16239,-94.6629 39.16516,-94.66278 39.16743,-94.66148 39.16966,-94.66071 39.17281,-94.66116 39.17554,-94.66282 39.17786,-94.66915 39.18072,-94.67396 39.18245,-94.67733 39.18239,-94.68158 39.18253,-94.68993 39.18187,-94.69418 39.18019,-94.69932 39.17702,-94.70279 39.17455,-94.70865 39.17141,-94.71607 39.17042,-94.72501 39.16979,-94.73354 39.17006,-94.74068 39.17084,-94.74864 39.17232,-94.75507 39.17511,-94.76022 39.17811,-94.76537 39.18218,-94.76991 39.18611,-94.77395 39.19064,-94.77648 39.19558,-94.77991 39.19957,-94.78212 39.20281,-94.7851 39.20539,-94.78722 39.2075,-94.79112 39.208,-94.79709 39.20881,-94.80242 39.20825,-94.80954 39.20812,-94.8167 39.20935,-94.82501 39.21225,-94.83058 39.21589,-94.83649 39.22029,-94.83739 39.22438,-94.83416 39.23111,-94.83041 39.23463,-94.82834 39.24046,-94.82806 39.24858,-94.83233 39.25555,-94.83677 39.26065,-94.84231 39.26645,-94.8508 39.27173,-94.86399 39.27644,-94.875 39.2822,-94.88766 39.29073,-94.8966 39.30233,-94.90264 39.31249,-94.90604 39.32372,-94.90704 39.33032,-94.90755 39.33367,-94.90835 39.33955,-94.90853 39.34499,-94.90733 39.34982,-94.90597 39.35341,-94.904 39.35698,-94.90244 39.35871,-94.89843 39.36135,-94.89369 39.36432,-94.88983 39.36655,-94.88613 39.36916,-94.88383 39.37221,-94.88217 39.37676,-94.88319 39.38042,-94.88604 39.38486,-94.88932 39.38876,-94.8934 39.39153,-94.89819 39.39234,-94.90175 39.39182,-94.90769 39.39066,-94.91354 39.38842,-94.91801 39.38687,-94.92415 39.38504,-94.93069 39.38491,-94.93401 39.38662,-94.93494 39.3871,-94.93875 39.38882,-94.94344 39.39192,-94.94597 39.3964,-94.94667 39.39956,-94.9462 39.40319,-94.94619 39.40718,-94.94943 39.41084,-94.95416 39.41466,-94.95855 39.4161,-94.96296 39.41763,-94.96825 39.41914,-94.97124 39.4213,-94.97459 39.4234,-94.97739 39.42711,-94.98054 39.43199,-94.98188 39.4365,-94.98318 39.43965,-94.98495 39.4432,-94.98756 39.44545,-94.99128 39.44791,-94.99834 39.44993,-95.00656 39.45243,-95.02476 39.45838,-95.03461 39.46174,-95.03993 39.46515,-95.04333 39.46887,-95.04637 39.4742,-95.04836 39.48005,-95.04909 39.48367,-95.04929 39.48956,-95.05103 39.49494,-95.05322 39.49945,-95.05658 39.50371,-95.06185 39.50744,-95.06917 39.51182,-95.07706 39.51574,-95.08621 39.52145,-95.09483 39.52744,-95.10105 39.53281,-95.10458 39.53587,-95.11033 39.54498,-95.11488 39.55532,-95.11567 39.56074,-95.11529 39.56664,-95.11372 39.57014,-95.11172 39.57462,-95.10796 39.57859,-95.10365 39.5812,-95.10032 39.58192,-95.09496 39.58249,-95.09018 39.58213,-95.08517 39.58025,-95.08112 39.57914,-95.07452 39.57837,-95.06908 39.57981,-95.06209 39.58135,-95.05755 39.58357,-95.05391 39.58651,-95.05046 39.5902,-95.04822 39.59433,-95.04674 39.59734,-95.04606 39.60072,-95.04738 39.60432,-95.04862 39.60782,-95.05081 39.61197,-95.05239 39.61654,-95.05517 39.62185,-95.05505 39.62387,-95.05492 39.62614,-95.05312 39.63142,-95.04951 39.63914,-95.04237 39.64887,-95.03669 39.65714,-95.03103 39.66313,-95.02354 39.66973,-95.01429 39.67537,-95.00513 39.67773,-94.99861 39.67922,-94.99257 39.6793,-94.98788 39.68034,-94.98289 39.68175,-94.97784 39.68372,-94.97377 39.68697,-94.97173 39.6896,-94.96967 39.69498,-94.96941 39.70042,-94.96967 39.707,-94.96998 39.71607,-94.96989 39.7272,-94.96899 39.73352,-94.96752 39.73893,-94.96489 39.74202,-94.96126 39.74464,-94.95784 39.74578,-94.9519 39.74726,-94.94767 39.74598,-94.94275 39.74367,-94.93365 39.73854,-94.92205 39.73151,-94.91122 39.7278,-94.90232 39.72618,-94.89314 39.72572,-94.88793 39.72668,-94.88129 39.72913,-94.87545 39.73332,-94.86964 39.73841,-94.86615 39.74329,-94.86411 39.74822,-94.86385 39.7532,-94.8643 39.75801,-94.86568 39.76389,-94.8673 39.76782,-94.87132 39.77148,-94.87662 39.77308,-94.8814 39.77322,-94.8857 39.77107,-94.88918 39.76842,-94.89499 39.76499,-94.90097 39.76321,-94.9095 39.76223,-94.91891 39.76287,-94.92487 39.7663,-94.92836 39.76821,-94.93051 39.76944,-94.93364 39.77346,-94.93493 39.77616,-94.93565 39.77977,-94.93517 39.78341,-94.93346 39.78605,-94.93036 39.78823,-94.92728 39.78996,-94.92126 39.79069,-94.91707 39.79057,-94.91147 39.79067,-94.90563 39.79161,-94.90016 39.7927,-94.89411 39.79445,-94.89066 39.79515,-94.88831 39.79656,-94.88429 39.79916,-94.88169 39.80317,-94.88029 39.80721,-94.87862 39.81125,-94.87866 39.81578,-94.87935 39.82079,-94.87954 39.82216,-94.88088 39.82527,-94.88339 39.82885,-94.88626 39.83143,-94.88972 39.83425,-94.89264 39.83569,-94.89861 39.83632,-94.90762 39.83655,-94.91293 39.83764,-94.91766 39.83868,-94.92119 39.84014,-94.92649 39.84211,-94.93067 39.84518,-94.93515 39.84871,-94.93736 39.8524,-94.93942 39.85589,-94.94118 39.86022,-94.9398 39.86381,-94.93784 39.86693,-94.93514 39.87094,-94.93212 39.87357,-94.93015 39.87714,-94.92881 39.87983,-94.9286 39.8839,-94.92969 39.88692,-94.93301 39.89174,-94.93645 39.89502,-94.94449 39.89888,-94.95116 39.90088,-94.95834 39.90154,-94.9643 39.90216,-94.97093 39.90144,-94.97631 39.90007,-94.98243 39.89949,-94.98904 39.89922,-94.99489 39.8997,-94.99741 39.89991,-95.00393 39.90145,-95.01114 39.9012,-95.01846 39.89868,-95.02345 39.89323,-95.02669 39.88984,-95.0268 39.88637,-95.02726 39.88227,-95.0283 39.87772,-95.02938 39.87452,-95.03403 39.87035,-95.0416 39.86666,-95.04829 39.86457,-95.05956 39.86348,-95.07633 39.86314,-95.0867 39.86381,-95.09147 39.86439,-95.09799 39.86541,-95.10389 39.8679,-95.10892 39.86927,-95.11512 39.87138,-95.11926 39.87313,-95.12278 39.87431,-95.12774 39.87568,-95.13163 39.87727,-95.13628 39.88057,-95.13882 39.88361,-95.14077 39.88765,-95.14152 39.89172,-95.14165 39.89534,-95.14235 39.89805,-95.14303 39.90298,-95.14563 39.90569,-95.15111 39.90784,-95.15649 39.90728,-95.16147 39.90619,-95.16656 39.90389,-95.17189 39.90148,-95.17957 39.89907,-95.18639 39.89914,-95.19342 39.90059,-95.19878 39.90355,-95.20321 39.90583,-95.20633 39.90893,-95.20622 39.91146,-95.20488 39.9146,-95.20291 39.91817,-95.20076 39.92039,-95.20028 39.92358,-95.19981 39.92722,-95.20058 39.92991,-95.20163 39.93311,-95.20379 39.93711,-95.20634 39.94114,-95.21001 39.94287,-95.21665 39.94409,-95.22203 39.94352,-95.22923 39.94336,-95.23594 39.94399,-95.24071 39.94538,-95.2468 39.94797,-95.25296 39.95192,-95.25641 39.95586,-95.26047 39.95992,-95.26551 39.96615,-95.27197 39.97033,-95.2778 39.9736,-95.28572 39.9766,-95.29437 39.97952,-95.30022 39.9833,-95.30601 39.98798,-95.30922 39.99377,-95.308 39.99989,-95.33968 40.00063,-95.43938 40.00082,-95.4508 40.00065,-95.54552 40.00159,-95.7865 40.0015,-95.99983 40.00131,-96.01006 40.0013,-96.23811 40.0014,-96.4636 40.00262,-96.4672 40.00262,-96.80672 40.00217,-96.9184 40.00227,-97.00124 40.00249,-97.33384 40.0034,-97.36868 40.00354,-97.37115 40.00353,-97.81775 40.00354,-97.82154 40.00353,-97.93002 40.00311,-97.9939 40.00286,-98.02944 40.00291,-98.27549 40.00379,-98.50454 40.00361,-98.69966 40.00333,-98.72883 40.00322,-98.99962 40.00214,-99.06706 40.00225,-99.17972 40.00243,-99.19975 40.00253,-99.28298 40.00261,-99.62601 40.00281,-99.62759 40.00281,-99.99561 40.00291,-100.1784 40.00277,-100.19192 40.00276,-100.1946 40.00276,-100.6854 40.00325,-100.73985 40.00332,-100.75684 40.00333,-100.75966 40.00335,-101.0006 40.0037,-101.299 40.00422,-101.32242 40.00425,-101.36938 40.00433,-101.41068 40.00451,-101.54526 40.0043,-101.62334 40.00417,-101.77873 40.00393,-101.86121 40.00393,-102.04855 40.00391))',
  5.                        4326)
  6.        )= 'TRUE';
  7. SQL Error: ORA-01704: string literal too long
  8. 01704. 00000 -  "string literal too long"
  9. *Cause:    The string literal IS longer than 4000 characters.
  10. *Action:   USE a string literal OF at most 4000 characters.
  11.            Longer VALUES may ONLY be entered USING bind VARIABLES.

This shows more clearly the SQL level restriction: the maximum size of a varchar2 object can hold in SQL is 4000 bytes; in PLSQL it is 32,767 bytes. So, the CLOB was fine in PLSQL but not in SQL.

I hope this helps someone else out there.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment