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