GeezerVison is showing github.com — Realtime Reporter Timeout · Issue #955 · utPLSQL/utPLSQL
Realtime Reporter Timeout #955DescriptionI'm having a bit of difficulty with the real-time reporter in 3.1.7. I'm taking the default timeout value and it's timing out at 60 seconds every time. In 3.1.6 it was working fine. I'm not sure if the issue is in my code or the framework, but here's what I have. Using the demo on YouTube and .\test\core\reporters\test_realtime_reporter.pkb from 3.1.6 as a guide I came up with the following.
Is this a fault in the framework or in my SQL? Thanks in advance.
Producer
DECLARE
l_reporter_0 ut3.ut_realtime_reporter := ut3.ut_realtime_reporter ();
l_reporters ut_reporters;
l_run_res NUMBER;
e_run_failure EXCEPTION;
PRAGMA EXCEPTION_INIT (e_run_failure, -20213);
BEGIN
l_reporter_0.set_reporter_id ('10CDDEA461D24BE38CF9606C16B693AE');
l_reporters := ut_reporters (l_reporter_0);
BEGIN
ut_runner.run (
a_paths =>
ut_varchar2_list ('MSTASZEW.REALTIME_REPORTING_SUITE'),
a_reporters => l_reporters,
a_fail_on_errors => TRUE,
a_coverage_schemes => NULL,
a_include_objects => NULL,
a_exclude_objects => NULL);
l_run_res := 1;
EXCEPTION
WHEN e_run_failure
THEN
l_run_res := 0;
END;
END;
Consumer
SELECT item_type, text
FROM TABLE (
ut_output_table_buffer ('10CDDEA461D24BE38CF9606C16B693AE').get_lines ())
WHERE TRIM (text) IS NOT NULL AND item_type IS NOT NULL;
Activityjgebal commented on Jun 23, 2019 Hi @michaelstaszewski
The demo video by @PhilippSalvisberg isn't really right.
It's using internal object name to retrieve output data from reporter.
Instead of:
SELECT item_type, text
FROM TABLE (
ut_output_table_buffer ('10CDDEA461D24BE38CF9606C16B693AE').get_lines ())
WHERE TRIM (text) IS NOT NULL AND item_type IS NOT NULL;Use reporter methods to access output data.
select item_type, text
from table(
ut3.ut_realtime_reporter('10CDDEA461D24BE38CF9606C16B693AE').get_lines()
);Internal output buffers should not be used.
In future, the privileges to access them can be removed in general.
Hope this helps. jgebal commented on Jun 23, 2019 We should probably do better job documenting public API's. jgebal commented on Jun 23, 2019 michaelstaszewski commented on Jun 23, 2019 Thanks, @jgebal, I'll give that a shot.
what are you building?
Toad for Oracle integration. We had a call with you and Pazus a couple of months ago. michaelstaszewski commented on Jun 23, 2019 With that change I get ORA-06553.
PhilippSalvisberg commented on Jun 23, 2019 In the upcoming utPLSQL for SQL Developer version we use the public API to access the output of the realtime reporter. @jgebal is right. I really did it the wrong (unsupported) way in the video mentioned above. It's only working up to v3.1.4 because starting with v.3.1.5 the realtime reporter is using a dedicated output table.
Here are some excerpts of the code used in the upcoming SQL Developer extension. This should help you to understand how to use it for the time being.
The PL/SQL block for the producer thread
The PL/SQL block for the consumer thread
You have to make sure that the reporter used in the consumer session is properly initialized (with the reporter id used in the producer thread). See here. Reacted by Michael Staszewski and Jacek Gębal michaelstaszewski commented on Jun 23, 2019 Reacted by Philipp Salvisberg and Jacek Gębal michaelstaszewski commented on Jun 24, 2019 get_lines() works, but rows come back in blocks of 100. get_lines_cursor() allows me to process rows as they become available for best UI updates. Is there a reason to avoid get_lines_cursor()? For real-time reporting I'm most concerned with UI updates and less so with maximum performance. jgebal commented on Jun 24, 2019 The 100 iis a size controlled by client oci/jdbc setting.
Try
Before running the query.
get_lines_cursor can be used as well but you need to handle closing the cursor is session both on success and exceptions. pesse commented on Jun 24, 2019 It's not possible (at least from java) to control the fetch size when using get_lines().
Therefore the cli uses get_lines_cursor(), which is actually SELECT where you can control the fetch size.
There are no real disadvantages of using get_lines_cursor(), I even enjoy the independence by using select * from ... - that way I don't need to adapt cli when we add new columns (as long as I don't need them). michaelstaszewski commented on Jun 24, 2019 I'm seeing the same, @pesse.
I'm using ODAC (OCI) and their FetchRows property controls arraysize and it's only functional with get_lines_cursor(). Similarly I execute my consumer and producer scripts in separate SQL*Plus sessions and SQL*Plus is also piping rows in blocks of 100. You can see the delayed response by the following sample suite. The pre-run doesn't even arrive until after 5 seconds have elapsed.
Suite
CREATE OR REPLACE PACKAGE TEST_955
AS
--%suite
--%test
PROCEDURE lengthy_test;
END;
/
CREATE OR REPLACE PACKAGE BODY TEST_955
AS
PROCEDURE lengthy_test
AS
BEGIN
DBMS_LOCK.sleep (5);
END;
END;
/
Producer
DECLARE
l_reporter_0 ut_realtime_reporter := ut_realtime_reporter ();
l_reporters ut_reporters;
l_run_res NUMBER;
e_run_failure EXCEPTION;
PRAGMA EXCEPTION_INIT (e_run_failure, -20213);
BEGIN
l_reporter_0.set_reporter_id ('D8A79E85915640A6A4E1698FDF90BA74');
l_reporter_0.output_buffer.init ();
l_reporters := ut_reporters (l_reporter_0);
BEGIN
ut_runner.run (
a_paths => ut_varchar2_list ('TEST_955'),
a_reporters => l_reporters,
a_fail_on_errors => TRUE,
a_coverage_schemes => NULL,
a_include_objects => NULL,
a_exclude_objects => NULL);
l_run_res := 1;
EXCEPTION
WHEN e_run_failure
THEN
l_run_res := 0;
END;
END;
/
Consumer
SET arraysize 1
VARIABLE report REFCURSOR
DECLARE
l_reporter ut_realtime_reporter := ut_realtime_reporter ();
BEGIN
l_reporter.set_reporter_id ('D8A79E85915640A6A4E1698FDF90BA74');
OPEN :report FOR SELECT t.item_type, t.text, rownum
FROM TABLE (l_reporter.get_lines ()) t;
END;
/
PRINT report
pesse commented on Jun 24, 2019 I know I investigated this some months ago. If I remember correctly I stumbled over a forum post or part of documentation where it was stated that you can't control PL/SQL fetch size, but only SQL fetch size (I think this is general, but I'm very sure it's the case from Java).
get_lines() is PL/SQL fetch size only, get_lines_cursor() uses the SQL fetch size. The difference isn't that big so from my point of view there are no downsides in using get_lines_cursor() michaelstaszewski commented on Jun 24, 2019 Correction to my earlier arraysize statement, @pesse. I'm seeing arraysize being observed, however the first record in the data isn't returned to my app until 100 rows are available server side or the run has completed. This is just observation, but it takes a long time for the first record to become available when it shouldn't.
get_lines_cursor() works well so I'll carry on there. Thanks for the help. jgebal commented on Jun 25, 2019 get_lines_cursor it is then :) Reacted by Michael Staszewski PhilippSalvisberg commented on Jun 25, 2019 I have to look at this as well... I do no t “feel” a delay when running it from SQLDev. I’m using a thin driver and i set the fetch size to 1. PhilippSalvisberg commented on Jun 28, 2019 I had a look at get_lines() and get_lines_cursor(). For me both methods work well from JDBC. My verdict. using get_lines() is safe.
A ut_realtime_reporter is initialized with a ut_output_clob_table_buffer. A get_lines() and a get_lines_cursor() call for an ut_realtime_reporter instance are implemented in the parent class ut_output_reporter_base.
First implementation layer:
Second implementation layer:
If get_lines_cursor() works with a fetchSize of 1, then querying from get_lines() should work as well. And with work I mean, that events are read by the consumer as soon as they are produced.
If this does not work, then I'm really interested in a reproducible test case. michaelstaszewski commented on Jul 1, 2019 @PhilippSalvisberg, I've provided a reproducible test case above. See my sample suite and the producer/consumer scripts to be executed in SQL Plus. Using that example I do not get the pre-run event even until after the 5 secs dbms_lock.sleep has elapsed using get_lines(). Using get_lines_cursor() and the backdoor approach from your YouTube video and earlier utPLSQL I get events immediately as they are produced. In my example above I'd get the pre-run, pre-suite, pre-test immediately and after 5 secs have elapsed then I get the post-xyz events.
Do you see differently using SQL Plus on your machine? I cannot comment on JDBC, I use OCI. PhilippSalvisberg commented on Jul 1, 2019 Strange. I tried it based on your test case above. It works as expected. See:
First three events (pre-run, pre-suite, pre-test) are reported immediately. Then 5 seconds pause (due to the dbms_lock.sleep(5) in the test) and then the remaining events (post-test, post-suite, post-run) are reported. - Looks good. michaelstaszewski commented on Jul 3, 2019 Perhaps database version related. I do all of my initial development on a local 11g XE database when I don't need version specific features. Here are the scripts running in my dev environment.
pesse commented on Jul 3, 2019 Good idea! I develop on a 12.2 normally, so maybe this is an issue which has been solved with 18 or 19.
To wrap the function with a cursor is a workaround which definitely works on 12.1 and 12.2, though. PhilippSalvisberg commented on Jul 3, 2019 Good thinking. I have to test with earlier DB versions as well. Let you know the result. PhilippSalvisberg commented on Jul 3, 2019 This is really strange.
I've tested it with SQL*Plus within a 11.2.0.4.190115 Docker container (from macOS). Works. Events are consumed immediately.
I tried it from Windows 10 using SQL*Plus 12.1.0.2.0 (32-bit Client/64-bit Client). And it works as well.
No problems from SQL Developer 19.1 connecting to the 11.2.0.4 database from macOS, Windows or Ubuntu. Events are available immediately, as expected.
I tried with utPLSQL v3.1.4 (first version with realtime reporter) and v3.1.8 (current develop version). Same result. No problems.
Maybe this is related to a specific version of SQL*Plus or Oracle Database or it is even platform specific (Windows). I'm running out of ideas. :-( PhilippSalvisberg commented on Jul 4, 2019 I can reproduce the problem with Oracle Database 11.2 XE and SQL*Plus! When using this database version the fetchSize will be ignored from Java as well. This really looks like an Oracle Database bug, which is has been fixed. However, the workaround is to use get_lines_cursor(). I changed the code in the SQL Developer extension accordingly.
Thanks @michaelstaszewski for reporting this issue. Reacted by Michael Staszewski and Jacek Gębal jgebal commented on Jul 13, 2019 I've made some small improvements (will be in 3.1.8).
So your producer and consumer can have reporters inline:
--main test run ( session 1 )
begin
ut_runner.run (
ut_varchar2_list ('ut3_tester','ut3$user#'),
ut_reporters(
ut_realtime_reporter().set_reporter_id('d8a79e85915640a6a4e1698fdf90ba74')
)
);
end;
/set arraysize 1
set pagesize 0
set long 400
set longchunksize 400
select
ut_realtime_reporter()
.set_reporter_id('d8a79e85915640a6a4e1698fdf90ba74')
.get_lines_cursor()
from dual;set arraysize 1
set pagesize 0
set long 400
set longchunksize 400
select *
from table(
ut_realtime_reporter()
.set_reporter_id('d8a79e85915640a6a4e1698fdf90ba74')
.get_lines()
);As with most enhancements, this will only work on 3.1.8 and above :(
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|
