Varying IN lists – last bit

So, just a final say on this series of blog entries.

The first is that often you start down one workaround and then you find something that doesn’t quite work so you workaround that and before you know it you’re along way from where you’d like to be.

What started as a simple desire to reduce the impact of dynamically generated varying IN lists (some not using bind variables) from a couple of applications was severely complicated by ODP.NET’s lack of support / interoperability with Oracle UDTs and the OCI limit of 4000 characters in a VARCHAR2 bind.

As a result, the current choice is between the original situation – lots of similar SQL – and inserting the values into a GLOBAL TEMPORARY TABLE and then using a query which has a WHERE … IN subquery selecting from that GTT. More on that down below…

When I wrote previously, also under consideration was using a global packaged variable. However this was eliminated as a possibility due to at least three frustrating issues. First up was an idea to use a function that would convert an associative array (supported by ODP.NET) to a similar UDT (just using a FOR LOOP that puts the values from one into the other). Using a bit of PL/SQL to demonstrate (8i hence the lack of SYS_REFCURSOR and the need to declare a REF CURSOR in a package):


CREATE OR REPLACE TYPE tt_number AS TABLE OF NUMBER;
/


CREATE OR REPLACE PACKAGE pkg_types
AS
--
TYPE refcursor IS REF CURSOR;
TYPE aa_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
--
END pkg_types;
/


CREATE OR REPLACE FUNCTION f_tt_convert_error_demo (
i_associative_array IN pkg_types.aa_number
)
RETURN tt_number
AS
--
vt_number tt_number := tt_number();
--
BEGIN
--
FOR i IN i_associative_array.FIRST .. i_associative_array.LAST
LOOP
--
vt_number.EXTEND();
vt_number(i) := i;
--
END LOOP;
--
RETURN vt_number;
--
END;
/


declare
v_number number;
v_cursor pkg_types.refcursor;
va_number pkg_types.aa_number;
begin
for i in 1 .. 10
loop
va_number(i) := i;
end loop;
open v_cursor for
select *
from table(cast(f_tt_convert_error_demo(va_number) as tt_number));
loop
fetch v_cursor into v_number;
exit when v_cursor%NOTFOUND;
dbms_output.put_line(v_number);
end loop;
close v_cursor;
end;

I should have known, but this cannot work due to an error “PLS-00425: in SQL, function argument and return types must be SQL type”.

Incidentally, if you try dynamic SQL, you’ll get a “PLS-00457: expressions have to be of SQL types” instead.

Secondly, just because you can do something in SQL doesn’t mean that the same statement will work in a PL/SQL routine (and vice versa from memory – it tends to be features that are relative new). Following on from SQL above:


CREATE OR REPLACE FUNCTION f_tt_error_demo
RETURN tt_number
AS
--
vt_number tt_number := tt_number();
--
BEGIN
--
FOR i IN 1 .. 10
LOOP
--
vt_number.EXTEND();
vt_number(i) := i;
--
END LOOP;
--
RETURN vt_number;
--
END;
/


SQL> select value(t)
2 from table (cast (f_tt_error_demo as tt_number)) t
3 /
VALUE(T)
----------
1
2
3
4
5
6
7
8
9
10


SQL> declare
2 begin
3 for a in (select VALUE(t) num
4 from table (cast (f_tt_error_demo as tt_number)) t)
5 loop
6 dbms_output.put_line(a.num);
7 end loop;
8 end;
9 /
declare
*
ERROR at line 1:
ORA-06550: line 0, column 0:
PLS-00382: expression is of wrong type
ORA-06550: line 3, column 13:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 28:
PLS-00364: loop index variable 'A' use is invalid
ORA-06550: line 6, column 7:
PL/SQL: Statement ignored

but, with dynamic sql:


SQL> declare
2 v_number number;
3 v_cursor pkg_types.refcursor;
4 begin
5 open v_cursor for
6 ' select value(t) '||
7 ' from table (cast (f_tt_error_demo as tt_number)) t ';
8 loop
9 fetch v_cursor into v_number;
10 exit when v_cursor%NOTFOUND;
11 dbms_output.put_line(v_number);
12 end loop;
13 close v_cursor;
14 end;
15 /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.

Thirdly, the above are distilled code examples of further errors that I came across while trying to put together a bit of example code for this blog entry to show the real problem that I had.

I actually wanted to use the function in a subquery like this:


create table error_demo
as
select rownum col1
from all_objects
where rownum < 11
1 select *
2 from error_demo
3 where col1 IN (select value(t) num
4* from table (cast (f_tt_error_demo as tt_number)) t)
SQL> /
COL1
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.


SQL> declare
2 v_number number;
3 v_cursor pkg_types.refcursor;
4 begin
5 open v_cursor for
6 select col1
7 from error_demo
8 where col1 IN (select *
9 from table (cast (f_tt_error_demo as tt_number)) t);
10 loop
11 fetch v_cursor into v_number;
12 exit when v_cursor%NOTFOUND;
13 dbms_output.put_line(v_number);
14 end loop;
15 close v_cursor;
16 end;
17 /
from table (cast (f_tt_error_demo as tt_number)) t);
*
ERROR at line 9:
ORA-06550: line 9, column 37:
PLS-00220: simple name required in this context
ORA-06550: line 6, column 4:
PL/SQL: SQL Statement ignored

“PLS-00220: simple name required in this context” – that’s a new one for me, first time I’ve had that error, I think.

But again, with dynamic sql:


SQL> ed
Wrote file afiedt.buf
1 declare
2 v_number number;
3 v_cursor pkg_types.refcursor;
4 begin
5 open v_cursor for
6 ' select col1 '||
7 ' from error_demo '||
8 ' where col1 IN (select * '||
9 ' from table (cast (f_tt_error_demo as tt_number)) t)';
10 loop
11 fetch v_cursor into v_number;
12 exit when v_cursor%NOTFOUND;
13 dbms_output.put_line(v_number);
14 end loop;
15 close v_cursor;
16* end;
SQL> /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.

So, as I mentioned way up above, these issues really have reduced it down to a choice between inserting the values in a GTT and then using that GTT in an IN subquery SELECT, or just to revert back to the IN lists as they were.

On the GTT front, we can bulk insert the values for the IN list from the application into the GTT using a simple procedure which accepts an associative array (like pkg_types.aa_number above).
One important note is that the GTT as to be created as “ON COMMIT PRESERVE ROWS”, otherwise the application will raise error “ORA-08103: object no longer exists”. This rings a bell from previous experiences using a JDBC app.

However this then requires another sort of workaround to delete the rows in the GTT as the first step in the procedure which bulk inserts the rows….

So, this leaves it all in a funny place where you’ve got to weigh up the evils of all the IN lists with the inefficiency, phaffing around and the general “bad smell” of the GTT approach.

It also leaves two open questions. Firstly, should we have another look at a higher level at the application design to reconsider why these IN lists are being constructed / are required in the first place? And secondly, when will ODP.NET provide the same support for Oracle UDTs as it does for JDBC?

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 62 other followers

%d bloggers like this: