Sunday, May 17, 2015


One use for the addition of PL/SQL functions in the WITH clause is to get the HIGH_VALUE of a partition in a usable column format.

 FUNCTION char2000(i_tab in varchar2, i_part in varchar2) 
   v_char varchar2(2000);
   select high_value into v_char
   from user_tab_partitions a
   where a.table_name = i_tab
   and a.partition_name = i_part;
   if v_char like 
      v_char := regexp_substr(v_char,q'{'[^']+'}');
   end if;
   RETURN v_char;
select table_name, partition_name, 
       char2000(table_name, partition_name) high_val,
       partition_position, tablespace_name, 
       segment_created, num_rows, last_analyzed, 
       global_stats, user_stats
from user_tab_partitions ut
where segment_created='YES'
order by table_name, high_val;

Oracle have spent well over a decade telling us that LONG is deprecated, but still persist in using it in their data dictionary. PL/SQL is the only practical way of getting the values into a more usable data type.

You will want the last version of the SQL Plus client. For SQL, sqlplus treats the semi-colon as a "go off and execute this". PL/SQL has traditionally needed a period on an otherwise empty line to switch from the statement editor to the command prompt.

For example:

Having PL/SQL embedded in the SQL statement confuses the older clients, and we get a bout of premature execution.

In the client, a WITH statement is treated as a PL/SQL statement if it contains PL/SQL (ie needing the period statement terminator). If it doesn't contain PL/SQL then it doesn't (so there's no change required for older scripts). That said, I'd recommend consistently using the period terminator for PL/SQL and SQL.  

The SQLcl client (still beta/early adopter) currently manages the straight select okay, but fails if it is part of a CREATE VIEW. 

Tim Hall has already noted that the WITH PL/SQL doesn't currently work when embedded in a PL/SQL block (such as a procedure), but that is expected in a future release. 

Oh, and while it isn't documented in manual, WITH is its own statement for the purposes of PRODUCT_USER_PROFILE. I can't imagine anyone on the planet is still using PRODUCT_USER_PROFILE for security. If they are, they need to rethink in light of WITH statements and result sets being returned by PL/SQL. 

Sunday, May 10, 2015

Oracle things that piss me off (pt 2) - No Direction

The SQL Developer team has been chugging forward with it's SQL Command Line (sqlcl) tool.

As I developer, I understand where they are coming from. SQL Developer benefited from being able to run scripts built for the SQL*Plus command line tool. Then there's the temptation to add a few more useful titbits to the tool. And if it is built 'properly', then it would be relatively easy to decouple it from the GUI and have it as a stand-alone. 


where's the big picture ?

I'm pretty sure (but happy to be corrected) that "SQL Developer" is part of the 12.1 database installation. It is certainly referenced in the guides. So I'd assume that the next 12.2 release will have "SQL Developer" and "sqlcl" command line tool and SQL Plus. I couldn't guess whether the sqlplus will be offered as a last gasp, "to be deprecated" option or whether the long term plan is to supply two SQL command line tools.

Unix/Linux users are probably used to something similar, as they generally have the options of different shells, such as bash, ksh, csh etc. But to remedy any confusion, scripts are generally written with a shebang so it can automatically work out which of the available shells it should use.

What DBAs are most likely to end up with is a script for which they'll have to guess whether it is aimed at sqlplus or sqlcl (or, if they are lucky, a comment at the start of the code).

Having the clients "sort of" compatible makes it worse. It is harder to tell what it is aimed at, and what might go wrong if the incorrect client is used. Plus opting for compatibility perpetuates some of the dumb crud that has accumulated in sqlplus over the decades.

For example:
This is an SQL statement:
This is a directive to the SQLPlus client
You could tell the subtle difference between the SET as SQL statement and SET as sqlplus directive by the semi-colon at the end. Except that both sqlplus and sqlcl will happily accept a semicolon on the end of a 'local' SET command.

If you think it is hard keeping track of what commands are processed by the database, and what are processed by the client, we also have commands that do both.

16:01:49 SQL> select sysdate, to_char(sysdate), cast(sys_context('USERENV','NLS_DATE_FORMAT') as varchar2(20)) dt_fmt,
  2          cast(sys_context('USERENV','NLS_CALENDAR') as varchar2(20)) cal
  3          from dual;

SYSDATE                 TO_CHAR(SYSDATE)   DT_FMT               CAL
----------------------- ------------------ -------------------- --------------------
10/MAY/15               10/MAY/15          DD/MON/RR            GREGORIAN

16:02:35 SQL> alter session set nls_date_format = 'DD/Mon/YYYY';

Session altered.

16:02:40 SQL> select sysdate, to_char(sysdate), cast(sys_context('USERENV','NLS_DATE_FORMAT') as varchar2(20)) dt_fmt,
  2          cast(sys_context('USERENV','NLS_CALENDAR') as varchar2(20)) cal
  3          from dual;

SYSDATE            TO_CHAR(SYSDATE)     DT_FMT               CAL
------------------ -------------------- -------------------- --------------------
10/May/2015        10/May/2015          DD/Mon/YYYY          GREGORIAN

To clarify this, the statement returns one column as a DATE, which will be converted to a string by the client according to its set of rules, and one column as a string converted from a DATE by the database's set of rules.

The ALTER SESSION has been interpreted by both the client AND the server.

This becomes obvious when we do this:

16:02:44 SQL> alter session set nls_calendar='Persian';

Session altered.

16:06:22 SQL> select sysdate, to_char(sysdate),
  2       cast(sys_context('USERENV','NLS_DATE_FORMAT') as varchar2(20)) dt_fmt,
  3       cast(sys_context('USERENV','NLS_CALENDAR') as varchar2(20)) cal
  4       from dual;

SYSDATE                 TO_CHAR(SYSDATE)       DT_FMT               CAL
----------------------- ---------------------- -------------------- ----------
10 May       2015       20 Ordibehesht 1394    DD Month YYYY        Persian

The database knows what to do with the Persian calendar, but the sqlcl client didn't bother. SQLPlus copes with this without a problem, and can also detect when the NLS_DATE_FORMAT is changed in a stored procedure in the database rather than via ALTER SESSION. I assume some NLS values are available/fed back to the client via OCI.

If I was going for a brand-new SQL client, I'd draw a VERY strong line between commands meant for the client and commands intended for the database (maybe a : prefix, reminiscent of vi). I'd also consider that some years down the track, I might be using the same client to extract data from the regular Oracle RDBMS, their mySQL database, a cloud service.... 

To be honest, I'd want one tool that is aimed at deploying DDL to databases (procedures, new columns etc) and maybe data changes (perhaps through creating and executing a procedure). A lot of the rest would be better off supplied as a collection of libraries to be used with a programming language, rather than as a client tool. That way you'd get first class support for error/exception handling, looping, conditions....

When it comes to naming this tool, bear in mind this is how the XE install refers to the SQL Plus client: