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:
1 comment:
The big picture...to build the best tools we can to help our customer build and maintain the best applications they can with Oracle Database.
SQLcl is in early adopter, beta mode.
The idea is for it to do everything that SQL*Plus does, but also bring in more modern IDE type features such as statement completion, command recall. Additionally, add commands that we know are popular in SQL Developer, such as exporting resultsets to CSV, generating DDL for objects, etc.
Our 'truth' point for whether something is working or not is - does it work that way in SQL*Plus? That will generally be our 'truth' for SQLcl. In fact, the script engine in SQL Developer is exactly the same code in SQLcl. So where we improve SQLcl, we're also improving the scripting support in SQL Developer.
Where you're finding discrepancies between SQLcl and SQL*Plus should be report to the OTN Community Forum for SQL Developer so we can address it.
Once it goes GA, report those same issues to My Oracle Support.
SQLcl is part of SQL Developer. You can find our statement of direction for that here. Now that v4.1 is live, I suppose we need to update that.
Post a Comment