Friday, July 20, 2018

The importance of being aliased

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I was writing a query returning the indexes to be monitored for a list of schemas for those tables having fewer than 5 indexes (not counting LOB indexes) and I was surprised to see that it returned no rows.
After checking what's wrong I realized that I had specified the wrong column for the included subquery, but how could it be that Oracle wasn't complaining about a non existing column?

with excluded as (select /*+ MATERIALIZE */ b.owner, b.index_name
                                      from dba_constraints b
                                     where b.constraint_type in ('P','U')
                                       and b.owner in ('A','B','C')
                                     ),
       included as (select /*+ MATERIALIZE */ owner, table_name
                                from dba_indexes
                               where owner in ('A','B','C') 
                                 and index_type != 'LOB' 
                               group by owner, table_name
                               having count(*) > 5
                            )
select 'ALTER INDEX ' || owner ||'.' || index_name || ' MONITORING USAGE;' as ddl
  from dba_indexes a
 where (a.owner, a.table_name) in (select owner, index_name
                                     from included
                              )
  and (a.owner, a.index_name) not in (select owner, index_name
                                        from excluded 
                                     )
  and a.owner in ('A','B','C')
  and a.index_type != 'LOB';
 
DDL
===
(no rows) 

Soon I realized not only that I had specified the wrong column (I should have written table_name instead) but also that I hadn't specified table aliases and index_name was not a value coming from included, but from the outer view dba_indexes, which is just fine from Oracle's point of view but totally wrong for my purposes.

In order to prevent these mistakes, the SQL above needs to be written using ALWAYS table aliases in the subqueries and applying them to each relevant column:

  with excluded as (select /*+ MATERIALIZE */ b.owner, b.index_name
                                      from dba_constraints b
                                     where b.constraint_type in ('P','U')
                                       and b.owner in ('A','B','C')
                                     ),
       included as (select /*+ MATERIALIZE */ owner, table_name
                                from dba_indexes
                               where owner in ('A','B','C') 
                                 and index_type != 'LOB' 
                               group by owner, table_name
                               having count(*) > 5
                            )
  select 'ALTER INDEX ' || owner ||'.' || index_name || ' MONITORING USAGE;' as ddl
  from dba_indexes a
 where (a.owner, a.table_name) in (select i.owner, i.table_name
                                     from included i
                              )
  and (a.owner, a.index_name) not in (select e.owner, e.index_name
                                        from excluded e
                                     )
  and a.owner in ('A','B','C')
  and a.index_type != 'LOB';


In this case it was easy to guess that something was wrong because the query returned no results and I knew it was nonsense, but in complex queries made up possibly of several subqueries one can easily overlook the problem should some results being returned.

Table aliases must be used at all times!

Wednesday, June 13, 2018

ORA-01704: string literal too long

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Just a quick post about an annoying problem that you may come across when doing test with certain objects created on the fly: literals longer than 4000 characters.

 Take the following query, containing a very long string literal, if you try to execute it:
 
SELECT SDO_UTIL.FROM_WKTGEOMETRY(
'POLYGON ((1612302.7276902203 4950081.050436133, 1612302.7276890145 4950081.050437695, 1612302.7276881393 4950081.050439465, 1612302.7276876287 4950081.050441371, 1612302.7276875023 4950081.050443341, 1612302.7276877656 4950081.050445298, 1612302.727688408 4950081.050447164, 1612312.9564954615 4950103.33475744, 1612312.9564961737 4950103.334758732, 1612338.1769781404 4950142.006065012, 1612338.1743615821 4950142.013483351, 1612338.1743615824 4950142.013483351, 1612338.1743611062 4950142.013485313, 1612338.174361034 4950142.013487331, 1612338.1743613686 4950142.013489322, 1612338.1743620962 4950142.013491205, 1612338.1743631875 4950142.013492904, 1612338.1743645975 4950142.013494349, 1612338.174366269 4950142.013495481, 1612338.174368134 4950142.013496255, 1612338.1743701163 4950142.013496637, 1612338.1743721352 4950142.013496615, 1612338.1743741082 4950142.013496187, 1612444.319999543 4950107.460000149, 1612444.3199999803 4950107.460000051, 1612444.320000246 4950107.4600004135, 1612444.676621345 4950108.060090811, 1612444.6766213449 4950108.060090811, 1612444.6766225009 4950108.060092383, 1612444.67662394 4950108.060093702, 1612444.6766256078 4950108.060094715, 1612444.6766274406 4950108.060095385, 1612444.6766293687 4950108.060095686, 1612444.6766313186 4950108.060095607, 1612444.6766332162 4950108.060095151, 1612444.6766349887 4950108.060094335, 1612444.6766366428 4950108.060093368, 1612462.5600570757 4950138.152752384, 1612495.715609956 4950193.9439799255, 1612495.7156100168 4950193.943980027, 1612511.8134320595 4950220.318693245, 1612511.8134332695 4950220.318694842, 1612511.8134347738 4950220.318696165, 1612511.8134365117 4950220.318697163, 1612511.8134384134 4950220.318697794, 1612511.813440403 4950220.318698033, 1612511.8134424002 4950220.318697871, 1612511.813444325 4950220.318697314, 1612511.8134460999 4950220.318696383, 1612511.8134476538 4950220.318695119, 1612511.8134489243 4950220.318693569, 1612511.8134498606 4950220.318691798, 1612511.8134504247 4950220.318689874, 1612511.813450594 4950220.318687879, 1612511.813450362 4950220.318685887, 1612511.8134497378 4950220.318683984, 1612500.6749918663 4950195.181303823, 1612500.6799360043 4950195.170415407, 1612502.7187613912 4950194.888863886, 1612520.1830489973 4950234.021594246, 1612520.176078283 4950234.020041902, 1612520.1760763521 4950234.020041666, 1612520.1760744122 4950234.020041808, 1612520.1760725365 4950234.020042323, 1612520.1760707959 4950234.0200431915, 1612520.1760692564 4950234.02004438, 1612520.176067976 4950234.020045845, 1612520.1760670035 4950234.02004753, 1612520.1760663756 4950234.02004937, 1612520.176066116 4950234.020051298, 1612520.1760662345 4950234.02005324, 1612520.1760667264 4950234.020055122, 1612520.1760675735 4950234.020056873, 1612525.9549299977 4950243.488159996, 1612525.9549300033 4950243.488160006, 1612545.9948357465 4950277.12255545, 1612545.994835822 4950277.122555574, 1612545.9962470904 4950277.124847643, 1612545.9962470906 4950277.124847643, 1612545.996248258 4950277.124849183, 1612545.9962497 4950277.12485047, 1612545.9962513624 4950277.124851455, 1612545.9962531833 4950277.124852102, 1612545.996255095 4950277.124852386, 1612545.9962570253 4950277.124852299, 1612545.9962589028 4950277.124851841, 1612545.9962606572 4950277.12485103, 1612545.9962622228 4950277.124849898, 1612545.9962635413 4950277.124848485, 1612545.9962645636 4950277.124846845, 1612545.9962652512 4950277.124845038, 1612545.9962655788 4950277.124843134, 1612545.9962655336 4950277.124841202, 1612545.9441220688 4950276.69273876, 1612615.3810361782 4950251.971107302, 1612615.3810361784 4950251.971107302, 1612615.3810378858 4950251.971106506, 1612615.3810394134 4950251.971105404, 1612615.3810407072 4950251.971104034, 1612615.3810417214 4950251.971102447, 1612615.3810424197 4950251.971100697, 1612615.3810427773 4950251.971098848, 1612615.3810427818 4950251.971096964,1612615.381042433 4950251.971095112, 1612593.5622557285 4950176.268175045, 1612589.41878 4950161.89192, 1612584.87928 4950146.02118, 1612584.8792799981 4950146.021180001, 1612584.8792799944 4950146.021179981, 1612580.1291894687 4950129.544864481, 1612562.674608643 4950069.001480894, 1612562.6746078886 4950069.001479016, 1612562.6746067712 4950069.001477328, 1612562.674605337 4950069.0014759, 1612562.6746036448 4950069.00147479, 1612562.6746017635 4950069.001474043, 1612562.6745997705 4950069.001473691, 1612562.6745977472 4950069.001473747, 1612562.6745957767 4950069.001474209, 1612559.0329492528 4950070.256152876, 1612558.882270013 4950070.308079995, 1612558.882269965 4950070.308080011, 1612558.882268369 4950070.308080528, 1612558.8822679287 4950070.308080623, 1612558.8822677515 4950070.308080209, 1612558.8807321305 4950070.303215536, 1612558.8807321307 4950070.303215536, 1612558.8807313445 4950070.303213705, 1612558.880730211 4950070.303212066, 1612558.8807287752 4950070.303210685, 1612558.8807270937 4950070.303209616, 1612558.8806900482 4950070.303190952, 1612558.831813929 4950070.148248236, 1612558.8073840255 4950070.070857081, 1612558.8073839773 4950070.0708569195, 1612551.9238770062 4950045.8155640215, 1612551.9238770003 4950045.815564, 1612551.5941031226 4950044.662955338, 1612538.8696744982 4950000.190584168, 1612538.7835280267 4949999.889535792, 1612538.7835280206 4949999.889535771, 1612511.5793044048 4949905.615640255, 1612511.5798917084 4949905.614583897, 1612511.5798917082 4949905.614583897, 1612511.5798924547 4949905.6145822015, 1612511.5798928756 4949905.6145803975, 1612511.5798929564 4949905.614578546, 1612511.5798926943 4949905.614576712, 1612511.5798920984 4949905.614574958, 1612511.577315513 4949905.608808861, 1612511.5773155128 4949905.608808861, 1612511.577314502 4949905.608807104, 1612511.5773131577 4949905.608805586, 1612511.5773115347 4949905.60880437, 1612511.5773097 4949905.608803507, 1612511.5773077286 4949905.608803032, 1612511.5773057023 4949905.608802964, 1612511.5773037036 4949905.608803307, 1612500.103112472 4949908.799904869, 1612500.1031124722 4949908.799904869, 1612511.5841630423 4949905.606895109, 1612511.5841648853 4949905.606894393, 1612511.5841665517 4949905.606893329, 1612511.5841679762 4949905.606891958, 1612511.5841691028 4949905.606890333, 1612511.58417141 4949905.606886183, 1612524.2976637285 4949902.01932794, 1612524.2976655727 4949902.019327216, 1612524.297667238 4949902.019326142, 1612524.297668659 4949902.019324761, 1612524.2976697795 4949902.019323127, 1612524.297670556 4949902.019321304, 1612524.2976709576 4949902.019319364, 1612524.297670969 4949902.019317382, 1612524.2976705895 4949902.019315437, 1612523.0232272441 4949897.779511995, 1612522.5135900173 4949896.084105058, 1612522.5135899826 4949896.084104935, 1612520.1865722018 4949887.476697587, 1612520.1865721773 4949887.476697498, 1612517.7823322963 4949878.8986931415, 1612517.782331557 4949878.89869126, 1612517.7823304546 4949878.898689567, 1612517.7823290343 4949878.898688129, 1612517.7823273537 4949878.898687007, 1612517.1003535376 4949878.536893573, 1612514.3841257987 4949875.63958398, 1612512.9343265232 4949873.827413355, 1612512.9343257917 4949873.827412537, 1612511.66630308 4949872.55729771, 1612511.3037624902 4949865.493014319, 1612511.3037621186 4949865.493012084, 1612509.4922274272 4949859.152640665, 1612508.767330085 4949855.34726788, 1612508.7673298283 4949855.34726684, 1612504.9625499095 4949842.845526215, 1612504.238054365 4949838.315586878, 1612504.4196325787 4949836.144113403, 1612505.5758999523 4949835.34744768, 1612505.5759015223 4949835.34744634, 1612505.5759027835 4949835.347444706, 1612505.5759036823 4949835.347442848, 1612505.5759041805 4949835.347440844, 1612505.5759042567 4949835.3474387815, 1612505.5759039077 4949835.347436747, 1612505.1327547848 4949833.76634822, 1612505.1327540686 4949833.766346385, 1612505.1327530064 4949833.766344725,
1612505.1327516397 4949833.766343306, 1612505.132750021 4949833.766342183, 1612505.1327482136 4949833.7663413985, 1612505.1327462874 4949833.766340984, 1612505.1327443172 4949833.766340954, 1612505.1327423798 4949833.766341313, 1612500.0087109632 4949835.246938101, 1612499.999999141 4949833.0113034155, 1612499.999998956 4949833.011301539, 1612499.9999984219 4949833.01129973, 1612499.9999975576 4949833.011298054, 1612499.9999963942 4949833.01129657, 1612499.9999949727 4949833.011295331, 1612499.999993344 4949833.011294381, 1612499.9999915657 4949833.011293753, 1612499.5701146345 4949832.903867232, 1612499.570112807 4949832.903866951, 1612499.5701109592 4949832.903867012, 1612499.570109154 4949832.903867411, 1612332.6885689322 4949886.464173349, 1612332.6885668929 4949886.464174266, 1612332.6885651082 4949886.464175614, 1612332.6885636675 4949886.4641773235, 1612332.3128312742 4949887.027824378, 1612332.3128304565 4949887.027825864, 1612331.5613624363 4949888.718863602, 1612331.5613618207 4949888.718865458, 1612331.561361578 4949888.718867399, 1612331.561361718 4949888.718869349, 1612331.8870316627 4949890.621959667, 1612329.4981346398 4949891.390437311, 1612329.49813464 4949891.390437311, 1612329.4981327904 4949891.39043812, 1612329.498131141 4949891.390439284, 1612329.498129759 4949891.3904407555, 1612329.4981287008 4949891.390442475, 1612329.4981280093 4949891.390444371, 1612329.4981277129 4949891.390446369, 1612329.4981278235 4949891.390448384, 1612331.6861899188 4949905.308067472, 1612331.686189919 4949905.308067472, 1612331.6861906594 4949905.30806998, 1612337.986171594 4949919.483300679, 1612337.9861718942 4949919.483301297, 1612346.5503238584 4949935.659945988, 1612346.5503248665 4949935.65994753, 1612346.5503261406 4949935.65994886, 1612346.550327637 4949935.659949935, 1612346.5503293052 4949935.659950716, 1612346.5503310885 4949935.659951179, 1612346.5503329262 4949935.659951307, 1612346.5503347563 4949935.659951095, 1612365.956240631 4949931.57451634, 1612373.6575880218 4949945.849508198, 1612373.6575888467 4949945.849509482, 1612373.6575898577 4949945.849510625, 1612386.4368673943 4949958.254476118, 1612390.5710568763 4949976.6693648435, 1612390.5710574416 4949976.669366592, 1612391.698541782 4949979.300982044, 1612391.8866131937 4949983.433933367, 1612391.3236194993 4949987.188960429, 1612390.194169866 4949988.322196488, 1612387.0469123712 4949990.476230033, 1612387.0469108552 4949990.476231309, 1612387.0469096205 4949990.476232857, 1612387.0469087153 4949990.4762346195, 1612387.046908175 4949990.476236526, 1612387.0469080214 4949990.476238501, 1612387.04690826 4949990.476240467, 1612387.0469088817 4949990.476242349, 1612387.423641842 4949991.323474103, 1612381.3630180343 4949993.9591765925, 1612381.3630180345 4949993.9591765925, 1612381.3630170042 4949993.959177114, 1612378.702303411 4949995.502856269, 1612378.7023034107 4949995.502856269, 1612378.702301683 4949995.502857536, 1612378.702300265 4949995.502859144, 1612378.7022992221 4949995.5028610155, 1612372.903983685 4950009.182204972, 1612351.8583981518 4950020.832831716, 1612351.8583962126 4950020.832833117, 1612349.414746833 4950023.088351959, 1612349.4147455762 4950023.08835336, 1612349.4147446041 4950023.088354972, 1612349.4147439515 4950023.088356738, 1612349.4147436412 4950023.088358594, 1612349.2266783365 4950025.719517744, 1612348.662713838 4950033.2375448495, 1612346.2201414702 4950045.826125534, 1612343.2116520128 4950052.218134204, 1612326.6751141474 4950063.3049086565, 1612323.8567027529 4950063.681125215, 1612321.415211409 4950063.868924944, 1612319.6478427825 4950063.529948768, 1612319.6478409332 4950063.529948589, 1612319.6478390824 4950063.529948755, 1612319.6478372945 4950063.529949261, 1612319.647835631 4950063.529950089, 1612319.6478341494 4950063.52995121, 1612319.6478329008 4950063.529952587, 1612318.4054309344 4950065.18546395, 1612313.7081326174 4950069.694802342, 
1612309.0100455247 4950074.017106278, 1612309.010044676 4950074.017107161, 1612305.8152607656 4950077.775625582, 1612302.7276902203 4950081.050436133), (1612577.4612226172 4950169.171184227, 1612577.4743234555 4950169.174929047, 1612613.090467904 4950248.925765897, 1612611.355973458 4950250.097461703, 1612576.5989585689 4950169.761114943, 1612577.4612226172 4950169.171184227), (1612552.3481246177 4950177.6450913735, 1612552.3612103625 4950177.648812867, 1612589.427143694 4950259.457142527, 1612586.5180513188 4950259.738981499, 1612550.6099401454 4950178.8195660785, 1612552.3481246177 4950177.6450913735), (1612527.235158953 4950186.118358558, 1612527.2479602853 4950186.122239187, 1612566.066669277 4950269.100517044, 1612564.0327389643 4950269.381574221, 1612525.7893589304 4950187.001990109, 1612527.235158953 4950186.118358558), (1612484.88543452 4950095.0973148495, 1612484.8978037445 4950095.101689731, 1612523.4342271215 4950180.989262764, 1612521.9916516265 4950181.870405617, 1612483.7316950404 4950095.690477499, 1612484.88543452 4950095.0973148495), (1612498.658874648 4950192.9347000765, 1612498.6008893466 4950192.943744307, 1612457.7501397943 4950105.023844077, 1612459.4944369185 4950104.455610587, 1612500.3664662621 4950190.9275897555, 1612498.658874648 4950192.9347000765))'
) as geo FROM dual;

you'll get ORA-01704: string literal too long.

You can work around the problem by concatenating chunks of less than 4000 characters in this fashion:

SELECT SDO_UTIL.FROM_WKTGEOMETRY(empty_clob()||
'POLYGON ((1612302.7276902203 4950081.050436133, 1612302.7276890145 4950081.050437695, 1612302.7276881393 4950081.050439465, 1612302.7276876287 4950081.050441371, 1612302.7276875023 4950081.050443341, 1612302.7276877656 4950081.050445298, 1612302.727688408 4950081.050447164, 1612312.9564954615 4950103.33475744, 1612312.9564961737 4950103.334758732, 1612338.1769781404 4950142.006065012, 1612338.1743615821 4950142.013483351, 1612338.1743615824 4950142.013483351, 1612338.1743611062 4950142.013485313, 1612338.174361034 4950142.013487331, 1612338.1743613686 4950142.013489322, 1612338.1743620962 4950142.013491205, 1612338.1743631875 4950142.013492904, 1612338.1743645975 4950142.013494349, 1612338.174366269 4950142.013495481, 1612338.174368134 4950142.013496255, 1612338.1743701163 4950142.013496637, 1612338.1743721352 4950142.013496615, 1612338.1743741082 4950142.013496187, 1612444.319999543 4950107.460000149, 1612444.3199999803 4950107.460000051, 1612444.320000246 4950107.4600004135, 1612444.676621345 4950108.060090811, 1612444.6766213449 4950108.060090811, 1612444.6766225009 4950108.060092383, 1612444.67662394 4950108.060093702, 1612444.6766256078 4950108.060094715, 1612444.6766274406 4950108.060095385, 1612444.6766293687 4950108.060095686, 1612444.6766313186 4950108.060095607, 1612444.6766332162 4950108.060095151, 1612444.6766349887 4950108.060094335, 1612444.6766366428 4950108.060093368, 1612462.5600570757 4950138.152752384, 1612495.715609956 4950193.9439799255, 1612495.7156100168 4950193.943980027, 1612511.8134320595 4950220.318693245, 1612511.8134332695 4950220.318694842, 1612511.8134347738 4950220.318696165, 1612511.8134365117 4950220.318697163, 1612511.8134384134 4950220.318697794, 1612511.813440403 4950220.318698033, 1612511.8134424002 4950220.318697871, 1612511.813444325 4950220.318697314, 1612511.8134460999 4950220.318696383, 1612511.8134476538 4950220.318695119, 1612511.8134489243 4950220.318693569, 1612511.8134498606 4950220.318691798, 1612511.8134504247 4950220.318689874, 1612511.813450594 4950220.318687879, 1612511.813450362 4950220.318685887, 1612511.8134497378 4950220.318683984, 1612500.6749918663 4950195.181303823, 1612500.6799360043 4950195.170415407, 1612502.7187613912 4950194.888863886, 1612520.1830489973 4950234.021594246, 1612520.176078283 4950234.020041902, 1612520.1760763521 4950234.020041666, 1612520.1760744122 4950234.020041808, 1612520.1760725365 4950234.020042323, 1612520.1760707959 4950234.0200431915, 1612520.1760692564 4950234.02004438, 1612520.176067976 4950234.020045845, 1612520.1760670035 4950234.02004753, 1612520.1760663756 4950234.02004937, 1612520.176066116 4950234.020051298, 1612520.1760662345 4950234.02005324, 1612520.1760667264 4950234.020055122, 1612520.1760675735 4950234.020056873, 1612525.9549299977 4950243.488159996, 1612525.9549300033 4950243.488160006, 1612545.9948357465 4950277.12255545, 1612545.994835822 4950277.122555574, 1612545.9962470904 4950277.124847643, 1612545.9962470906 4950277.124847643, 1612545.996248258 4950277.124849183, 1612545.9962497 4950277.12485047, 1612545.9962513624 4950277.124851455, 1612545.9962531833 4950277.124852102, 1612545.996255095 4950277.124852386, 1612545.9962570253 4950277.124852299, 1612545.9962589028 4950277.124851841, 1612545.9962606572 4950277.12485103, 1612545.9962622228 4950277.124849898, 1612545.9962635413 4950277.124848485, 1612545.9962645636 4950277.124846845, 1612545.9962652512 4950277.124845038, 1612545.9962655788 4950277.124843134, 1612545.9962655336 4950277.124841202, 1612545.9441220688 4950276.69273876, 1612615.3810361782 4950251.971107302, 1612615.3810361784 4950251.971107302, 1612615.3810378858 4950251.971106506, 1612615.3810394134 4950251.971105404, 1612615.3810407072 4950251.971104034, 1612615.3810417214 4950251.971102447, 1612615.3810424197 4950251.971100697, 1612615.3810427773 4950251.971098848, 1612615.3810427818 4950251.971096964,'||
'1612615.381042433 4950251.971095112, 1612593.5622557285 4950176.268175045, 1612589.41878 4950161.89192, 1612584.87928 4950146.02118, 1612584.8792799981 4950146.021180001, 1612584.8792799944 4950146.021179981, 1612580.1291894687 4950129.544864481, 1612562.674608643 4950069.001480894, 1612562.6746078886 4950069.001479016, 1612562.6746067712 4950069.001477328, 1612562.674605337 4950069.0014759, 1612562.6746036448 4950069.00147479, 1612562.6746017635 4950069.001474043, 1612562.6745997705 4950069.001473691, 1612562.6745977472 4950069.001473747, 1612562.6745957767 4950069.001474209, 1612559.0329492528 4950070.256152876, 1612558.882270013 4950070.308079995, 1612558.882269965 4950070.308080011, 1612558.882268369 4950070.308080528, 1612558.8822679287 4950070.308080623, 1612558.8822677515 4950070.308080209, 1612558.8807321305 4950070.303215536, 1612558.8807321307 4950070.303215536, 1612558.8807313445 4950070.303213705, 1612558.880730211 4950070.303212066, 1612558.8807287752 4950070.303210685, 1612558.8807270937 4950070.303209616, 1612558.8806900482 4950070.303190952, 1612558.831813929 4950070.148248236, 1612558.8073840255 4950070.070857081, 1612558.8073839773 4950070.0708569195, 1612551.9238770062 4950045.8155640215, 1612551.9238770003 4950045.815564, 1612551.5941031226 4950044.662955338, 1612538.8696744982 4950000.190584168, 1612538.7835280267 4949999.889535792, 1612538.7835280206 4949999.889535771, 1612511.5793044048 4949905.615640255, 1612511.5798917084 4949905.614583897, 1612511.5798917082 4949905.614583897, 1612511.5798924547 4949905.6145822015, 1612511.5798928756 4949905.6145803975, 1612511.5798929564 4949905.614578546, 1612511.5798926943 4949905.614576712, 1612511.5798920984 4949905.614574958, 1612511.577315513 4949905.608808861, 1612511.5773155128 4949905.608808861, 1612511.577314502 4949905.608807104, 1612511.5773131577 4949905.608805586, 1612511.5773115347 4949905.60880437, 1612511.5773097 4949905.608803507, 1612511.5773077286 4949905.608803032, 1612511.5773057023 4949905.608802964, 1612511.5773037036 4949905.608803307, 1612500.103112472 4949908.799904869, 1612500.1031124722 4949908.799904869, 1612511.5841630423 4949905.606895109, 1612511.5841648853 4949905.606894393, 1612511.5841665517 4949905.606893329, 1612511.5841679762 4949905.606891958, 1612511.5841691028 4949905.606890333, 1612511.58417141 4949905.606886183, 1612524.2976637285 4949902.01932794, 1612524.2976655727 4949902.019327216, 1612524.297667238 4949902.019326142, 1612524.297668659 4949902.019324761, 1612524.2976697795 4949902.019323127, 1612524.297670556 4949902.019321304, 1612524.2976709576 4949902.019319364, 1612524.297670969 4949902.019317382, 1612524.2976705895 4949902.019315437, 1612523.0232272441 4949897.779511995, 1612522.5135900173 4949896.084105058, 1612522.5135899826 4949896.084104935, 1612520.1865722018 4949887.476697587, 1612520.1865721773 4949887.476697498, 1612517.7823322963 4949878.8986931415, 1612517.782331557 4949878.89869126, 1612517.7823304546 4949878.898689567, 1612517.7823290343 4949878.898688129, 1612517.7823273537 4949878.898687007, 1612517.1003535376 4949878.536893573, 1612514.3841257987 4949875.63958398, 1612512.9343265232 4949873.827413355, 1612512.9343257917 4949873.827412537, 1612511.66630308 4949872.55729771, 1612511.3037624902 4949865.493014319, 1612511.3037621186 4949865.493012084, 1612509.4922274272 4949859.152640665, 1612508.767330085 4949855.34726788, 1612508.7673298283 4949855.34726684, 1612504.9625499095 4949842.845526215, 1612504.238054365 4949838.315586878, 1612504.4196325787 4949836.144113403, 1612505.5758999523 4949835.34744768, 1612505.5759015223 4949835.34744634, 1612505.5759027835 4949835.347444706, 1612505.5759036823 4949835.347442848, 1612505.5759041805 4949835.347440844, 1612505.5759042567 4949835.3474387815, 1612505.5759039077 4949835.347436747, 1612505.1327547848 4949833.76634822, 1612505.1327540686 4949833.766346385, 1612505.1327530064 4949833.766344725, '||
'1612505.1327516397 4949833.766343306, 1612505.132750021 4949833.766342183, 1612505.1327482136 4949833.7663413985, 1612505.1327462874 4949833.766340984, 1612505.1327443172 4949833.766340954, 1612505.1327423798 4949833.766341313, 1612500.0087109632 4949835.246938101, 1612499.999999141 4949833.0113034155, 1612499.999998956 4949833.011301539, 1612499.9999984219 4949833.01129973, 1612499.9999975576 4949833.011298054, 1612499.9999963942 4949833.01129657, 1612499.9999949727 4949833.011295331, 1612499.999993344 4949833.011294381, 1612499.9999915657 4949833.011293753, 1612499.5701146345 4949832.903867232, 1612499.570112807 4949832.903866951, 1612499.5701109592 4949832.903867012, 1612499.570109154 4949832.903867411, 1612332.6885689322 4949886.464173349, 1612332.6885668929 4949886.464174266, 1612332.6885651082 4949886.464175614, 1612332.6885636675 4949886.4641773235, 1612332.3128312742 4949887.027824378, 1612332.3128304565 4949887.027825864, 1612331.5613624363 4949888.718863602, 1612331.5613618207 4949888.718865458, 1612331.561361578 4949888.718867399, 1612331.561361718 4949888.718869349, 1612331.8870316627 4949890.621959667, 1612329.4981346398 4949891.390437311, 1612329.49813464 4949891.390437311, 1612329.4981327904 4949891.39043812, 1612329.498131141 4949891.390439284, 1612329.498129759 4949891.3904407555, 1612329.4981287008 4949891.390442475, 1612329.4981280093 4949891.390444371, 1612329.4981277129 4949891.390446369, 1612329.4981278235 4949891.390448384, 1612331.6861899188 4949905.308067472, 1612331.686189919 4949905.308067472, 1612331.6861906594 4949905.30806998, 1612337.986171594 4949919.483300679, 1612337.9861718942 4949919.483301297, 1612346.5503238584 4949935.659945988, 1612346.5503248665 4949935.65994753, 1612346.5503261406 4949935.65994886, 1612346.550327637 4949935.659949935, 1612346.5503293052 4949935.659950716, 1612346.5503310885 4949935.659951179, 1612346.5503329262 4949935.659951307, 1612346.5503347563 4949935.659951095, 1612365.956240631 4949931.57451634, 1612373.6575880218 4949945.849508198, 1612373.6575888467 4949945.849509482, 1612373.6575898577 4949945.849510625, 1612386.4368673943 4949958.254476118, 1612390.5710568763 4949976.6693648435, 1612390.5710574416 4949976.669366592, 1612391.698541782 4949979.300982044, 1612391.8866131937 4949983.433933367, 1612391.3236194993 4949987.188960429, 1612390.194169866 4949988.322196488, 1612387.0469123712 4949990.476230033, 1612387.0469108552 4949990.476231309, 1612387.0469096205 4949990.476232857, 1612387.0469087153 4949990.4762346195, 1612387.046908175 4949990.476236526, 1612387.0469080214 4949990.476238501, 1612387.04690826 4949990.476240467, 1612387.0469088817 4949990.476242349, 1612387.423641842 4949991.323474103, 1612381.3630180343 4949993.9591765925, 1612381.3630180345 4949993.9591765925, 1612381.3630170042 4949993.959177114, 1612378.702303411 4949995.502856269, 1612378.7023034107 4949995.502856269, 1612378.702301683 4949995.502857536, 1612378.702300265 4949995.502859144, 1612378.7022992221 4949995.5028610155, 1612372.903983685 4950009.182204972, 1612351.8583981518 4950020.832831716, 1612351.8583962126 4950020.832833117, 1612349.414746833 4950023.088351959, 1612349.4147455762 4950023.08835336, 1612349.4147446041 4950023.088354972, 1612349.4147439515 4950023.088356738, 1612349.4147436412 4950023.088358594, 1612349.2266783365 4950025.719517744, 1612348.662713838 4950033.2375448495, 1612346.2201414702 4950045.826125534, 1612343.2116520128 4950052.218134204, 1612326.6751141474 4950063.3049086565, 1612323.8567027529 4950063.681125215, 1612321.415211409 4950063.868924944, 1612319.6478427825 4950063.529948768, 1612319.6478409332 4950063.529948589, 1612319.6478390824 4950063.529948755, 1612319.6478372945 4950063.529949261, 1612319.647835631 4950063.529950089, 1612319.6478341494 4950063.52995121, 1612319.6478329008 4950063.529952587, 1612318.4054309344 4950065.18546395, 1612313.7081326174 4950069.694802342, '||
'1612309.0100455247 4950074.017106278, 1612309.010044676 4950074.017107161, 1612305.8152607656 4950077.775625582, 1612302.7276902203 4950081.050436133), (1612577.4612226172 4950169.171184227, 1612577.4743234555 4950169.174929047, 1612613.090467904 4950248.925765897, 1612611.355973458 4950250.097461703, 1612576.5989585689 4950169.761114943, 1612577.4612226172 4950169.171184227), (1612552.3481246177 4950177.6450913735, 1612552.3612103625 4950177.648812867, 1612589.427143694 4950259.457142527, 1612586.5180513188 4950259.738981499, 1612550.6099401454 4950178.8195660785, 1612552.3481246177 4950177.6450913735), (1612527.235158953 4950186.118358558, 1612527.2479602853 4950186.122239187, 1612566.066669277 4950269.100517044, 1612564.0327389643 4950269.381574221, 1612525.7893589304 4950187.001990109, 1612527.235158953 4950186.118358558), (1612484.88543452 4950095.0973148495, 1612484.8978037445 4950095.101689731, 1612523.4342271215 4950180.989262764, 1612521.9916516265 4950181.870405617, 1612483.7316950404 4950095.690477499, 1612484.88543452 4950095.0973148495), (1612498.658874648 4950192.9347000765, 1612498.6008893466 4950192.943744307, 1612457.7501397943 4950105.023844077, 1612459.4944369185 4950104.455610587, 1612500.3664662621 4950190.9275897555, 1612498.658874648 4950192.9347000765))'
) as geo FROM dual;

If your literal is outrageously long, you might want to write a procedure that splits up the string and formats it in the fashion explained above.


See message translations for ORA-01704 and search additional resources.

Monday, August 28, 2017

The amazing ANSI join syntax quirk

The kind of quirks I love: those that you can find a workaround for without having to wait for a patch.

I am talking Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production running on some unix flavor (I ignore which flavor, I've no access to the unix box).

You execute the following query and it works.

select f.cuaa,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 
       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,
       p.cod_na, p.fol, p.part
  from pcat c
  join cons d on (d.id_fgra = c.id_fgra)
  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)
  join fgra f on (f.id_fgra = d.id_fgra)
  join trkc k on (k.id_prt = c.id_part)
  join part p on (p.id_prt = k.id_prt)
where f.cua = 'AFVTZZ78M11C499O'
 and k.fin > sysdate
 and p.fin_val > sysdate
 and c.init < sysdate
 and c.fin > sysdate
 and c.valid < sysdate;

Then you execute:

insert into mp_part (cua, id_fgra, id_cons, id_part, id_prt, shape_sim, cod_na, fol, part)
select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 
       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,
       p.cod_na, p.fol, p.part
  from pcat c
  join cons d on (d.id_fgra = c.id_fgra)
  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)
  join fgra f on (f.id_fgra = d.id_fgra)
  join trkc k on (k.id_prt = c.id_part)
  join part p on (p.id_prt = k.id_prt)
where f.cua = 'AFVTZZ78M11C499O
 and k.fin > sysdate
 and p.fin_val > sysdate
 and c.init < sysdate
 and c.fin > sysdate
 and c.valid < sysdate;

SQL Error: No more data to read from socket
it caused a core dump.

Then you try 
create table test as
select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 
       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,
       p.cod_na, p.fol, p.part
  from pcat c
  join cons d on (d.id_fgra = c.id_fgra)
  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)
  join fgra f on (f.id_fgra = d.id_fgra)
  join trkc k on (k.id_prt = c.id_part)
  join part p on (p.id_prt = k.id_prt)
where f.cua = 'AFVTZZ78M11C499O'
 and k.fin > sysdate
 and p.fin_val > sysdate
 and c.init < sysdate
 and c.fin > sysdate
 and c.valid < sysdate;

and it works without a hitch.

It turns out that the problem is with the INSERT SELECT ANSI join syntax combined with a spatial function call in the projection list.

If I rewrite the query with the traditional Oracle syntax, it runs smoothly.


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles