This is a generic utility function that I wrote for use in unit testing web services that return REF cursors. It takes as input an open REF cursor and an optional filter string and returns the output as an array of delimited strings matching the filter. This can then be used to assert against expected output records.
The initial technical difficulty I had with making this generic was in getting the structure of the cursor. This is resolved by translating the REF cursor into a cursor that can be managed by the package DBMS_SQL, using DBMS_SQL.To_Cursor_Number.
As I am using it for unit testing the function does not need to be scalable. For other uses involving large volumes it could be made scalable by, for example, converting to a pipelined function.
Data Types
The cursor needs to return a flat projection, and columns accounted for currently are:
- NUMBER
- VARCHAR2
- DATE
- TIMESTAMP
Custom Dependencies
- L1_chr_arr – custom array type, equivalent to SYS.ODCIVarchar2List
- Utils.List_Delim – function that turns an array of strings into one delimited string, included in my unit test framework, Brendan’s Database Unit Testing Framework
Code
The function wiil (shortly) be packaged within my unit testing package UT_Utils, but for demo purposes, here is a script that has a driving anonymous block with the function declared locally within that block.
DECLARE
l_csr SYS_REFCURSOR;
l_res_lis L1_chr_arr;
c_query_1 CONSTANT VARCHAR2(4000) := 'SELECT * FROM employees ORDER BY employee_id';
PROCEDURE Write_Log (p_line VARCHAR2) IS
BEGIN
DBMS_Output.Put_Line (p_line);
END Write_Log;
FUNCTION Cursor_to_Array (p_csr IN OUT SYS_REFCURSOR, p_filter VARCHAR2 DEFAULT NULL) RETURN L1_chr_arr IS
c_chr_type CONSTANT PLS_INTEGER := 1; --DBMS_Types.TYPECODE_* do not seem to quite work
c_num_type CONSTANT PLS_INTEGER := 2;
c_dat_type CONSTANT PLS_INTEGER := 12;
c_stp_type CONSTANT PLS_INTEGER := 180;
l_csr_id PLS_INTEGER;
l_n_cols PLS_INTEGER;
l_desctab DBMS_SQL.DESC_TAB;
l_chr_val VARCHAR2(4000);
l_num_val NUMBER;
l_dat_val DATE;
l_stp_val TIMESTAMP;
l_val_lis L1_chr_arr;
l_res_lis L1_chr_arr := L1_chr_arr();
l_rec VARCHAR2(4000);
BEGIN
l_csr_id := DBMS_SQL.To_Cursor_Number (p_csr);
DBMS_SQL.Describe_Columns (l_csr_id, l_n_cols, l_desctab);
FOR i IN 1..l_n_cols LOOP
CASE l_desctab(i).col_type
WHEN c_chr_type THEN
DBMS_SQL.Define_Column (l_csr_id, i, l_chr_val, 4000);
WHEN c_num_type THEN
DBMS_SQL.Define_Column (l_csr_id, i, l_num_val);
WHEN c_dat_type THEN
DBMS_SQL.Define_Column (l_csr_id, i, l_dat_val);
WHEN c_stp_type THEN
DBMS_SQL.Define_Column (l_csr_id, i, l_stp_val);
ELSE
Write_Log ('Col type ' || l_desctab(i).col_type || ' not accounted for!');
END CASE;
END LOOP;
WHILE DBMS_SQL.Fetch_Rows (l_csr_id) > 0 LOOP
l_val_lis := L1_chr_arr();
l_val_lis.EXTEND (l_n_cols);
FOR i IN 1 .. l_n_cols LOOP
CASE l_desctab(i).col_type
WHEN c_chr_type THEN
DBMS_SQL.Column_Value (l_csr_id, i, l_chr_val);
l_val_lis(i) := l_chr_val;
WHEN c_num_type THEN
DBMS_SQL.Column_Value (l_csr_id, i, l_num_val);
l_val_lis(i) := l_num_val;
WHEN c_dat_type THEN
DBMS_SQL.Column_Value (l_csr_id, i, l_dat_val);
l_val_lis(i) := l_dat_val;
WHEN c_stp_type THEN
DBMS_SQL.Column_Value (l_csr_id, i, l_stp_val);
l_val_lis(i) := l_stp_val;
END CASE;
END LOOP;
l_rec := Utils.List_Delim (l_val_lis);
IF l_rec LIKE '%' || p_filter || '%' THEN
l_res_lis.EXTEND;
l_res_lis (l_res_lis.COUNT) := l_rec;
END IF;
END LOOP;
DBMS_SQL.Close_Cursor (l_csr_id);
RETURN l_res_lis;
END Cursor_to_Array;
BEGIN
OPEN l_csr FOR c_query_1;
l_res_lis := Cursor_to_Array (l_csr, 'SA_REP');
FOR i IN 1..l_res_lis.COUNT LOOP
Write_Log (i || ': ' || l_res_lis(i));
END LOOP;
END;
/
Output for Cursor against HR.employees
1: 150|Peter|Tucker|PTUCKER|011.44.1344.129268|30-JAN-05|SA_REP|10000|.3|145|80| 2: 151|David|Bernstein|DBERNSTE|011.44.1344.345268|24-MAR-05|SA_REP|9500|.25|145|80| 3: 152|Peter|Hall|PHALL|011.44.1344.478968|20-AUG-05|SA_REP|9000|.25|145|80| 4: 153|Christopher|Olsen|COLSEN|011.44.1344.498718|30-MAR-06|SA_REP|8000|.2|145|80| 5: 154|Nanette|Cambrault|NCAMBRAU|011.44.1344.987668|09-DEC-06|SA_REP|7500|.2|145|80| 6: 155|Oliver|Tuvault|OTUVAULT|011.44.1344.486508|23-NOV-07|SA_REP|7000|.15|145|80| 7: 156|Janette|King|JKING|011.44.1345.429268|30-JAN-04|SA_REP|10000|.35|146|80| 8: 157|Patrick|Sully|PSULLY|011.44.1345.929268|04-MAR-04|SA_REP|9500|.35|146|80| 9: 158|Allan|McEwen|AMCEWEN|011.44.1345.829268|01-AUG-04|SA_REP|9000|.35|146|80| 10: 159|Lindsey|Smith|LSMITH|011.44.1345.729268|10-MAR-05|SA_REP|8000|.3|146|80| 11: 160|Louise|Doran|LDORAN|011.44.1345.629268|15-DEC-05|SA_REP|7500|.3|146|80| 12: 161|Sarath|Sewall|SSEWALL|011.44.1345.529268|03-NOV-06|SA_REP|7000|.25|146|80| 13: 162|Clara|Vishney|CVISHNEY|011.44.1346.129268|11-NOV-05|SA_REP|10500|.25|147|80| 14: 163|Danielle|Greene|DGREENE|011.44.1346.229268|19-MAR-07|SA_REP|9500|.15|147|80| 15: 164|Mattea|Marvins|MMARVINS|011.44.1346.329268|24-JAN-08|SA_REP|7200|.1|147|80| 16: 165|David|Lee|DLEE|011.44.1346.529268|23-FEB-08|SA_REP|6800|.1|147|80| 17: 166|Sundar|Ande|SANDE|011.44.1346.629268|24-MAR-08|SA_REP|6400|.1|147|80| 18: 167|Amit|Banda|ABANDA|011.44.1346.729268|21-APR-08|SA_REP|6200|.1|147|80| 19: 168|Lisa|Ozer|LOZER|011.44.1343.929268|11-MAR-05|SA_REP|11500|.25|148|80| 20: 169|Harrison|Bloom|HBLOOM|011.44.1343.829268|23-MAR-06|SA_REP|10000|.2|148|80| 21: 170|Tayler|Fox|TFOX|011.44.1343.729268|24-JAN-06|SA_REP|9600|.2|148|80| 22: 171|William|Smith|WSMITH|011.44.1343.629268|23-FEB-07|SA_REP|7400|.15|148|80| 23: 172|Elizabeth|Bates|EBATES|011.44.1343.529268|24-MAR-07|SA_REP|7300|.15|148|80| 24: 173|Sundita|Kumar|SKUMAR|011.44.1343.329268|21-APR-08|SA_REP|6100|.1|148|80| 25: 174|Ellen|Abel|EABEL|011.44.1644.429267|11-MAY-04|SA_REP|11000|.3|149|80| 26: 175|Alyssa|Hutton|AHUTTON|011.44.1644.429266|19-MAR-05|SA_REP|8800|.25|149|80| 27: 176|Jonathon|Taylor|JTAYLOR|011.44.1644.429265|24-MAR-06|SA_REP|8600|.2|149|80| 28: 177|Jack|Livingston|JLIVINGS|011.44.1644.429264|23-APR-06|SA_REP|8400|.2|149|80| 29: 178|Kimberely|Grant|KGRANT|011.44.1644.429263|24-MAY-07|SA_REP|7000|.15|149|| 30: 179|Charles|Johnson|CJOHNSON|011.44.1644.429262|04-JAN-08|SA_REP|6200|.1|149|80| PL/SQL procedure successfully completed.
with most stylish colour scheme around for store addresses and phrase balloons to answer all your chosen store first to add elegance to get it a balloon will arrive deflated but you free of our giant number 2 balloon – along with you
Dimensions (approximately): H 86cm
Trafford Centre: Due to post This means that your local Card Factory store locator for helium
Metallic rose gold colour scheme around for you free of confetti balloons giant number 2 balloon Coming in rose gold hues are sure to answer all your confirmation email as proof of our store first to a fabulous celebration Find beautiful metallic shapes letters numbers and in a great way to