APEX-Listener Bugs - ORDS, SODA & JSON in the Database

Hi,
as of now, we're seeing three major bugs in APEX-Listener which prevent us from using it for production:
1. It seems as if database version 10.2.0.1 is not supported. Support seems to start at 10.2.0.3. Due to some changes in the internal Oracle views, APEX Listener seems to be unable to work with earlier versions. As Oracle XE is 10.2.0.1, Oracle XE can't be used with the latest APEX-Listener
2. We encounter an issue with the APEX-Listener which is hard to reproduce. We get a
get_page FAILED:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
error. To us it seems to be a typo in APEX Listener that defines max http header size to be 32676 instead of 32767. There is an anonymous PL/SQL Procedure that is raised by APEX Listener with this limit set. Here's the code from catalina.out:
<pre>
declare
nlns number := 999999;
l_clob CLOB;
lines htp.htbuf_arr;
l_buff varchar2(32676);
l_clob_init boolean:= false;
l_file varchar2(5);
begin
OWA.GET_PAGE(lines, nlns);
if (nlns > 1) then
for i in 1..nlns loop
if ( length(lines(i)) > 0 ) then
if ( ( lengthb(l_buff) + lengthb(lines(i))) > 32676) then
               if (NOT l_clob_init) then
                    dbms_lob.createtemporary(l_clob, TRUE);
                    dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
                    l_clob_init:=true;
               end if;
dbms_lob.writeappend(l_clob,length(l_buff),l_buff);
l_buff := lines(i);
else
l_buff := l_buff || lines(i);
end if;
end if;
end loop;
end if;
if (l_clob_init) then
dbms_lob.writeappend(l_clob,length(l_buff),l_buff);
l_buff := '';
end if;
? := l_clob;
? := l_buff;
if (wpg_docload.is_file_download) then l_file:='TRUE'; else l_file := 'FALSE'; end if; ? := l_file;
? := sys_context('USERENV','SID');
end;
</pre>
Asa far as we can see, the error seems to happen, if by chance the amount of information passed between client and server falls into a critical size. Therefore, it may be that the typo above is responsible for that.
3. We also get an error if JQuery is used on the page and if the JQuery plugin adds form fields (at least this is how we could reproduce the following error). This seems to be solved with the new release, as people post in this forum, but as we can't connect to Oracle XE, we can't proof it.
This is the error here:
<pre>
init: # headers=48
declare nm owa.vc_arr := ?;
     vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:37
CALL:
begin
wwv_flow.accept(p_request=>?,
apexir_BETWEEN_TO=>?,
p_flow_id=>?,
p_md5_checksum=>?,
p_instance=>?,
apexir_BETWEEN_FROM=>?,
p_page_submission_id=>?,
p_ignore_10=>?,
p_flow_step_id=>?);
commit;
end;
BINDS
p_request:T_ADMINISTRATION
apexir_BETWEEN_TO:
p_flow_id:101
p_md5_checksum:
p_instance:3401284967103001
apexir_BETWEEN_FROM:SUCCEEDED
p_page_submission_id:2953302995156111
p_ignore_10:2586417839631836
p_flow_step_id:2000
p_request:T_ADMINISTRATION
apexir_BETWEEN_TO:
p_flow_id:101
p_md5_checksum:
p_instance:3401284967103001
apexir_BETWEEN_FROM:SUCCEEDED
p_page_submission_id:2953302995156111
p_ignore_10:2586417839631836
p_flow_step_id:2000
EXEC FAILED:ORA-06550: Zeile 2, Spalte 2:
PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'ACCEPT'
ORA-06550: Zeile 2, Spalte 2:
PL/SQL: Statement ignored
ORA-06550: Zeile 2, Spalte 2:
PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'ACCEPT'
ORA-06550: Zeile 2, Spalte 2:
PL/SQL: Statement ignored
</pre>
Could anybody from the developer team share some light with us on whether these observations are known and whether there are plans to overcome these issues?
Best regards,
Jürgen 

1) I'm not sure what views changed. We check all_args and all_object and I didn't think those changed.
2) The 676 vs. 767 is a typo on my part.
3) not sure if there's a question here or just stating it works now.
-kris 

I have tried ApexListener with 10.2.0.1 and get the following exception:
java.sql.SQLSyntaxErrorException: ORA-00904: "OBJECT_TYPE": invalid identifier
OBJECT_TYPE is a new column on "dba_procedures" and perhaps other views starting with 10.2.0.3.
Here is the stack dump from the glassfish server.log:
[#|2010-07-05T13:57:05.937-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|APEX Listener version : 1.10.179.10.43|#]
[#|2010-07-05T13:57:05.937-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|APEX Listener server info: GlassFish Server Open Source Edition 3.0.1|#]
[#|2010-07-05T13:57:05.953-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Using Config file:C:\DOCUME~1\Dan\LOCALS~1\Temp\\apex\apex-config.xml|#]
[#|2010-07-05T13:57:06.125-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|-- listing properties --|#]
[#|2010-07-05T13:57:06.125-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|PropertyCheckInterval=60|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|ValidateConnection=true|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|MinLimit=1|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|MaxLimit=10|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|InitialLimit=3|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|AbandonedConnectionTimeout=900|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|MaxStatementsLimit=10|#]
[#|2010-07-05T13:57:06.140-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|InactivityTimeout=1800|#]
[#|2010-07-05T13:57:06.859-0500|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|MaxConnectionReuseCount=50000|#]
[#|2010-07-05T13:57:09.171-0500|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|java.sql.SQLSyntaxErrorException: ORA-00904: "OBJECT_TYPE": invalid identifier
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
     at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
     at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke(PreparedStatementProxyFactory.java:111)
     at $Proxy97.executeQuery(Unknown Source)
     at oracle.dbtools.apex.Procedure.checkProcedureExist(Procedure.java:410)
     at oracle.dbtools.apex.Procedure.isProcedureExist(Procedure.java:384)
     at oracle.dbtools.apex.Procedure.resolveName(Procedure.java:284)
     at oracle.dbtools.apex.Procedure.<init>(Procedure.java:144)
     at oracle.dbtools.apex.Procedure.getProcedure(Procedure.java:64)
     at oracle.dbtools.apex.OWA.validateProcedure(OWA.java:307)
     at oracle.dbtools.apex.security.Security.isValidRequest(Security.java:121)
     at oracle.dbtools.apex.ModApex.validateRequest(ModApex.java:214)
     at oracle.dbtools.apex.ModApex.doGet(ModApex.java:75)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:734)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
     at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1523)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:641)
     at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
     at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
     at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
     at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
     at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
     at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
     at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
     at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
     at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
     at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
     at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
     at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
     at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
     at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
     at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
     at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
     at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
     at java.lang.Thread.run(Thread.java:619)
|#]
Regards,
Dan 

I am definitely seeing the 676 vs. 767 error as well. Just wondering if there was a time frame for a patch on that?
Thanks,
-Richard 

The 676 vs. 767 shouldn't cause an error. It's just a smaller buffer by a little.
-kris 

I see now that the 676 actually shouldn't be a problem in per say, but my problem does lie in that error snippet. I do get this error in my environment in a very specific, and almost always reproducible way. It's the reason I upgraded to the latest listener deployment and went through that whole OBJECT_TYPE mess, hoping it had been fixed. As it stands now, whatever this problem is does affect me.
App Server
CentOS 5.5
Glassfish 3.0.1
Apex Listener 1.10.179.10.43
http(8080) and https(8181)
iptables redirects 80 -> 8080, 443 -> 8181
Database Server
CentOS 5.5
Oracle 10g XE
Browser makes no difference. Tested in FF 3.6.6, IE8, Chrome 5.0.375.99.
Steps to reproduce for me:
url to http://my.host.com/apex/
login to my workspace
select applications -> application I am working on -> shared components -> report queries -> query
The query selected almost never comes up and instead I receive an http error 500 page.
If hit the browser back button, then click on the same or a different query, it brings up a partial page, which properly has the html header region with css, js, etc, and then when it starts the body does not contain anything except the actual edit regions "Report Query Attributes", "Source Queries", "Session State". There is no navigation, nor is there even a submit/save button. It would appear that I can repeat this indefinitely. It appears that no error is thrown on the partial pages, but the error that is shown in the server.log for the 500 error is the one above:
[#|2010-07-09T14:05:22.416-0400|SEVERE|oracle-glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=31;_ThreadName=Thread-1;|
***********ERROR***********
init: # headers=45
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:28
CALL:
begin
f(p=>?);
commit;
end;
BINDS
p:4000:1116:1217956095649366:::1116,1117,1112:P1116_ID,P1116_INIT:3424132466720998,YESPAGE CALL:
declare
nlns number := 999999;
l_clob CLOB;
lines htp.htbuf_arr;
l_buff varchar2(32676);
l_clob_init boolean:= false;
l_file varchar2(5);
begin
OWA.GET_PAGE(lines, nlns);
if (nlns > 1) then
for i in 1..nlns loop
if ( length(lines(i)) > 0 ) then
if ( ( lengthb(l_buff) + lengthb(lines(i))) > 32676) then
if (NOT l_clob_init) then
dbms_lob.createtemporary(l_clob, TRUE);
dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
l_clob_init:=true;
end if;
dbms_lob.writeappend(l_clob,length(l_buff),l_buff);
l_buff := lines(i);
else
l_buff := l_buff || lines(i);
end if;
end if;
end loop;
end if;
if (l_clob_init) then
dbms_lob.writeappend(l_clob,length(l_buff),l_buff);
l_buff := '';
end if;
? := l_clob;
? := l_buff;
if (wpg_docload.is_file_download) then l_file:='TRUE'; else l_file := 'FALSE'; end if; ? := l_file;
end;
get_page FAILED:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32
^^^^^^^^^^^^ERROR^^^^^^^^ 

Can you turn debugging on in the listener and post/send me the log from that.
In the apex-config.xml add a property:
<entry key="apex.debug.debugger">true</entry>
-kris 

I keep the debugger on. In addition to the below, I have truncated the glassfish server log and it is available at: https://apex.vendelinc.com/server.log.tmp
These logs are basically from a reboot and start of glassfish through to APEX failure on 4 different clicks on the report queries. Not to say that code it shows the error in is necessarily the culprit, but tell me this, it would almost appear like it could be the size of the bind variable buffer on the receiving end of these statements or am I reading that wrong?
STATS ======================================
Current Time:     Mon Jul 12 11:52:50 EDT 2010
Server Uptime:     0 Days 00:01:55.865
Total Accesses:     17.0
Total Traffic:     289.52K
Total Errors:     4
Total File Downloads:     0
Total Processing Time:     0 Days 00:00:03.736
Processing Time in milliseconds: Minimum: 57 Average: 220 Median: 130 Maximum: 1637
Database Time in milliseconds: Minimum: 12 Average: 85 Median: 58 Maximum: 182
Active Requests:     0
Connections in the pool apex: Active: 0 Available: 1
Security: Total Entries: 0 Maximum Entries Allowed: 2000
Caching Files: Caching file is disabled.
ERROR TRACKING =============================
4 error messages
--- 7/12/10 11:52 AM ( 12 Seconds Ago ) ---
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32
--- 7/12/10 11:52 AM ( 14 Seconds Ago ) ---
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32
--- 7/12/10 11:52 AM ( 17 Seconds Ago ) ---
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32
--- 7/12/10 11:52 AM ( 22 Seconds Ago ) ---
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32
LOGGING ====================================
17 logs
07/12/10 11:52:39 Procedure f FOUND Args 15 DBTime 48 ms ProcessingTime 62 ms
07/12/10 11:52:38 Procedure f FOUND Args 15 DBTime 75 ms ProcessingTime 84 ms
07/12/10 11:52:37 Procedure f FOUND Args 15 DBTime 58 ms ProcessingTime 73 ms
07/12/10 11:52:35 Procedure f FOUND Args 15 DBTime 83 ms ProcessingTime 90 ms
07/12/10 11:52:34 Procedure f FOUND Args 15 DBTime 57 ms ProcessingTime 72 ms
07/12/10 11:52:33 Procedure f FOUND Args 15 DBTime 161 ms ProcessingTime 170 ms
07/12/10 11:52:31 Procedure f FOUND Args 15 DBTime 47 ms ProcessingTime 62 ms
07/12/10 11:52:28 Procedure f FOUND Args 15 DBTime 122 ms ProcessingTime 136 ms
07/12/10 11:52:26 Procedure f FOUND Args 15 DBTime 174 ms ProcessingTime 189 ms
07/12/10 11:52:25 Procedure f FOUND Args 15 DBTime 58 ms ProcessingTime 73 ms
07/12/10 11:52:23 Procedure f FOUND Args 15 DBTime 124 ms ProcessingTime 142 ms
07/12/10 11:52:22 Procedure f FOUND Args 15 DBTime 108 ms ProcessingTime 130 ms
07/12/10 11:52:18 Procedure f FOUND Args 15 DBTime 182 ms ProcessingTime 197 ms
07/12/10 11:52:18 Procedure wwv_flow.accept ADD 218 ms Args 308 DBTime 42 ms ProcessingTime 294 ms
07/12/10 11:50:56 Procedure f FOUND Args 15 DBTime 41 ms ProcessingTime 57 ms
07/12/10 11:50:56 Procedure f ADD 199 ms Args 15 DBTime 56 ms ProcessingTime 268 ms
07/12/10 11:50:54 Procedure apex ADD 569 ms Args 1 DBTime 12 ms ProcessingTime 1637 ms 

I see page 1116 has some anonymous blocks so I'm checking there. I can't see how line 32 in this block would/could ever result in that error.
-kris 

As is always the case it's working here, so I'll need a little more help.
Are there other languages or is this English?
Are the quieries themselves large?
Are there lots of queries?
If you try this with a NO for the init does it succeed?
p=4000:1116:840307125879106:::1116,1117,1112:P1116_ID,P1116_INIT:3426243641780950,YES
p=4000:1116:840307125879106:::1116,1117,1112:P1116_ID,P1116_INIT:3426243641780950,NO
-kris 

1. English
2. select PKID,
EMAIL,
LASTNAME,
FIRSTNAME,
REGISTERED,
CREATED,
MODIFIED,
STATUS
from RPTS_USERS
3. 2x Report Query objects, each containing the same single query above.
Extra - This app was only ever created in Apex 4.0 locally after install of the production release. All objects in that app have been created, not imported...so it shouldn't be like a hanging config thing.
4. If I try the Query link, then I switch it to 'NO' instead, I get the query edit page without all of the tab navigation, breadcrumbs, apply button, etc like I described above. Hitting refresh on this page will make it alternate between 500 error and the partial page described above.
I think I have it narrowed down to being something to do with the Workspace Administrator permission or a combination of fields affected by it. I know it sounds odd. I was adding a set of credentials in case you would like to look. Like any good admin I only gave that user the permissions to actually get in (workspace admin = No) and like any good patient, I wanted to make sure that the symptoms showed up when you logged in. Well lo and behold the problem did not show up at all. So I tested some different combinations and figured out that when I took away the admin rights from a new user I created I didn't see the problem, but when I add them back here it comes. Well there went that idea. After I edited and saved now I can't reach it as my under-privileged developer.
If you want to check it out, I have 2 sets of creds for you. Email at richard.e.wendel {at} gmail and I can forward them on. One set of admin, one set of non-admin.
Edited by: rwendel on Jul 12, 2010 8:31 PM 

I am getting this error too. Very consistently on certain page/ parameter combinations in our appliction.
--Tomcat 6 on Windows XP
--Oracle XE E on CentOS 5
--Apex Listenser from apex_listener.1.10.179.10.43.zip
The page that consistently gives this error has a Tabular Form with a number of updatable Textarea cells.
The same page works fine when connecting via EPG(on XE) or OHS (/pls/apex).
I am watching this thread to get a resolution whenever it is published.
If I can help in any way by providing trace, debug,etc. just let me know.
------------------
Well, after the above post I kept tinkering and here is what I found. Not sure if it will help, but still...
Here is what I did and the results I got:
1. On the page from which I was navigating to error prone page I turned on DEBUG , i.e. tweaked the URL from NO to YES
2. With DEBUG ON I navigated to the error prone page. It displayed correct! But of course with all the debug lines.
3. Strange, Tomcat did not report the error. Instead it listed out H:...stuff followed by Cookie:....; but no error on line 32.
4. On I again tweaked the URL (on the error prone page) and made DEBUG NO
5. I got Unable to bind "page item name1" Unable to bind "page item name2"...in the regions
6. This time Tomcat reported the "....ORA-06512: at line 32..." error
Tried the above steps a few times, the behaviour is consistent as described above.
Edited by: user11972291 on Jul 14, 2010 7:41 PM 

I didn't forget. I'm still looking....
-kris 

No worries. If you want to get in and look or w/e you are welcome to. It is a test server I am setting up for a new JasperReports integration service...this one uses all the APEX delivered stuff so it's more or a drop-in replacement...normally I wouldn't worry about these type of issues except I am on to testing and in my environment it just happens to affect the query and layout objects.
It seems that when I log out, clear cookies, etc, login and try I do get to whatever object I was trying to, and can edit once, but then after that it seems to go away. I figure I will probably download the Apache+Modplsql and just make sure that APEX 4.0 (or my installation of it) isn't to blame.
-Richard 

Hi Kris,
thanks for looking into this.
The problem with the view is because of a change between version 10.2.0.1 and 10.2.0.3 on the all_objects view. There is an additional view added to the view in 10.2.0.3.
We think that the 676 typo might cause problems, as the error seems to happen only with certain amount of information floating around. I have to exact same users with same usergroups and content. User one is named DIOPDE and user two is named NACHTWEY and DIOPDE receives this error and user NACHTWEY doesnt. Our idea is that for some reason the amount of information which remains aftter splitting a larger area by 32767 falls into a region between 32676 and 32767. This then causes this error to happen.
Shurely this is only a guess, but to us this would explain why we get this "buffer overflow" error.
What happens now is that with the latest version of the APEX-LISTENER and a respective patch of the Express Edition database regarding the view we sometimes get the following error:
<pre>
init: # headers=44
declare nm owa.vc_arr := ?;
     vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:131
CALL:
begin
f(p=>?);
commit;
end;
BINDS
p:1000:RULE_DETAIL:1844511190868286::NO::P201_RULE_ID:AB_FREI_REGEL
EXEC FAILED:Keine weiteren Daten aus Socket zu lesenKeine weiteren Daten aus Socket zu lesen
Keine weiteren Daten aus Socket zu lesen
</pre>
Plus, we could see that the system is running correctly if the language is set to German but raising errors if we set it to English.
Best regards,
Jürgen

Related

APEX Listener EA2 Standalone CLOB error

I'm testing the APEX Listener EA2 release in Standalone mode on CentOS against Oracle XE.
My RESTful service calls use the Media Resource type to return a CLOB that I format myself inside a function:
select 'application/json', my_function_that_returns_json_in_a_clob from dual
In the EA2 release, this is generating an error if the function returns more than 4000 bytes (works with 4000, fails with 4001). It is as though the function is getting cast as a varchar2 somewhere. I verified that the function will correctly return a large result to other sources (dbms_output, for instance).
The APEX Listener Log reports the error as:
oracle.dbtools.rt.web.HttpEndpointBase restfulServices
SEVERE: ORA-22922: nonexistent LOB value
Here's a Test Case:
create or replace function test_size(p_cnt in integer) return clob
is
l_clob clob;
begin
for i in 1..p_cnt loop
l_clob:=l_clob||'a';
end loop;
return l_clob;
end;
Then create a RESTful service call:
Method: "GET"
Source Type: "Media Resource"
Source: select 'application/json', test_size(4001) from dual
Thanks,
Tim 
Hi Tim,
I do not use 2.0 EA but since your query looked interesting I tried out your scenario on 1.1.4 on GlassFish and encountered the same error that you have reported.
It appears that you hit a ceiling of 4000 for VARCHAR2 as well as CLOB with Media Resource.
The good news is that "So what is the battle is lost, all is not lost..".
Here is a work around that , at least in my environment , seems to break through the barrier. I tried it till a value of 200000 successfully. Stopped at this point as it started taking too long to run it !
I changed the function to return BLOB as follows
create or replace function test_size(p_cnt in integer) return BLOB
as
l_blob BLOB ;
l_ret  BLOB ;
l_clob CLOB;
l_warning  integer;
l_size integer := DBMS_LOB.LOBMAXSIZE;
l_dest_offset  integer := 1;
l_src_offset   integer := 1;
l_blob_csid    number := dbms_lob.default_csid;
l_lang_context integer := dbms_lob.default_lang_ctx;
begin
  dbms_lob.createtemporary(l_blob, true);
  for i in 1..p_cnt loop
    l_clob := l_clob||'*';
  end loop;
  DBMS_LOB.CONVERTTOBLOB(
   l_blob,
   l_clob,
   l_size,
   l_dest_offset,
   l_src_offset ,
   l_blob_csid,
   l_lang_context,
   l_warning
  );
   l_ret := l_blob;
   dbms_lob.freetemporary(l_blob);
  return l_ret;
end;Cheers,
PS: BTW, if you intend to put this into Production then it just might be worth considering the QUERY type. It may mean creating a few queries to be able to construct the required SELECT statement but most probably be worth the extra effort for the following reasons:
a. No risk of ending up with a malformed JSON
b. SELECT will consume less resources than an equivalent PL/SQL block , presumably with Explicit CURSORs
c. Will be faster as well.
Edited by: Prabodh on Oct 12, 2012 5:23 PM 
Thanks for your thorough reply!
Unfortunately, your workaround doesn't fix the problem with EA2. It encounters the 4000 character limit as well.
I understand your advice regarding using the QUERY type. I chose the Media Resource strategy for two main reasons:
1. These services calls are for our mobile developers. Because they use some predefined frameworks for managing data that comes to the mobile app, they wanted the json to distinguish between data sets (rather than every data set being called "items"). I am able to format the json in such a way that it doesn't confuse their code. Is there another way to customize the format of the default json created by the QUERY type?
2. I need to conduct some other business as part of the call. For instance, the service passes in a user's credentials which I use to authenticate and then retrieve the appropriate results based on who they are. Currently, I have stored procedures that authenticate APEX users or LDAP users.
Perhaps old habits die hard, but I find that if I just do all of this myself in a stored function, I have all of the control I need. If you have other recommended strategies, I'm all ears.
Thanks!
_Tim                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Hmmm.
That it does not work with 2.0 EA is really odd. Or, maybe it has to do with the fact that I use GF as the J2EE Container.
Will try with 2.0 EA Standalone in a day or two and check out what happens.
Cheers, 
Thanks for your detailed report, I'll look into it.
Colm 
I'm experiencing the same issue (4000 limit) with both CLOB and BLOB approach on APEX 4.2.1 with Listener 2.0.0.354 - tested on Tomcat 7 and standalone.
However I found this workaround to raise the limit to 32k:
1) Switch Handler's Source type = "Media Resource" to "PL/SQL"
2) Replace SELECT with PL/SQL code to send CLOB using the sys.htp.p procedure
However once the CLOB is over 32k you will get the same "java.sql.SQLException: ORA-22922: nonexistent LOB value"
Jan 
Jan,
the Media Resource issue is fixed in 2.0.1 and it should also address the HTP.PRN > 32Kb issue
http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html 
Hi Colm,
I'm happy to report the Media Resource works fine for both CLOBs and BLOBs - thanks!
However I have no luck with the HTP.PRN > 32Kb. At least the error is different...
Jan
-----
Mar 06, 2013 8:51:44 PM oracle.dbtools.rt.web.WebErrorResponse internalError
SEVERE: oracle.jdbc.driver.OracleClobReader cannot be cast to java.io.InputStream
java.lang.ClassCastException: oracle.jdbc.driver.OracleClobReader cannot be cast to java.io.InputStream
at oracle.dbtools.rt.plsql.OracleWebAccess.response(OracleWebAccess.java:112)
at oracle.dbtools.rt.plsql.AnonymousBlockGenerator.generate(AnonymousBlockGenerator.java:186)
at oracle.dbtools.rt.resource.templates.v2.ResourceTemplatesDispatcher$HttpResourceGenerator.response(ResourceTemplatesDispatcher.java:309)
at oracle.dbtools.rt.web.RequestDispatchers.dispatch(RequestDispatchers.java:88)
at oracle.dbtools.rt.web.HttpEndpointBase.restfulServices(HttpEndpointBase.java:412)
at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:162)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.doFilter(ServletAdapter.java:1059)
at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.invokeFilterChain(ServletAdapter.java:999)
at com.sun.grizzly.http.servlet.ServletAdapter.doService(ServletAdapter.java:434)
at oracle.dbtools.standalone.SecureServletAdapter.doService(SecureServletAdapter.java:65)
at com.sun.grizzly.http.servlet.ServletAdapter.service(ServletAdapter.java:379)
at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
at com.sun.grizzly.tcp.http11.GrizzlyAdapterChain.service(GrizzlyAdapterChain.java:196)
at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Unknown Source) 
Confirmed here, too. With APEX Listener 2.0.1, HTP.PRN > 32Kb produces:
[#|2013-03-22T15:23:43.046-0400|SEVERE|glassfish3.1.2|null|_ThreadID=77;_ThreadName=Thread-2;|oracle.jdbc.driver.OracleClobReader cannot be cast to java.io.InputStream
java.lang.ClassCastException: oracle.jdbc.driver.OracleClobReader cannot be cast to java.io.InputStream

PLS-00306 with Apache tomcat/Apex Listener

Hi together,
I have some Problems with the Apex Listener.
The system consists of:
ORACLE 11.1.0
APEX 4.0
APEX Listener version 0.10.110.10.57
apache-tomcat-6.0.26
jre 1.6
Always I try to save/edit something or choose another tab I get the error:
HTTP Status 500 - System Unavailable. Please try again later.
type Status report
message System Unavailable. Please try again later.
description The server encountered an internal error (System Unavailable. Please try again later.) that prevented it from fulfilling this request.
This is not the case, if I only use the application builder. I think, the error occurs only if I am working in a running application.
here is the stdout_log:
APEX Listener version 0.10.110.10.57
Using Config file:D:\Tomcat 6.0\temp\apex\apex-config.xml
-- listing properties --
PropertyCheckInterval=60
ValidateConnection=true
MinLimit=1
MaxLimit=10
InitialLimit=3
AbandonedConnectionTimeout=900
MaxStatementsLimit=10
InactivityTimeout=1800
init: # headers=47
declare nm owa.vc_arr := ?;
     vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:142
CALL:
begin
wwv_flow.accept(fmap=>?,
p_md5_checksum=>?,
fhdr=>?,
p_flow_id=>?,
p_flow_step_id=>?,
p_request=>?,
fcud=>?,
f04=>?,
fcs=>?,
f03=>?,
f02=>?,
f01=>?,
p_page_submission_id=>?,
p_instance=>?);
commit;
end;
BINDS
fmap:ID
p_md5_checksum:
fhdr:Id
p_flow_id:102
p_flow_step_id:51
p_request:SUBMIT
fcud:U
f04: {NEXTNEXTNEXTNEXTNEXT}
fcs: {00A5E05CA539579980E89DED70BC0C693DC38654F6C9278859509EA539207F23A78008F7DB33ADE672F4330576F4C7F4CE2A2C7C39741E4C22B5598071724449075324B645A65925F6CCE709B0AA8A31}
f03: {CANCELCANCELCANCELCANCELCANCEL}
f02: {Manual_DeactivatedManual_ActivatedManual_ActivatedManual_ActivatedManual_Activated}
f01: {27682769277027712772}
p_page_submission_id:1434186759017069
p_instance:4298631303140632
fmap:ID
p_md5_checksum:
fhdr:Id
p_flow_id:102
p_flow_step_id:51
p_request:SUBMIT
fcud:U
f04: {NEXTNEXTNEXTNEXTNEXT}
fcs: {00A5E05CA539579980E89DED70BC0C693DC38654F6C9278859509EA539207F23A78008F7DB33ADE672F4330576F4C7F4CE2A2C7C39741E4C22B5598071724449075324B645A65925F6CCE709B0AA8A31}
f03: {CANCELCANCELCANCELCANCELCANCEL}
f02: {Manual_DeactivatedManual_ActivatedManual_ActivatedManual_ActivatedManual_Activated}
f01: {27682769277027712772}
p_page_submission_id:1434186759017069
p_instance:4298631303140632
EXEC FAILED:ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'ACCEPT'
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'ACCEPT'
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'ACCEPT'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'ACCEPT'
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'ACCEPT'
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'ACCEPT'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
Can anybody help me solving this problem?
Or is this the wrong constellation with tomcat?
Thanks in advance!
Anja
Edited by: Anja Zahn on Jul 14, 2010 3:41 AM
Edited by: Anja Zahn on Jul 14, 2010 3:41 AM 
First can you download the latest apex listener and try again?
-kris 
Is the new listener supported on Apache tomcat? According to the installation instructions its not listed as one of the supported Application Servers? 
I think user618859 is right. Its not documented with tomcat! Should I try anyway? 
Hi,
Its not supported is that you cannot raise a e-tar, but it runs on Tomcat . You will find related info here http://www.oracle.com/technology/products/database/application_express/html/apex_listener_install_web.html
Regards, 
so, I installed the new version of Apex Listener with tomcat. It works, but I still get some strange error messages concerning checksums. And I can't explain why.
I also installed the new version of Apex Listener with OC4J. This works fine :) 
Kind of the same way support is for any oracle product on virtualization technology that isn't Oracle's own...if they can't reproduce it in the supported environment, then you are out of luck. I don't blame them for the replacement of Tomcat with Glassfish. Glassfish is a very fine j2ee server and now that it is Oracle's they would love to promote it more.

IMP-00003: ORACLE error 1659 encountered

Hi,
I have created three data files and each is having size 25 GB and it is autoextended to 1 GB.
During dump (size 30 GB) import in oracle database i am geeting following error message.
IMP-00017: following statement failed with ORACLE error 1659:
"CREATE INDEX "IDX$$_055E0001" ON "LXHIST_DFDAEE54" ("LXOID" , "LXKIND" ) P"
"CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 4294967294 NEXT 1048576 F"
"REELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) L"
"OGGING"
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 14 in tablespace CARRARO
IMP-00017: following statement failed with ORACLE error 3113:
"BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_IMP_SKIP_INDEXES_OFF; END;"
IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00000: Import terminated unsuccessfully
My Dump file size is 30 GB
Total size of disk is 195 GB and free space size is 98.9 GB
Could you please provide a solution for this issue.
-Arun
Edited by: 862164 on May 28, 2011 1:28 PM 
Hi Arun,
first: Are you sure you are in the right forum? If so, you need to now that Oracle XE comes along with some limitations. One of them is the amount of user data that can be stored in a XE instance: In the 11g XE this limit is set to 11 Gigabytes. See the [url http://download.oracle.com/docs/cd/E17781_01/license.112/e18068/toc.htm]Licensing Information for more details.
If you don't try to import to a XE instance, you should post in {forum:id=732}, as this forum here is dedicated to the Beta of Database 11g Express Edition.
Anyway, XE or some other Edition, on of the error messages tells you the root cause:
ORA-01659: unable to allocate MINEXTENTS beyond 14 in tablespace CARRAROWhich means: You need to extend tablespace CARRARO. A dump file can be smaller than the actual amount of space needed to import it. This can be due to the fact that objects like indexes use up more space, that you enabled compression for export, and, last but not least, that you exported storage paramters for all objects, e.g. look at
"CREATE INDEX "IDX$$_055E0001" ON "LXHIST_DFDAEE54" ("LXOID" , "LXKIND" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 4294967294 NEXT
{code}
The part *STORAGE(INITIAL 4294967294* tells you the inital amount of space reserved for that index, which is about 4 GBytes.
If you post again in the recommended forum for such issues, please provide additional information on your Database Version and the state of tablespace (amount of free space after the import stopped), and of course information on the file system your data files are using...
-Udo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

"Application Builder" page in APEX causes error in standalone listener

Whenever anyone tries to view the "Application Builder" in our APEX install which is using the standalone listener (v. 1.1.3.243.11.40), they get a blank page.
It seems to be the same error as described at Page get blank when click application builder - but we are using the standalone listener, and have restarted the listener to no avail.
-----
init: # headers=46
declare nm owa.vc_arr := ?;
vl owa.vc_arr := ?;
begin
owa.init_cgi_env( ?, nm, vl );
htp.init; htp.HTBUF_LEN := 63;
? := sys_context('USERENV','SID');
end;
SID:491
CALL:
begin
f(p=>?);
commit;
end;
BINDS
p:4000:1500:1458736111892021::NO:::
EXEC FAILED:No more data to read from socketNo more data to read from socket
No more data to read from socket
Edited by: user3749268 on Dec 14, 2011 10:15 AM 
Problem solved - turns out it was related to Metalink ID 418531.1 - an opidsa segfault in 10.2.0.3.

BICS Sample Application: Public Donations Data Samples V507 issues and solutions!

HiI have just installed the Public Donations Data Samples (v507) Application into my Oracle BI Cloud Services environment and I ran into a couple of problems. I thought if I noted down here the problems I faced and how I solved them I might help others out. Sample URL - BICS Samples It's not clear in the notes currently what the password is when you install the snapshot file - Donations_507_31.barThe password is 'Admin123' The DDL File that comes in the zip file DONATIONS 507_31 DDL.sql has an error that needs to be corrected. This is the code here
CREATE OR REPLACE FORCE VIEW  "V_POSTED_DATE"  AS
  SELECT
 
  DAY_DT as DAY_DT,
  EXTRACT(YEAR FROM DAY_DT) YR,
  FLOOR(EXTRACT(MONTH FROM DAY_DT)/3.1+1) QTR_NUM,
TO_CHAR(DAY_DT, 'YYYY-')||'Q'||TO_CHAR(DAY_DT, 'Q') QTR, 
EXTRACT(MONTH FROM DAY_DT) MTH_NUM, 
TO_CHAR(DAY_DT, 'YYYY-MM') MTH, 
TO_CHAR(DAY_DT, 'YYYYMM') MTH_ID, 
TO_CHAR(DAY_DT, 'MON') MTH_NAME 
--,TO_CHAR(DAY_DT, 'DAY') DAY_NAME 
--,TO_CHAR(DAY_DT, 'D') DAY_OF_WEEK
--,TO_CHAR(DAY_DT, 'DD') DAY_OF_MTH 
--,TO_CHAR(DAY_DT, 'WW') WEEK_NUM   
FROM  (SELECT TO_DATE(TO_CHAR(MAX(DATE_COMPLETED),'DD-MON-YYYY'),'DD-MON-YYYY')+10 AS MAXD, MAX(DATE_COMPLETED)-MIN(DATE_POSTED)+300 AS RNGDT  FROM DONATION_PROJECTS)  
MODEL PARTITION BY (1 AS PART) DIMENSION BY (1 AS DIM)  
MEASURES ( 
MAXD AS MAX_DT,   RNGDT AS RNGDT ,  CAST(NULL AS DATE) DAY_DT ,  CAST(NULL AS NUMBER) ID , CAST(NULL AS NUMBER) SEQUENCE )   
IGNORE NAV RULES     UPSERT ALL 
( ID[FOR DIM FROM 1 TO RNGDT[DIM = 1] INCREMENT 1]=ID[CV()-1]+1 ,SEQUENCE[ANY] = 0-ID[CV()] ,DAY_DT[ANY]= MAX_DT[DIM = 1] + SEQUENCE[CV()] )
;
 
Make sure you remove the commented out lines
--,TO_CHAR(DAY_DT, 'DAY') DAY_NAME  
--,TO_CHAR(DAY_DT, 'D') DAY_OF_WEEK
--,TO_CHAR(DAY_DT, 'DD') DAY_OF_MTH  
--,TO_CHAR(DAY_DT, 'WW') WEEK_NUM
If you don't remove them you receive an invalid character error when you run the DDL script and the view doesn't get created. You should get 4 errors when you run that DDL file for the first time. The script drops the tables before creating them so in a clean environment the 4 errors of "ORA-00942: table or view does not exist" is expected.  If you don't fix this up you will get the following error on the Dashboard from the Overview_2 Analysis as it tries to use the V_POSTED_DATE view
"Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSErro
Please have your System Administrator look at the log for more details on this error. (HY000)
Please have your System Administrator look at the log for more details on this error."
 The other thing worth noting is that when I went to load up the data using datasync it took 40 minutes to load. Admittedly I'm living in the most isolated city in the world - Perth, Western Australia and our cloud server is in the US so that probably contributed. But I suggest kicking it off before your lunch break =) I hope this helps!Kylie Payne

Categories

Resources