Pages

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�� >




Thursday, June 04, 2015

ORDS - Auto REST table feature


Got a question on how easy it is to use ORDS to perform insert | update | delete on a table.  Here's the steps.

1) Install ORDS ( cmd line or there's a new wizard in sqldev )

2) Enable the schema and table in this case klrice.emp; ( again there's a wizard in sqldev )


BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'KLRICE',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'klrice',
                       p_auto_rest_auth => FALSE);
    

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'KLRICE',
                       p_object => 'EMP',
                       p_object_type => 'TABLE',
                       p_object_alias => 'emp',
                       p_auto_rest_auth => FALSE);
    
    commit;

END;



3) Check the catalog.  Everything enabled will be in the catalog wether they be auto-enabled tables or lovingly crafted ones.  For this case, we can see /emp/ is there



4) Do work.  Here I'm inserting a new record.  The PK is referenced in the URI and in the JSON doc.  Notice the http method here is a PUT and that the content type is set to application/json.



5) The results.  It worked



6) Now for doing an update.





7) The results is that the response back from ORDS is the correct values.




8) Now go show the people hand coding REST what took 1 plsql block to enable !

Wednesday, June 03, 2015

Javascript Inside the Database


Where there's a will, there's a way. Not sure on the best use of this off hand but here it is.

Javascript inside an Oracle Database.

Since Javascript is included in java via the ScriptEngine framework. It's fairly easy to add a java wrapper to call the javascript engine.
create or replace and compile java source named "EvalScript" as
import javax.script.*;
public class EvalScript {
    public static String eval(String argument) throws Exception {
        // create a script engine manager
        ScriptEngineManager factory = new ScriptEngineManager();
        // create a JavaScript engine
        ScriptEngine engine = factory.getEngineByName("javascript");

        // set the param into the javascript context
        engine.put("s", argument);

        // evaluate JavaScript code from String
        return  engine.eval("var a='hi';  a + ', ' + s;").toString();
        //return "a";
    }
}
/

Then the standard plsql wrapper to call a java stored procedure.
create or replace function eval (argument in varchar2)
 return varchar2  as language java
name 'EvalScript.eval(java.lang.String) return java.lang.String';


Now you can simply call that function and the return is from javascript OR make a REST call via ORDS.

This is a simple REST call that takes in {name} and passes it to the function just created.






Then pass in the value to be passed in to the javascript engine and you now have ORDS + DB + Server Side javascript.




Tuesday, May 05, 2015

REST Data Services and SQL Developer












The database tools team released 3 new GA releases and an update to our SQLCL.


Official Releases are here:

   SQL Developer, Modeler, and Data Miner:
       https://blogs.oracle.com/otn/entry/news_oracle_updates_development_tools
       https://blogs.oracle.com/datamining/entry/oracle_data_miner_4_1

  REST Data Services now with SODA
       https://blogs.oracle.com/otn/entry/news_oracle_rest_enables_oracle
       https://blogs.oracle.com/databaseinsider/entry/oracle_database_12c_as_a

Some of the feedback we received was that ORDS was difficult to setup and running.  This prompted an overhaul of the install process.  There is now a few ways to get going.  As with earlier releases, there is a command line.  There is now a 'simple' , 'advanced' , and an option to parameterize ( silent ) install.  The simple is the default and will ask minimal information.  The catch is that it will make assumptions.  Some of those assumptions like a USERS tablespace may not work for your database.  In this case try the advanced, it will prompt for _everything_ and then some for a fully customized install.





Taking the simple to install one step more, REST Data Services is now embedded into SQL Developer to be able to get up and running faster than ever.  Under the Tools menu there is now options to Manage,Run,Install, and Uninstall ( although why would anyone? ).




The first page of the installation will ask for 2 things.  First if there is a specific ORDS to use or to use the embedded one.  The version number is printed for which ever is chosen to ensure the proper decision.  The second is simply where to store the configuration.  This configuration is just the normal files and could be portable to take to any other ORDS to set it up.

The next is the database credentials and connect strings.  There is something new here in that the user is now ORDS_PUBLIC_USER.  This is a change that has come in the decoupling from Application Express.  Now ORDS is completely standalone and Apex is no longer required.
If connecting to the ORDS_PUBLIC_USER fails ( as in user doesn't exist ),  the wizard will prompt for the credentials needed to install ORDS.
When using APEX or any OWA toolkit based applications, the db user for those to be executed as needs to be entered.

In ORDS 2.x, the repository where the REST definitions were stored was inside Application Express' repository.  ORDS 3.0 has it's on dedicated ORDS_METADATA database user which holds this information.  This means that there could be definitions in both places.  For Apex 5.0, the screens in the sql workshop will continue to operate against the tables in the Apex repository.  That means to continue to operate this wizard will have to be filled out for the 2.0 users to access this data.

When operating like this.  ORDS will use the metadata from both the 3.0 repository and the Apex repository.  This allows the flexibility to the developer where they would continue to develop.  The new features such as Auto Enablement will only be in the new ORDS 3.0 repository.

What this means to you.
  - Continue to use the APEX screens, everything will work just fine
  - Using the REST definitions in SQL Developer, will go into the new ORDS schema
  - If there is a conflict, ORDS repository will be chosen over the Apex repository.




This screen will give the option to start ORDS up as soon as the wizard finishes installing and setting up.  Also is the location for the static file, think /i/ for apex.


The ORDS Administrator and ORDS RESTful User are used to connect to the running ORDS from SQL Developer for remote administrations.

A quick final review of the options and it's up and running.




Friday, May 01, 2015

Logstash and Oracle Database

    For anyone that doesn't know what Logstash is head over to http://logstash.net/ and watch the video on the home page.  Mark Rittman has done a great blog post on the full ELK stack with OBIEE.

    This blog post is a first cut at an input filter for Logstash to gather metrics, logs, or anything that can be expressed in sql.  A huge caution that this is a 0.01 attempt and will get better as I need it to.  This is a basic config of doing a select sessions every 5 seconds and report that in this case to just STDOUT.  There are many choices of where to send this information like to elastic search for visualization into Kibana as Mark showed in the OBIEE example.

    If anyone uses this, changes it, likes it, doesn't like it,  let me know.

input { 
  orasql { sql      => "SELECT count(1) sessions from v$session "
           dbuser   => "klrice"
           dbpasswd => "klrice"
           dburl    => "localhost/orcl"
           interval =>  5
      } 
}
output {
 stdout { codec => rubydebug }
}

The running of this is just needs to add in the plugin path for be able to find the orasql input filter.

     The filter itself is where everything oracle-wise is happening.  Since Logstash is written in JRuby, I'm able to use the normal Oracle JDBC Driver to access the database.  For this case I have a ./lib folder where I have a local copy.  This could just as easily be $ORACLE_HOME/jdbc/lib/ojdbc6.jar to ensure using the same as the database version when on the same server.

     Since the connect and establishing a session is expensive when repeatedly issuing sql, I have kept a connection open.  The connection could be closed for any number of reasons so there's an auto-reconnect in here also by calling the getConnection before any sql exec.   Before someone asks: no, sqldev will not get an auto-reconnect like this.




# encoding: utf-8
require "logstash/inputs/base"
require "logstash/namespace"
require "socket" # for Socket.gethostname
require 'java'
# SETUP THE JDBC DRIVER TO YOUR LOCATION
$CLASSPATH << "lib/ojdbc6.jar"

java_import 'oracle.jdbc.OracleDriver'
java_import 'java.sql.DriverManager'


# Run sql line tools and capture the whole output as an event.
#
# Notes:
#
# * The '@source' of this event will be the sql run.
# * The '@message' of this event will be the entire stdout of the sql
#   as one event.
#
class LogStash::Inputs::OraSQL < LogStash::Inputs::Base

  config_name "orasql"
  milestone 2
   
  $currConnection = nil

  default :codec, "plain"

  # Set this to true to enable debugging on an input.
  config :debug, :validate => :boolean, :default => false, :deprecated => "This setting was never used by this plugin. It will be removed soon."

  # SQL to run. For example, "select * from emp"
  config :sql, :validate => :string, :required => true

  # dbuser to run. For example, "select * from emp"
  config :dbuser, :validate => :string, :required => true ,  :default => "/"

  # dbpass to run. For example, "select * from emp"
  config :dbpasswd, :validate => :string, :required => false , :default => ""

  # dburl to run. For example, "select * from emp"
  config :dburl, :validate => :string, :required => true , :default => "//localhost/orcl"

  # Interval to run the sql. Value is in seconds.
  config :interval, :validate => :number, :required => true , :default => 120

  public
  def register
    @logger.info("Registering SQL Input", :type => @type,
                 :sql => @sql, :interval => @interval)
  end # def register
  
  public 
  def getConnection
      if $currConnection == nil  or  ! $currConnection.isValid(100)  
           oradriver = OracleDriver.new
           DriverManager.registerDriver oradriver

           con_props = java.util.Properties.new
           con_props.setProperty("user", @dbuser)
           con_props.setProperty("password", @dbpasswd )
 
           conn =  Java::oracle.jdbc.OracleDriver.new.connect('jdbc:oracle:thin:@' + @dburl, con_props)

           conn.auto_commit = false

           $currConnection = conn
      end 
      
      return $currConnection  
       
  end # end getConnection

  public
  def run(queue)
    hostname = Socket.gethostname
   
    loop do
      start = Time.now
      @logger.info? && @logger.info("Running SQL", :sql => @sql)

      conn = getConnection

      stmt = conn.prepare_statement @sql
      rset = stmt.execute_query
      while ( rset.next )
         i=1
         event =  event = LogStash::Event.new
         decorate(event)
         cols = rset.getMetaData.getColumnCount
         msg = ""
         r=0
         while ( i <= cols ) 
             val = rset.getString(i)
             if ( val != nil ) 
                if ( r > 0 )
                   msg = msg + ","
                end
                event[ rset.getMetaData.getColumnName(i).downcase ] =  val
                msg = msg +  "\"" +rset.getMetaData.getColumnName(i).downcase +  "\" : \"" + val + "\"" 
                r=r+1
             end
             i = i + 1
         end
         event['message'] = "{" + msg + "}"
        queue << event
      end
      conn.close

      duration = Time.now - start
      @logger.info? && @logger.info("Command completed", :sql => @sql,
                                    :duration => duration)

      # Sleep for the remainder of the interval, or 0 if the duration ran
      # longer than the interval.
      sleeptime = [0, @interval - duration].max
      if sleeptime == 0
        @logger.warn("Execution ran longer than the interval. Skipping sleep.",
                     :sql => @sql, :duration => duration,
                     :interval => @interval)
      else
        sleep(sleeptime)
      end
    end # loop
  end # def run
end # class LogStash::Inputs::OraSQL