Oracle 12c. New features bring new pitfalls

Hello.

Analyzing the new features of Oracle 12c, here and there, I come across reefs, when not everything works as expected, falls or is simply not obvious. Of course, this is a standard situation when in the first release of the new version a lot of raw ... but, as you know, forewarned is forearmed. Probably, someone will come in handy so as not to repeat my rake.

I am writing a rake in order to attack them.
')
PS: edited: added at the end a bug which I forgot to write right away (use in PL / SQL, the SQL constructs in WITH of which PL / SQL was used).

We acquaint PMON with local listener

Let's start with a simple one, which is not even a bug, but a configuration issue.
Installed in a standard installation and configuration of Oracle 12c (in my case - on Oracle Linux, but, I think, not critical).
- Create a CDB database.
- We switch / create PDB-bases.
By default, the added bases do not appear to listener. Out of the box, PMON did not make friends with a listener living on the same server and automatic registration does not occur. Not a tragedy, however, with your hands to add entries to listener.ora, somehow not at all interesting in the context of the new multitenant architecture feature.
Let's introduce PMON and the locally living listener:

ALTER SYSTEM SET local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521))' scope=both; ALTER SYSTEM REGISTER; 

The base is registered, the listener saw the joy come. It cannot be said that this is a specific feature of the 12s, but it is precisely in it that the opportunity to add / manage databases literally on the move, and within the Multitenant architecture, the automatic registration of databases in a listener is more relevant than ever. Previously, creating a new database was a much larger “event” and adding a couple of lines to listener.ora did not cause me any prejudice.

PDB autorun after reboot

Restart the server. CDB has risen. PDB - not open (during the connection database shutdown or startup in progress error).
I don’t know if it’s right that after rebooting the server the administrator should connect to the CDB database and say

 alter pluggable database all open; 

It seems like just like that the bases do not reboot (should not) ... But something tells me that, to put it mildly, this is not convenient. And DBAs, which support dozens and hundreds of databases at the same time, will obviously not say thanks.
Settings or commands that would convince Orakl that it is necessary to automate PDB-shki all the same, it wasn’t found out (maybe I was looking badly. Tell me in comments if someone found it).
In the all-knowing Internet this moment is no longer a news and the most common recommendation:

 create or replace trigger open_all_pdb after startup on database BEGIN  execute immediate 'alter pluggable database all open'; END open_all_pdbs; 

By personal conviction, I do not really like triggers, but in this case, it seems to be the smallest of the available evils.

Invisible columns

The peculiarity is not Oracle, but PL / SQL developer (version 10.0.1.1694 - downloaded from PL / SQL Developer just recently), but still.
Let's compare the behavior on the invisible sqlplus column (leads in accordance with the documentation):

 SQL> descr test_invisible; Name Null? Type ------------------ -------- ----------- ID NUMBER(38) THIRD_COL NUMBER(38) 

And the PL / QSL Developer command window:

 SQL> descr test_invisible; Name Type Nullable Default Comments --------- ------------ -------- ------- -------- ID INTEGER Y THIRD_COL INTEGER Y INV_COL VARCHAR2(20) Y 

Does not know yet Pl / Sql developer about the new feature, which is not surprising. But not everyone realizes that the command window of a PL / SQL developer is not an honest sql * plus through any pipe, but simply a pseudo-like interface.
I think they will soon come to their senses, but at the first moment I was somewhat surprised and thoughtful.

PL / SQL support in with

According to Orakl, this feature was made primarily to improve performance (let's leave a detailed look at the brackets, for offtopic to the topic of the post), just as we say that pragma UDF works for these purposes no worse, but ...
“BUT” is a bug discovered by Johnathan Lewis and described in his blog.
Add one performance “feature” of the Pohamal (in some cases) - another - DETERMINISTIC.

Consider the example code:

 --         . CREATE TABLE all_ones AS SELECT 1 AS ID FROM dual CONNECT BY LEVEL<100; SET TIMING ON ARRAYSIZE 15 WITH FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN DBMS_LOCK.sleep(1); RETURN p_id; END; SELECT slow_function(ID) FROM all_ones WHERE ROWNUM <= 10; / …. 10 rows selected. Elapsed: 00:00:10.02 

Although, in fairness, it does not appear in all cases:

 WITH FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN DBMS_LOCK.sleep(1); RETURN p_id; END; SELECT slow_function(1) FROM all_ones WHERE ROWNUM <= 10; / 10 rows selected. Elapsed: 00:00:01.01 WITH FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN DBMS_LOCK.sleep(1); RETURN p_id; END; SELECT (SELECT slow_function(id) FROM dual) FROM all_ones WHERE ROWNUM <= 10; / Elapsed: 00:00:01.02 

SQL Text expansion

Another nice innovation is the new procedure DBMS_UTILITY.EXPAND_SQL_TEXT - I already described it earlier in the Habré .
When I tested it, it worked wonderfully both on my view and tables with VPD ..., and for example, on all_users ... however, an attempt to apply it to all_objects led to an error in the dbms_utility package. I guess the reason is that even a user with the DBA role did not find access to some completely internal system objects ... or maybe just a bug in the code.

 DECLARE x CLOB; BEGIN dbms_utility.expand_sql_text(input_sql_text => 'select * from all_objects', output_sql_text => x ); dbms_output.put_line(x); END; ORA-00904: : invalid identifier ORA-06512: at "SYS.DBMS_UTILITY", line 1581 ORA-06512: at line 3 

And here are a couple of things that I did not encounter myself, but it was also interesting to read with others:

DBMS_METADATA and session sequence

Found in one of the topics on sql.ru.
It looks like DBMS_METADATA is not yet aware of the new feature:

 --   sequence create sequence seq session; DECLARE x CLOB; begin x:=dbms_metadata.get_ddl( 'SEQUENCE', 'SEQ'); dbms_output.put_line(x); end; / --   seqence,   session.  . CREATE SEQUENCE "DENKREP"."SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOPARTITION; 

Pagination, arrays and run-time calculation of the number of lines for fetch

Found a person in the blog

 SQL> declare 2 type table_tt is table of employees%rowtype; 3 v_tt table_tt; 4 5 v_limit_nr number:=10; 6 v_counter_nr number:=0; 7 begin 8 select * 9 bulk collect into v_tt 10 from employees 11 offset v_counter_nr*v_limit_nr rows 12 fetch next v_limit_nr rows only; -- variable 13 end; 14 / type table_tt is table of employees%rowtype; * ERROR at line 2: ORA-03113: end-of-file on communication channel Process ID: 3060 Session ID: 20 Serial number: 35307 SQL> --       ,   . ... 12 fetch next 10 rows only; -- hard-code 13 end; 14 / PL/SQL procedure successfully completed. SQL> 

PS: In the comments to the source they wrote that oracle already knew about the bug and bug was added on the metalink # 17404511

PPS: added later (remembered more)

PL / SQL support in SQL with in PL / SQL

The name - oil oil. Let's figure it out.
It seems that PL / SQL is not yet aware of the extension of the SQL language, and does not yet support such SQL constructions:

 SQL> DECLARE dummy NUMBER; BEGIN WITH FUNCTION test_with (n_id IN NUMBER) RETURN NUMBER IS BEGIN dbms_output.put_line(n_id); RETURN n_id; END; SELECT test_with(ID) INTO dummy FROM t1 WHERE ROWNUM < 2; dbms_output.put_line(dummy); END; / WITH FUNCTION test_with (n_id IN NUMBER) RETURN NUMBER IS * ERROR at line 4: ORA-06550: line 4, column 17: PL/SQL: ORA-00905: missing keyword ORA-06550: line 4, column 3: PL/SQL: SQL Statement ignored ORA-06550: line 6, column 4: PLS-00103: Encountered the symbol "SELECT" 

At the same time, it normally works in dynamic SQL (expectedly, but still):

 DECLARE dummy NUMBER; BEGIN EXECUTE IMMEDIATE 'WITH FUNCTION test_with (n_id IN NUMBER) RETURN NUMBER IS BEGIN dbms_output.put_line(n_id); RETURN n_id; END; SELECT test_with(ID) FROM t1 WHERE ROWNUM < 2' INTO dummy; END; 

So far, everything. I hope it was interesting.