Tuesday, June 16, 2015

SQLcl , yet again

By the Numbers


There's a new SQLcl out for download.  In case, there are too many to keep track of the build numbers are quite easy to tell if you have the latest.  The build posted today is  sqlcl-4.2.0.15.167.0827-no-jre.zip

Here's what we are doing
4.2.0 <-- doesn't matter at all
15     <- year
167   <- day in julian
0827 <- time the build was done

So yes, this build was done today at 8am !


Info on PL/SQL

I don't think I covered this before.  The info on any plsql gives you actual useful stuff.  It prints out a stub of pl/sql that can be copy and pasted to get going much quicker than looking at a describe tabular output of args.  I'll pick on dbms_metadata which is a huge package.



This can still be way too much 'info' if the packages are very large.  The other part of info is that you can get a subset.  Here the ".a" was added to filter it down.  I'll blog more on why I'm showing metadata as there's some pretty interesting usages that I just found how to use.



Show All

Not much to see here.  Just looks like it should.

Show All+

Much like the info/info+ we added lots more.  Just scroll over this or try it out and see how much more is there.  It should give you just about anything you may be interested in.




KLRICE@orcl🍺 >show all+

Show All:
========================================
appinfo is OFF and set to "SQL Command Line"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
BTitle OFF
colsep " "
concat "." (hex 5c)
copycommit 0
define "&"
echo OFF
escape OFF
FEEDBACK ON for 6 or more rows
heading ON
headsep "|" (hex 7c)
linesize 136
long 80
longchunksize 80
null null
numformat ""
numwidth 10
pagesize 44
PAUSE is OFF
release 1201000200
scan ON
serveroutput OFF
space " "
spool OFF
sqlcode 0
sqlprompt "@|red KLRICE|@@@|green orcl|@@|blue 🍺 >|@ "
suffix "sql"
termout ON
time OFF
timing OFF
trimout OFF
trimspool OFF
TTitle OFF
USER is "KLRICE"
verify ON
wrap : lines will be wrapped

Show All+ (Single Line):
========================================
_restrict 0
buffer SQL
CD : Default
commandline true
con_id 3
con_name ORCL
encoding "UTF-8"
LDAPCON : Default
net: ON
noverwrite: WARN
null null
SQL Format : ansiconsole
SQLPATH : /Users/klrice/workspace/raptor_common/:.
systemout "true"
Toplevel TRUE 
url top_base_url ""    base_url ""    node_url (Parent) "file:/Users/klrice/workspace/raptor_common"
xquery BASEURI "" CONTEXT "" NODE DEFAULT ORDERING DEFAULT

Show All+ (Multi Line):
========================================

CONNECTION:
========================================
CONNECTION:
 KLRICE@jdbc:oracle:thin:@localhost:1521/orcl 
CONNECTION_IDENTIFIER:
 orcl
CONNECTION_DB_VERSION:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
NOLOG:
 false
PRELIMAUTH:
 false

DDL:
========================================
STORAGE : ON
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

DEFINES:
========================================
DEFINE _DATE =  "16-JUN-15" (CHAR)
DEFINE _CONNECT_IDENTIFIER =  "orcl" (CHAR)
DEFINE _USER =  "KLRICE" (CHAR)
DEFINE _PRIVILEGE =  "" (CHAR)
DEFINE _SQLPLUS_RELEASE =  0401000000 (NUMBER)
DEFINE _EDITOR =  "vim" (CHAR)
DEFINE _O_VERSION =  "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE =  1201000000 (NUMBER)
DEFINE _PWD =  "/Users/klrice/workspace/raptor_common" (CHAR)

INSTANCE:
========================================
USERNAME KLRICE
INSTANCE_NAME cdb1
HOST_NAME klrice-nsvpn-dhcp-141-144-19-110.vpn.oracle.com
SID 21
VERSION 12.1.0.2.0
STARTUP_DAY 20150616

INTERNALERRORS:
========================================
sqldev.error "false"
sqldev.error.any.in "false"
script.runner.autocommit.errorflag "" 
sqldev.last.err.message "" 
sqldev.last.err.message.forsqlcode "" 

JDBC:
========================================
-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Major Version: 12
Database Minor Version: 1
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 12.1.0.2.0
Driver Major Version: 12
Driver Minor Version: 1
Driver URL: jdbc:oracle:thin:@localhost:1521/orcl

NLS:
========================================
DB_TIMEZONE +00:00
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
SESSION_TIMEZONE America/New_York
SESSION_TIMEZONE_OFFSET -04:00

PDBS:
========================================
CON_ID  NAME  OPEN_MODE   RESTRICTED  
3       ORCL  READ WRITE  NO          


SGA:
========================================
Total System Global Area   838860800 bytes      
Fixed Size                   2929936 bytes      
Variable Size              545262320 bytes      
Database Buffers           285212672 bytes      
Redo Buffers                 5455872 bytes      

KLRICE@orcl🍺 >