My Speaking Schedule for Oracle Open World 2014
Posted: August 27, 2014 Filed under: 12C, Open World, Oracle, Regular Expressions, Row Pattern Matching, SQL | Tags: 12C, Open World, Oracle, Regular Expressions, Row Pattern Matching, SQL Leave a commentA quick post to let you know about the two presentations that I’ll be doing at Oracle Open World 2014.
Session ID: UGF4482
Session Title: “Getting Started with SQL Pattern Matching in Oracle Database 12c“
Venue / Room: Moscone South – 301
Date and Time: 9/28/14, 13:30 – 14:15
Session ID: CON4493
Session Title: “Regular Expressions in Oracle Database 101”
Venue / Room: Moscone South – 303
Date and Time: 10/2/14, 13:15 – 14:00
As usual, you might have to check before the session to make sure the room has not changed.
I hope to see you there.
New in Oracle 12c: Querying an Associative Array in PL/SQL Programs
Posted: August 2, 2014 Filed under: 12C, PL/SQL | Tags: 12C, PL/SQL 9 CommentsI was aware that up to Oracle 11g, a PL/SQL program wasn’t allowed use an associative array in a SQL statement. This is what happens when I try to do it.
SQL> drop table test_array purge; Table dropped. SQL> create table test_array as 2 select level num_col from dual 3 connect by level <= 10; Table created. SQL> select * from test_array; NUM_COL ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL> drop package PKG_TEST_ARRAY; Package dropped. SQL> create or replace package PKG_TEST_ARRAY as 2 3 type tab_num is table of number index by pls_integer; 4 5 end PKG_TEST_ARRAY; 6 / Package created. SQL> declare 2 my_array pkg_test_array.tab_num; 3 begin 4 for i in 1 .. 5 loop 5 my_array(i) := i*2; 6 end loop; 7 8 for i in ( 9 select num_col from test_array 10 where num_col in (select * from table(my_array)) 11 ) 12 loop 13 dbms_output.put_line(i.num_col); 14 end loop; 15 end; 16 / where num_col in (select * from table(my_array)) * ERROR at line 10: ORA-06550: line 10, column 51: PLS-00382: expression is of wrong type ORA-06550: line 10, column 45: PL/SQL: ORA-22905: cannot access rows from a non-nested table item ORA-06550: line 9, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 13, column 26: PLS-00364: loop index variable 'I' use is invalid ORA-06550: line 13, column 5: PL/SQL: Statement ignored
As you can see, the TABLE operator is expecting either a nested table or a varray.
The limitation has been removed in Oracle 12c. This is what happens now.
SQL> set serveroutput on SQL> SQL> drop table test_array purge; Table dropped. SQL> create table test_array as 2 select level num_col from dual 3 connect by level <= 10; Table created. SQL> select * from test_array; NUM_COL ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL> drop package PKG_TEST_ARRAY; Package dropped. SQL> create or replace package PKG_TEST_ARRAY as 2 3 type tab_num is table of number index by pls_integer; 4 5 end PKG_TEST_ARRAY; 6 / Package created. SQL> declare 2 my_array pkg_test_array.tab_num; 3 begin 4 for i in 1 .. 5 loop 5 my_array(i) := i*2; 6 end loop; 7 8 for i in ( 9 select num_col from test_array 10 where num_col in (select * from table(my_array)) 11 ) 12 loop 13 dbms_output.put_line(i.num_col); 14 end loop; 15 end; 16 / 2 4 6 8 10 PL/SQL procedure successfully completed.
Here’s another example using a slightly different query.
SQL> declare 2 my_array pkg_test_array.tab_num; 3 begin 4 for i in 1 .. 5 loop 5 my_array(i) := i*2; 6 end loop; 7 8 for i in ( 9 select a.num_col, b.column_value 10 from 11 test_array a, 12 table (my_array) b 13 where 14 a.num_col = b.column_value 15 ) 16 loop 17 dbms_output.put_line(i.num_col); 18 end loop; 19 end; 20 / 2 4 6 8 10 PL/SQL procedure successfully completed.
Very nice stuff.