oracle10g - Oracle subquery internal error -


the query in listing 1 joins 2 subqueries, both of computed 2 named subqueries (animal , sea_creature). output should list animals don't live in sea, , list animals live in sea.

when run in console window (sql navigator 5.5), server returns error:

15:21:30  ora-00600: internal error code, arguments: [evapls1], [], [], [], [], [], [], []

why? , how around it?

interesting note, can run same query in program written in delphi xe7 (using tsqlquery component), , works ok. not problem sql navigator. if create view containing expression in listing 1, selecting view not output error. problem in oracle server.

if make animal subquery simple, in listing 2, works. else, selecting table, results in internal error.

listing 1: (outputs error)

with animal (   select animal_name     xmltable( 't/e' passing xmltype( '<t><e>tuna</e><e>cat</e><e>dolphin</e><e>swallow</e></t>')                   columns                     animal_name varchar2(100) path 'text()')),  sea_creature (   select           'tuna' creature_name dual   union select 'shark'    dual   union select 'dolphin'  dual   union select 'plankton' dual)  select nonsea_animals, sea_animals   (    select stringagg( animal_name) nonsea_animals     (      (select * animal)           minus (select creature_name animal_name sea_creature))),    (select stringagg( animal_name) sea_animals     animal     animal_name in       (select creature_name animal_name sea_creature))

listing 2: (this works)

with animal (   select           'tuna' animal_name dual   union select 'cat'     dual   union select 'dolphin' dual   union select 'swallow' dual),  sea_creature (   select           'tuna' creature_name dual   union select 'shark'    dual   union select 'dolphin'  dual   union select 'plankton' dual)  select nonsea_animals, sea_animals   (    select stringagg( animal_name) nonsea_animals     (      (select * animal)           minus (select creature_name animal_name sea_creature))),    (select stringagg( animal_name) sea_animals     animal     animal_name in       (select creature_name animal_name sea_creature));

listing 3: expected output expressions in both listings 1 & 2:

nonsea_animals    sea_animals ------------------------------- 'cat,swallow'    'tuna,dolphin'

the oracle banner shown in listing 4.

listing 4: select * v$version

oracle database 10g enterprise edition release 10.2.0.4.0 - 64bi pl/sql release 10.2.0.4.0 - production core    10.2.0.4.0  production tns ibm/aix risc system/6000: version 10.2.0.4.0 - productio nlsrtl version 10.2.0.4.0 - production

how craziness explained?

update

here explain plan ...

plan_table_output

---------------------------------------------------------------------------- | id  | operation                            | name                        | ---------------------------------------------------------------------------- |   0 | select statement                     |                             | |   1 |  temp table transformation           |                             | |   2 |   load select                     |                             | |   3 |    view                              |                             | |   4 |     collection iterator pickler fetch| xmlsequencefromxmltype      | |   5 |   load select                     |                             | |   6 |    union-all                         |                             | |   7 |     fast dual                        |                             | |   8 |     fast dual                        |                             | |   9 |     fast dual                        |                             | |  10 |     fast dual                        |                             | |  11 |   nested loops                       |                             | |  12 |    view                              |                             | |  13 |     sort aggregate                   |                             | |  14 |      view                            |                             | |  15 |       minus                          |                             | |  16 |        sort unique                   |                             | |  17 |         view                         |                             | |  18 |          table access full           | sys_temp_0fd9d6666_765bccbd | |  19 |        sort unique                   |                             | |  20 |         view                         |                             | |  21 |          table access full           | sys_temp_0fd9d6667_765bccbd | |  22 |    view                              |                             | |  23 |     sort aggregate                   |                             | |  24 |      hash join right semi            |                             | |  25 |       view                           | vw_nso_1                    | |  26 |        view                          |                             | |  27 |         table access full            | sys_temp_0fd9d6667_765bccbd | |  28 |       view                           |                             | |  29 |        table access full             | sys_temp_0fd9d6666_765bccbd | ----------------------------------------------------------------------------

ora-03113 , ora-6000 happens on using with clause query when fatal happened on execution.

oracle's subquery factoring or with clause, can overused @ times. oracle may create global temporary table every query inside clause, reusing results. so, xmltable() here, have created gtt here, , perhaps crash database.

collection iterator pickler fetch when fetched pl/sl object. it returns pickled(packed , formatted) data

it might involve creation of temp table beneath mentioned previously. subquery factoring , pl/sql array selection didnt go well.

i have seen queries nested union all in with getting crashed.

this bug in oracle, , should reported them.

only way around now, reforming query. in our application, usage of with strictly restricted(due high cpu usage) report purposes executed batch.


-

Comments

Post a Comment

Popular posts from this blog

apache - PHP Soap issue while content length is larger -

we have developed soap based webservice post data third party webservice. accepts 4 params. in 1 of parameters accepts xml string no size limit restriction put in place. the webservice works fine in local environment running on php 5.2 , apache2. works content length long 10 mb. in staging server running nginx , php fpm.we have updated max post size , max upload filesize in php.ini , client_max_body param in nginx.conf allow upto 50 mb. but webservice client keeps on loading in staging environment. gives 502 timeout , gives soap error. can me understand issue here?
Read more

asynchronous - Python asyncio task got bad yield -

i confused how play around asyncio module in python 3.4. have searching api search engine, , want each search request run either parallel, or asynchronously, don't have wait 1 search finish start another. here high-level searching api build objects raw search results. search engine using kind of asyncio mechanism, won't bother that. # no asyncio module used here class search(object): ... self.s = some_search_engine() ... def searching(self, *args, **kwargs): ret = {} # raw searching according args , kwargs , build wrapped results ... return ret to try async requests, wrote following test case test how can interact stuff asyncio module. # here testing script @asyncio.coroutine def handle(f, *args, **kwargs): r = yield f(*args, **kwargs) return r s = search() loop = asyncio.get_event_loop() loop.run_until_complete(handle(s.searching, arg1, arg2, ...)) loop.close() by running pytest, return runtimeerror: task got bad yield : {results s...
Read more

javascript - Complete OpenIDConnect auth when requesting via Ajax -

the normal openidconnect server works like: you go a.com/secure-resource you 302 server your browser handles , sends identity server you login there it sends a.com via post you logged in on a.com , a.com/secure-resource on browser. however have scenario i'm trying solve need help. the user logged in on idserver the user logged in on a.com the user not logged in on b.com we need send ajax call web server b.com (from domain a.com ) b.com configured use openidconnect. but because request b.com via ajax, user cannot redirected idserver. (all in response 302 ) we can go ahead , handle 302 via ajax (i'm still not sure whether work, security-wise). but is there scenario in identityserver/openidconnect designed these situations? with identityserver in scenario setup server b.com use bearer token authentication, need use access token provided a.com in headers of ajax call $.ajax({ url: 'http://b.com', headers: { ...
Read more