Apex listener connection reset issues - Weblogic Server

Apex listener connection reset issues - Weblogic Server - ORDS, SODA & JSON in the Database

Hi,
We are experiencing connection reset issues with apex listener 1.0 every time the server stays without an active connection for some time (Error : HTTP 500 on the browser when we try to load the application - After the error, we refresh the page and everything is back to normal.)
We have other java applications that are deploy on this server and we don't have any "connection reset" problem on those applications.
Is there a parameter that can be initialize in the apexConfig.xml so that we don't have this kind of error every once a will. Or is there another solution to correct this problem.
Here is what we have in the server log when this error happens:
/****************************************************************************************************************************/
==== doGet()====
isValidRequest(), procedure name: <f>
Validating:f
*** Found procedure in cache: f*** Total number of arguments: 12_init_: # header
s=42
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;
INIT FAILED:Exception d'E/S: Connection reset
nm(1) := 'Accept';
vl(1) := 'image/gif, image/jpeg, image/pjpeg, image/pjpeg, application/x-shockwa
ve-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/m
sword, application/x-ms-application, application/x-ms-xbap, application/vnd.ms-x
psdocument, application/xaml+xml, */*';
nm(2) := 'User-Agent';
vl(2) := 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.5
; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1; InfoPath.2; .NET CLR 3.0.45
06.2152; .NET CLR 3.5.30729)';
nm(3) := 'Accept-Encoding';
vl(3) := 'gzip, deflate';
nm(4) := 'Cookie';
vl(4) := 'EnapAlert=datealert=';
nm(5) := 'Host';
vl(5) := 'XXXXXXXXXXXX';
nm(6) := 'Connection';
vl(6) := 'Keep-Alive';
nm(7) := 'Accept-Language';
vl(7) := 'fr-ca';
nm(8) := 'APEX_LISTENER_VERSION';
vl(8) := '1.10.179.10.43';
nm(9) := 'DAD_NAME';
vl(9) := '';
nm(10) := 'DOC_ACCESS_PATH';
vl(10) := '';
nm(11) := 'DOCUMENT_TABLE';
vl(11) := '';
nm(12) := 'GATEWAY_IVERSION';
vl(12) := '3';
nm(13) := 'GATEWAY_INTERFACE';
vl(13) := 'CGI/1.1';
nm(14) := 'HTTP_ACCEPT';
vl(14) := 'image/gif, image/jpeg, image/pjpeg, image/pjpeg, application/x-shockw
ave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/
msword, application/x-ms-application, application/x-ms-xbap, application/vnd.ms-
xpsdocument, application/xaml+xml, */*';
nm(15) := 'HTTP_ACCEPT_ENCODING';
vl(15) := 'gzip, deflate';
nm(16) := 'HTTP_ACCEPT_LANGUAGE';
vl(16) := 'fr-ca';
nm(17) := 'HTTP_ACCEPT_CHARSET';
vl(17) := 'null';
nm(18) := 'HTTP_COOKIE';
vl(18) := 'EnapAlert=datealert=';
nm(19) := 'HTTP_IF_MODIFIED_SINCE';
vl(19) := 'null';
nm(20) := 'HTTP_IF_NONE_MATCH';
vl(20) := 'null';
nm(21) := 'HTTP_HOST';
vl(21) := 'XXXXXXXXXXX:443';
nm(22) := 'HTTP_ORACLE_ECID';
vl(22) := '';
nm(23) := 'HTTP_PORT';
vl(23) := '443';
nm(24) := 'HTTP_REFERER';
vl(24) := 'null';
nm(25) := 'HTTP_USER_AGENT';
vl(25) := 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB6.
5; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1; InfoPath.2; .NET CLR 3.0.4
506.2152; .NET CLR 3.5.30729)';
nm(26) := 'PATH_ALIAS';
vl(26) := ' ';
nm(27) := 'PATH_INFO';
vl(27) := '/f';
nm(28) := 'PLSQL_GATEWAY';
vl(28) := 'WebDb';
nm(29) := 'QUERY_STRING';
vl(29) := 'p=119:1';
nm(30) := 'REMOTE_ADDR';
vl(30) := 'XXX.XXX.XXX.XXX';
nm(31) := 'REMOTE_USER';
vl(31) := 'APEX_PUBLIC_USER';
nm(32) := 'REQUEST_CHARSET';
vl(32) := 'AL32UTF8';
nm(33) := 'REQUEST_IANA_CHARSET';
vl(33) := 'UTF-8';
nm(34) := 'REQUEST_METHOD';
vl(34) := 'GET';
nm(35) := 'REQUEST_PROTOCOL';
vl(35) := '/1.1';
nm(36) := 'SCRIPT_NAME';
vl(36) := '/apex';
nm(37) := 'SCRIPT_PREFIX';
vl(37) := '';
nm(38) := 'SERVER_NAME';
vl(38) := 'XXXXXXXX';
nm(39) := 'SERVER_PORT';
vl(39) := '443';
nm(40) := 'SERVER_PROTOCOL';
vl(40) := 'HTTP/1.1';
nm(41) := 'SERVER_SOFTWARE';
vl(41) := 'Mod-Apex';
nm(42) := 'WEB_AUTHENT_PREFIX';
vl(42) := ' ';
Exception d'E/S: Connection reset
Connexion interrompue
Got results length:NULL
/***************************************************************************************************************************/
Here is what we have in the apexListener error log :
/***************************************************************************************************************************/
--- 10-07-24 09:29 ( 50 Minutes 17 Seconds Ago ) ---
Exception d'E/S: Connection reset
/***************************************************************************************************************************/
Thanks for your help...

user8897870,
This is probably an issue with your database and some resource limiting on the connections causing the timeouts.
-kris

Has anyone come up with a fix for this, or something else to try???
We are having the same issue, every morning when I access apex (and various times of the day), I will get the "Server Unavailable" error. I have looked in the logs, and I am not finding the error that was listed above. According to our WebLogic admin, apex has it's own logging system and it is returning the following error:
--- 9/13/10 7:38 AM ( 5 Hours 22 Minutes 25 Seconds Ago ) ---
Io exception: Connection reset by peer: socket write error
Our DBA tried updating the profile with a 24-hour limit on the APEX_PUBLIC_USER account, but that hasn't seemed to fix the problem. We are running Apex 4.01 on Oracle 11gR2 with WebLogic 11g.
Thanks,
Michelle

Hi Michelle,
how did you configure your JDBC-connection in the Listener's Administration? You could try to reduce the timeouts for the Listener so it drops unused connections faster.
Are there any firewalls/routers/proxies between your WebLogic-server and the database that may cause a connection to timeout?
It seems the Listener doesn't know it is cut and tries to (re)use it, but apparently this fails. Reducing the timeout to a value less than the timeout of intermediate systems might help to prevent using dead conncections.
#Kris: Is there any probing of the connections status before the connection is used again?
-Udo

Unfortunately, I can't see anything about the jdbc connection of the listener. But, since the post above, we now think it is a problem in the firewall. The firewall between the database and weblogic is cutting the connection after 2 hours of inactivity. I may have our web administrator look into setting the timeout of the listener to a time period of less than 2 hours and see if that fixes the problem. Thanks for your help.
Michelle

Hi Michelle,
the JDBC connection parameters regarding timeout can be configured via the Listener Admin (<your_webserver_base_url>/<your_apex_listener_context>/listenerAdmin, e.g. http://localhost:8080/apex/listenerAdmin) when you expand "JDBC Settings" on the connection tab. You can also change them by editing the apex-config.xml that belongs to your deployment.
-Udo

We set out timeout to a lower value then the firewall timeout, but this did not fix the problem, it may have made it worse. Any other suggestions?
Thanks,
Michelle

Have you set both "AbandonedConnectionTimeout" and "InactivityTimeout" to an appropriate level? The first parameter has to be lower than the second one.
If that's the case, there is a problem in the handling for pooling, either in the Listener or the WLS.
You could then try to set the firewall timeout and the JDBC-timeouts high enough to never see the timeout.
-Udo

Related

APEX-Listener Bugs

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

Using Json types

Getting wrong type when trying to use json types "json_array_t" "json_object_t". Do we need to enable anything on db. Database Version "12.2.0.1.0"

>>when trying to use json typesExactly what are you trying to do? Please share your code and use case.

Can you show a example or a code that we can see to help you to debug it? We cannot solve something that we can not see

trable "test_json_12_2" definitionName            Null?    Type            --------------- -------- --------------- MEMBER_ID       NOT NULL NUMBER(18)      ATTRIBUTE_VALUE NOT NULL VARCHAR2(32767)  (JSON check constraint) DECLARE    CURSOR mem_attributes IS SELECT        member_id,        attribute_value                             FROM        test_json_12_2                         v_mem_atrr_obj   json_object_t;    v_tags_array     json_array_t;   BEGIN    FOR r_mem IN mem_attributes LOOP        v_mem_atrr_obj := r_mem.attribute_value;-- Check if json has an array        BEGIN            IF                v_mem_atrr_obj.get('memberAttributes.tag').is_array ()            THEN                dbms_output.put_line('member_id :'                                       || r_mem.member_id                                       || ' has tier Array');            END IF;        END;    END LOOP;END;/

Please refer above. thanks for quick followup

Hi, it works for me. It should works for you too. That is my Database Version

Are you sure you db version is 12.2.0.1.0?

BANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0PL/SQL Release 12.2.0.1.0 - Production                                                    0CORE 12.2.0.1.0 Production                                                                  0TNS for Linux: Version 12.2.0.1.0 - Production                                            0NLSRTL Version 12.2.0.1.0 - Production                                                    0

Where do you run that code? Workaround is to use pl/jsonhttps://stackoverflow.com/questions/35785214/oracle-12c-does-not-recognize-json-type  or apex_json packagehttps://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm

Let me know what you tried?

Makes me think this could something small. Tried to test this out using livesql.oracle.com . I get the same error.   CREATE TABLE "TEST_JSON_12_2"    ( "MEMBER_ID" NUMBER(18,0) NOT NULL ENABLE, "ATTRIBUTE_VALUE" VARCHAR2(32767 BYTE) NOT NULL ENABLE   );      ALTER TABLE "TEST_JSON_12_2" ADD CONSTRAINT jscon_chk check (ATTRIBUTE_VALUE IS JSON STRICT);            insert into TEST_JSON_12_2 values   ( 3, '{"memberAttributes":{"tag":[{"name":"CustomerBonus","value":"PLATINUM","Id":"SCOTT"}]}}');           DECLARE    CURSOR mem_attributes IS SELECT        member_id,        attribute_value                             FROM        test_json_12_2;                             v_mem_atrr_obj   json_object_t;    v_tags_array     json_array_t;BEGIN    FOR r_mem IN mem_attributes LOOP        v_mem_atrr_obj := r_mem.attribute_value;        BEGIN            IF                v_mem_atrr_obj.get('memberAttributes.tag').is_array ()            THEN                dbms_output.put_line('member_id :'                                       || r_mem.member_id                                       || ' has tier Array');            END IF;          END;      END LOOP;END

I don’t know why is not working for you . It works fine for me. you can use the workaround

Which error? And which version is using there?

Error: ORA-06550: line 14, column 33:PLS-00382: expression is of wrong typeORA-06550: line 14, column 9:PL/SQL: Statement ignored06550. 00000 -  "line %s, column %s:\n%s"*Cause:    Usually a PL/SQL compilation error.*Action: Version: BANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0PL/SQL Release 12.2.0.1.0 - Production                                                    0CORE 12.2.0.1.0 Production                                                                  0TNS for Linux: Version 12.2.0.1.0 - Production                                            0NLSRTL Version 12.2.0.1.0 - Production                                                    0

Hi that is another error.You should solve that. Have a look here  https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9537440400346279780https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9535894000346566772https://livesql.oracle.com/apex/livesql/file/content_GBSP0AGEC2YP3R8YM2QM6SM3S.html

DS52 and Oracle 10g authentication?

Hello,
I would like to use Sun DS52 for authentication with Oracle 10g (HTML DB). Oracle has a function called dbms_ldap and I have been told to connect to the LDAP server in an anonymous PL/SQL block using SQL*Plus, and to get the simple_bind_s call to work. I have found this sql code that should I test my authentication with:
set serveroutput on
declare
l_dn varchar2(256) := 'CN=TEST_USER,OU=Hrusers,DC=ad,DC=fgfield,DC=com';
l_password varchar2(256) := 'TEST_PASSWORD'; -- adjust as required
l_ldap_host varchar2(256) := 'MY_LDAP_HOST'; -- adjust as required
l_ldap_port number := 389; -- adjust as required
l_retval pls_integer;
l_session dbms_ldap.session;
begin
l_retval := -1;
dbms_ldap.use_exception := TRUE;
begin
l_session := dbms_ldap.init( l_ldap_host, l_ldap_port );
l_retval := dbms_ldap.simple_bind_s( l_session, l_dn, l_password );
l_retval := dbms_ldap.unbind_s( l_session );
dbms_output.put_line('Authentication succeeded!');
exception when others then
l_retval := dbms_ldap.unbind_s( l_session );
dbms_output.put_line('Authentication failed!');
end;
exception when others then
dbms_output.put_line('Authentication exception!');
end;
/
The problem is that after I adjust the code to match my LDAP settings, I get 'Authentication failed!'. Can anybody let me know how to configure DS52 so I can get �simple_bind_s call to work�?
I am going through Sun ONE Directory Server Getting Started Guide, and I can connect, list entries in LDAP, etc. I just don�t know how to configure DS52 for Oracle�
Any advice is greatly appreciated.
Thank you for your time.
DanielD

Hi DanielD,
the first steps I would take are:
1) from the Oracle box, run
ldapsearch -D 'CN=TEST_USER,OU=HRusers,DC=ad,DC=fgfield,DC=COM' -w TEST_PASSWORD -h MY_LDAP_HOST -b "" -s base objectclass=\*
to make sure that there are no name resolution/firewall/etc issues.
If that passes, then I would use 'tail -f' on the LDAP server access log and try to authenticate in Oracle see if the BIND request is reaching the LDAP server and if the request completes without error on the LDAP side.

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

UTL_HTTP - ORA-29024 Certificate validation failure

I'm having issues with Oracle Certificate validation failure. while using UTL_HTTP calls. I've set the Oracle wallet path correctly and downloaded and registered the server certificates in Oracle Wallet. Tried similar https calls to other services. they're good. Can anyone throw ideas as to potential errors in the code/calls below? CREATE OR REPLACE PROCEDURE show_html_from_url (p_url  IN  VARCHAR2 DEFAULT NULL) AS  l_http_request   UTL_HTTP.req;  l_http_response  UTL_HTTP.resp;  l_text           VARCHAR2(32767);  l_default_url   VARCHAR2(300) := 'https://gb.redhat.com/';  l_url       VARCHAR2(600) := 'https://global.blyeplod.net/blyapi/Contract/ProjectSetup';  l_un VARCHAR2(256) := 'username'; --sending correct username  l_pwd VARCHAR2(32) := 'password'; --sending correct password  l_api_key VARCHAR2(256) := '***';  lv_wallet_path  VARCHAR2(300) := '/u01/app/oracle/product/11.1.0/db_1/wallet';  lv_request_body VARCHAR2(4000) :=   '{"Projects":{"Project":{"ProjectNumber":"110229","ProjectFriendlyName":"110229 - Test Project","ProjectRegion":"NAM"}}}';BEGIN  if p_url is not null then     l_url := p_url;  end if;  -- Make a HTTP request and get the response.  UTL_HTTP.set_wallet('file:'||lv_wallet_path);--//This step raises the ORA-29024: Certificate validation failure  l_http_request  := UTL_HTTP.begin_request(l_url,'POST' --//Method  ,'HTTP/1.1');  UTL_HTTP.set_authentication(l_http_request,l_un,l_pwd);  UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml charset=UTF-8');  UTL_HTTP.set_header(l_http_request, 'Content-Length', length(lv_request_body));  UTL_HTTP.write_text(l_http_request, lv_request_body);  --//ping and check response  l_http_response := UTL_HTTP.get_response(l_http_request);  -- Loop through the response.  BEGIN    LOOP      UTL_HTTP.read_text(l_http_response, l_text, 32766);      DBMS_OUTPUT.put_line (l_text);    END LOOP;  EXCEPTION    WHEN UTL_HTTP.end_of_body THEN      UTL_HTTP.end_response(l_http_response);   UTL_TCP.close_all_connections;    END;EXCEPTION  WHEN OTHERS THENdbms_output.put_line('***HTTP Test Raised error: '||sqlerrm);    UTL_HTTP.end_response(l_http_response);  UTL_TCP.close_all_connections;    RAISE;END show_html_from_url;/ SQL> exec show_html_from_url***HTTP Test Raised error: ORA-29273: HTTP request failedORA-06512: at"SYS.UTL_HTTP", line 1029ORA-29024: Certificate validation failureBEGIN show_html_from_url; END; ERROR at line 1:ORA-29273: HTTP request failedORA-06512: at "SYS.UTL_HTTP", line 1389ORA-29261: bad argumentORA-06512: at "PWB.SHOW_HTML_FROM_URL", line 41ORA-29273: HTTP request failedORA-06512: at "SYS.UTL_HTTP", line 1029ORA-29024: Certificate validation failureORA-06512: at line 1

Hi...... I am facing the same issue while processing https requests using UTL_HTTP .  any thoughts/ideas anyone? Thank You

This forums is for ORDS related questions.  This is more of a general plsql which you may get an answer in that forums SQL & PL/SQL -kris

Hi,this might be late, but you may be running into the same issue as I did a while back. Oracle have silently changed the requirements in their wallets. up to version 12.1 (possibly 11r2, I cannot confirm) you imported the entire certificate chain into the wallet.i.e. the root certificate, all intermediate certificates and the site certificate. as of 12.1 you should only import the root and intermediate certificates. This contradicts all documentation I found at the time. It may have been amended now thoughI asked and responded to my own question at Stackoverflow, you could have a look at that page to see if it resolves your problems.http://stackoverflow.com/questions/19380116/using-utl-http-wallets-on-12c-certificate-validation-failure.

Hi,(yes - also a late answer - but perhaps someone else can use this:)I just had the same problem after patching my 11.2.0.4 to 11.2.0.4.16 -> this certificate validation failure came up, but it worked before.Thanks to Tony Reed! I can confirm this is also happening on 11.2.0.4 - just remove the site certificate from the wallet and everything is working fine.

ORACLE Java Programming

Java Programming JSP Swing Java Servlet GlassFish