I really appreciate your time helping me out with this, Tom. I hope Oracle appreciates you as well. They certainly should...
I looked in DBA_TAB_COLS and don't see anything that appears to provide a numeric column type value. I checked the Internet and found some references to DBMS_TYPES, which does appear to contain
some of the values you indicated but not all. I also found a horribly complicated query on the internet that joined a bunch of SYS$xxx tables, pulling the numeric value from one of them and using a DECODE to convert the numeric value to a data type name. But I am yet to find a
definition for these anywhere.
In any event, I decided to just use the values you provided and give this a shot. The first problem I had was with my TIMESTAMP fields. When I tried to map the col_type values 12, 178, 179, 180, 181, 231 to a DATE variable I got the following error:
ERROR at line 1:
ORA-06562: type of out argument must match type of column or bind variable
ORA-06512: at "SYS.DBMS_SQL", line 2058
ORA-06512: at line 109
ORA-06512: at line 514
So I decided to use TIMESTAMP for the same column type values instead. That got rid of the above error.
I was finally able to get a unit test working that calls the procedure exactly once. However, the unit test needs to make repeated calls to the stored procedure to step through data in some of the tables of the application. It's simulating a pagination of data in the tables for a user interface. I found that repeated calls using this technique cause the following error after the 2nd attempt:
ERROR at line 1:
ORA-21779: duration not active
Charming.
If I subsequently attempt to run the unit test script again while still in SQL*Plus, I get errors like the following:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5792
Session ID: 137 Serial number: 2320
ERROR:
ORA-03114: not connected to ORACLE
As you can imagine, I had no idea what this could be. So I started putting debug statements all over the place. Interestingly enough, the error occurred in a portion of the code nowhere related to the DBMS_SQL package calls.
So I thought that maybe this was some "out there" situation due to the fact that I was using the "SELECT e.*, e2.*, ec.*" technique. I decided to change the stored procedure to use only "SELECT e.*" and then I could put together two unit tests: 1 using the DBMS_SQL stuff and another using the far simpler ROWTYPE and "FETCH INTO" approach (since now it's just a single table in the result set).
The short of it is it works flawlessly with the ROWTYPE technique but has the same exact malificent behavior with the DBMS_SQL technique.
One thing is I put the code to handle the repeated unit test calls inside a local procedure in the unit test script. The full script (sorry about the size) is as follows:
DECLARE
-- Used with RTTM_NEXT_N_FETCH stored procedure
v_fetch_table VARCHAR2(30);
v_fetch_type VARCHAR2(20);
v_fetch_size INTEGER;
v_trade_status rttm_execution.trade_status%TYPE;
v_boundary_from TIMESTAMP := NULL;
v_boundary_to TIMESTAMP := NULL;
v_sliding_from TIMESTAMP := NULL;
v_sliding_to TIMESTAMP := NULL;
v_oldest_timestamp TIMESTAMP;
v_newest_timestamp TIMESTAMP;
v_execution_records SYS_REFCURSOR;
v_clearing_records SYS_REFCURSOR;
-- Used for DBMS_SQL stuff
v_cursor_number INTEGER;
v_desc_tab DBMS_SQL.DESC_TAB;
v_column_count INTEGER;
v_col_name VARCHAR2(30);
v_col_value_timestamp TIMESTAMP;
v_col_value_varchar2 VARCHAR2(4000);
-- Used to obtain/print cursor results
v_record_count INTEGER;
c_execution_id VARCHAR2(4000);
c_trade_status VARCHAR2(4000);
c_insert_timestamp TIMESTAMP;
c_symbol VARCHAR2(4000);
c_clearing_id VARCHAR2(4000);
c_security_id VARCHAR2(4000);
PROCEDURE run_test IS
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('*', 80, '*'));
DBMS_OUTPUT.PUT_LINE('* Input:');
DBMS_OUTPUT.PUT_LINE('* v_fetch_table=' || v_fetch_table);
DBMS_OUTPUT.PUT_LINE('* v_fetch_type=' || v_fetch_type);
DBMS_OUTPUT.PUT_LINE('* v_fetch_size=' || v_fetch_size);
DBMS_OUTPUT.PUT_LINE('* v_trade_status=' || v_trade_status);
DBMS_OUTPUT.PUT_LINE('* v_boundary_from=' || NVL(TO_CHAR(v_boundary_from, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_boundary_to=' || NVL(TO_CHAR(v_boundary_to, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_sliding_from=' || NVL(TO_CHAR(v_sliding_from, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_sliding_to=' || NVL(TO_CHAR(v_sliding_to, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_oldest_timestamp=' || NVL(TO_CHAR(v_oldest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_newest_timestamp=' || NVL(TO_CHAR(v_newest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
rttm_next_n_fetch(
v_fetch_table,
v_fetch_type,
v_fetch_size,
v_trade_status,
v_boundary_from,
v_boundary_to,
v_sliding_from,
v_sliding_to,
v_oldest_timestamp,
v_newest_timestamp,
v_execution_records,
v_clearing_records
);
DBMS_OUTPUT.PUT_LINE('* Output:');
DBMS_OUTPUT.PUT_LINE('* v_sliding_from=' || NVL(TO_CHAR(v_sliding_from, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_sliding_to=' || NVL(TO_CHAR(v_sliding_to, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_oldest_timestamp=' || NVL(TO_CHAR(v_oldest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* v_newest_timestamp=' || NVL(TO_CHAR(v_newest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
DBMS_OUTPUT.PUT_LINE('* Execution:');
IF (v_execution_records IS NULL)
THEN
DBMS_OUTPUT.PUT_LINE('* NULL');
ELSE
v_cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(v_execution_records);
DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_number, v_column_count, v_desc_tab);
FOR i IN 1..v_column_count
LOOP
IF (v_desc_tab(i).col_type in (12, 178, 179, 180, 181, 231))
THEN
DBMS_SQL.DEFINE_COLUMN(v_cursor_number, i, v_col_value_timestamp);
ELSE
DBMS_SQL.DEFINE_COLUMN(v_cursor_number, i, v_col_value_varchar2, 4000);
END IF;
END LOOP;
v_record_count := 0;
WHILE (DBMS_SQL.FETCH_ROWS(v_cursor_number) > 0)
LOOP
v_record_count := v_record_count + 1;
c_execution_id := NULL;
c_trade_status := NULL;
c_insert_timestamp := NULL;
c_symbol := NULL;
FOR i IN 1..v_column_count
LOOP
v_col_name := v_desc_tab(i).col_name;
IF (v_col_name = 'EXECUTION_ID')
THEN
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_execution_id);
ELSIF (v_col_name = 'TRADE_STATUS')
THEN
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_trade_status);
ELSIF (v_col_name = 'INSERT_TIMESTAMP')
THEN
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_insert_timestamp);
ELSIF (v_col_name = 'SYMBOL')
THEN
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_symbol);
ELSIF (v_desc_tab(i).col_type in (12, 178, 179, 180, 181, 231))
THEN
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_col_value_timestamp);
ELSE
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_col_value_varchar2);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(
'* Record #' || v_record_count
|| ' execution_id=' || c_execution_id
|| ' trade_status=' || c_trade_status
|| ' insert_timestamp=' || to_char(c_insert_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF')
|| ' symbol=' || c_symbol
);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor_number);
END IF;
-- Just close clearing records for now
IF (v_clearing_records IS NOT NULL)
THEN
CLOSE v_clearing_records;
END IF;
END;
BEGIN
v_boundary_from := NULL;
v_boundary_to := NULL;
v_sliding_from := NULL;
v_sliding_to := NULL;
v_fetch_table := 'Execution';
v_fetch_type := 'First';
v_fetch_size := 10;
v_trade_status := 'Unmatched';
run_test();
insert into rttm_execution (execution_id, trade_status, insert_timestamp, symbol) values ('E000', 'Unmatched', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'PRODUCT_000');
insert into rttm_execution_2 (execution_id, transaction_time) values ('E000', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
insert into rttm_execution_c (execution_id) values ('E000');
...
... etc.
...
insert into rttm_clearing (clearing_id, trade_status, insert_timestamp, security_id) values ('C000', 'Unmatched', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00', 'YYYY-MM-DD HH24:MI:SS.FF'), 'PRODUCT_019X');
insert into rttm_clearing_2 (clearing_id, transaction_time) values ('C000', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00', 'YYYY-MM-DD HH24:MI:SS.FF'));
insert into rttm_clearing_c (clearing_id) values ('C000');
...
... etc.
...
run_test();
v_fetch_type := 'Next';
run_test();
run_test();
v_fetch_type := 'Refresh';
run_test();
v_fetch_type := 'Next';
run_test();
run_test();
v_fetch_type := 'Last';
run_test();
v_fetch_type := 'Previous';
run_test();
run_test();
run_test();
run_test();
v_fetch_table := 'Clearing';
v_fetch_type := 'First';
run_test();
v_fetch_type := 'Next';
run_test();
v_fetch_type := 'Refresh';
run_test();
v_fetch_type := 'Next';
run_test();
run_test();
run_test();
v_fetch_type := 'Last';
run_test();
v_fetch_type := 'Previous';
run_test();
run_test();
run_test();
run_test();
END;
/
ROLLBACK;
I wondered if there might be something about the fact that these DBMS_SQL calls are being made within a local procedure. Perhaps if I actually created a unit test procedure and ran it from there it would work better.
No dice. Same exact error. After running the "run_test" procedure twice, I get the same error:
DECLARE
*
ERROR at line 1:
ORA-21779: duration not active
Question: Any ideas? Do you see something that is missing?It looks like using Java for a unit test would be infinitely simpler. The frustrating thing is I am virtually certain the procedure works. I just can't prove it with a unit test.
January 10, 2011 - 7:46 am UTC
You didn't see this decode statement???
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE',
23, 'RAW', 24, 'LONG RAW',
58, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
122, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
123, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'),
that is in the view text of DBA_TAB_COLS
Make your example really teeny tiny and make it reproduce the error in full and make it 100% complete (routine to call and everything - use all_users, all_objects - etc to test with)
and then I'll take a look. The code is too large right now. Get rid of everything that is NOT RELEVANT to reproducing the issue - everything.