Skip navigation.

Gary Myers

Syndicate content
I am a proud Oracle developer and this is my blog.
My website is here
This blog is OPINION, SUGGESTION and DEBATE. Please correct anything I write that is misleading.Gary Myersnoreply@blogger.comBlogger259125
Updated: 14 hours 38 min ago

With PL/SQL and LONGs (and PRODUCT_USER_PROFILE)

Sun, 2015-05-17 00:45
One use for the 12.1.0.2 addition of PL/SQL functions in the WITH clause is to get the HIGH_VALUE of a partition in a usable column format.

with
 FUNCTION char2000(i_tab in varchar2, i_part in varchar2) 
 RETURN VARCHAR2 IS
   v_char varchar2(2000);
 BEGIN
   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 
     'TO_DATE(''%'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')'
   then
      v_char := regexp_substr(v_char,q'{'[^']+'}');
   end if;
   --
   RETURN v_char;
 END;
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 12.1.0.2 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. 


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

Sun, 2015-05-10 00:33
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. 

BUT.....

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:
SET ROLE ALL;
This is a directive to the SQLPlus client
SET TIMING ON
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....
PS.When it comes to naming this tool, bear in mind this is how the XE install refers to the SQL Plus client:


Oracle things that piss me off (pt 1)

Sat, 2015-04-25 16:27

This annoys me.The fact that Oracle thinks it is appropriate to sell me to 'Ask' whenever I update my Oracle JRE. 

On my home machines,I've ditched the Oracle route for JRE. Java runtime is a requirement for running Minecraft (now owned by Microsoft) and they've now incorporated keeping the JRE updated as part of their updates. No attempts to install some crappy piece of spyware on my machine. 
And it is at the stage where I trust Microsoft over Oracle any day of the week.


A world of confusion

Sat, 2015-04-04 02:00
It has got to the stage where I often don't even know what day it is. No, not premature senility (although some may disagree). But time zones.

Mostly I've had it fairly easy in my career. When I worked in the UK, I just had the one time zone to work with. The only time things got complicated was when I was working at one of the power generation companies, and we had to make provision for the 23-hour and 25-hour days that go with Daylight Savings.

And in Australia we only have a handful of timezones, and when I start and finish work, it is the same day for any part of Australia. I did work on one system where the database clock was set to UTC, but dates weren't important on that application.

Now it is different. I'm dealing with events that happen all over the world. Again the database clock is UTC, with the odd effect that TRUNC(SYSDATE) 'flips over' around lunchtime. Now when I want to look at 'recent' entries (eg a log table) I've got into the habit of asking WHERE LOG_DATE > SYSDATE - INTERVAL '9' HOUR

And we also have columns that are TIMESTAMP WITH TIMEZONE. So I'm getting into the habit of selecting COL_TS AT TIME ZONE DBTIMEZONE . I could use sessiontimezone, but then the time component of DATE columns would be inconsistent.  This becomes just a little more confusing this time of year as various places slip in and out of Daylight Savings.

Now things are getting even more complicated for me.

Again, during my career, I've been lucky enough to be pretty oblivious to character set issues. Most things have squeezed in to my databases without any significant trouble. Occasionally I've had to look for some accented characters in people's names, but that's been it.

In the past few months, I've been working with some European data where the issues have been more pronounced. Aside from a few issues in emails, I've been coping quite well (with a lot of help from Google Translate). 

Now I get to work with some Japanese data. And things get complicated.

"The modern Japanese writing system is a combination of two character types: logographic kanji, which are adopted Chinese characters, and syllabic kana. Kana itself consists of a pair of syllabarieshiragana, used for native or naturalised Japanese words and grammatical elements, and katakana, used for foreign words and names, loanwordsonomatopoeia, scientific names, and sometimes for emphasis. Almost all Japanese sentences contain a mixture of kanji and kana. Because of this mixture of scripts, in addition to a large inventory of kanji characters, the Japanese writing system is often considered to be the most complicated in use anywhere in the world.[1][2]"Japanese writing system

Firstly I hit katakana. With some tables, I can get syllables corresponding to the characters and work out something that I can eyeball and match up to some English data. As an extra complication, there are also half-width characters which are semantically equivalent but occupy different codepoints in Unicode. That has parallels to upper/lower case in English, but is a modern development that came about from trying to fit the previously squarish forms into print, typewriters and computer screens.

Kanji is a different order of shock. Primary school children in Japan learn the first 1000 or so characters. Another thousand plus get taught in high school. The character set is significantly larger in total.

I will have to see if the next few months cause my head to explode. In the mean time, I can recommend reading this article about the politics involved in getting characters (glyphs ? letters ?) into Unicode.  I Can Text You A Pile of Poo, But I Can’t Write My Name

Oh, and I'm still trying to find the most useful character/font set I can have on my PC and  use practically in SQL Developer. My current choice shows the Japanese characters when I click in the field in the dataset, but only little rectangles when I'm not in the field. The only one I've found that does show up all the time is really UGLY.