Oracle BFILENAME Function Explained with Examples

Complete IT Professional - Thu, 2016-08-11 06:00
The Oracle BFILENAME function is a useful file manipulation function. Learn what it is and how to use it in this article. Purpose of the Oracle BFILENAME Function The BFILENAME function returns an object called a “BFILE locator” from a specified directory and filename. This function is often used in PL/SQL to access the data […]
Categories: Development

Why Asking Myself How To Add Value At Work Is The Best Thing I’ve Done This Month

Complete IT Professional - Mon, 2016-08-08 06:00
I’ve been doing something at work which has been getting great results. I only started doing a lot of it a few weeks ago. But, I’ve had a few people notice, and I can see the positive impact it’s having on the team. I want to share it in this article so you can try […]
Categories: Development

Installing SQLcl on OEL/RHEL

Dimitri Gielis - Thu, 2016-08-04 16:55
In my previous post I talked about how SQLcl came in handy to work with JavaScript against the database.

The installation of SQLcl is easy... you just download the zip, unpack and run the executable.

But to be fair, before I got SQLcl running (especially the script part) I encountered a number of issues, so hopefully this post helps you be able to run SQLcl with all features in minutes as it's meant to be :)

Those were the error messages I received when running sql (script):

javax.script.ScriptException: Java class "java.util.ArrayList" has no public instance field or method named "0".

javax.script.ScriptException: ReferenceError: "Java" is not defined. (#1) in at line number 1

The solution for me was to upgrade my Java version to Java 8.

Here're the steps on my OEL/RHEL system to upgrade Java:

$ cd /opt

$ wget --no-cookies --no-check-certificate --header "Cookie:; oraclelicense=accept-securebackup-cookie" ""

$ tar xzf jdk-8u102-linux-x64.tar.gz 

cd jdk1.8.0_102/

alternatives --install /usr/bin/java java /opt/jdk1.8.0_102/bin/java 2
$ alternatives --config java

There are 5 programs which provide 'java'.

  Selection    Command
   1           /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
   2           /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
   3           /usr/lib/jvm/jre-1.5.0-gcj/bin/java
*+ 4           /usr/java/jre1.8.0_101/bin/java
   5           /opt/jdk1.8.0_102/bin/java

Enter to keep the current selection[+], or type selection number: 5

$ alternatives --install /usr/bin/jar jar /opt/jdk1.8.0_102/bin/jar 2
$ alternatives --install /usr/bin/javac javac /opt/jdk1.8.0_102/bin/javac 2
$ alternatives --set jar /opt/jdk1.8.0_102/bin/jar
$ alternatives --set javac /opt/jdk1.8.0_102/bin/javac
$ java -version
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

$ export JAVA_HOME=/opt/jdk1.8.0_102
export JRE_HOME=/opt/jdk1.8.0_102/jre
$ export PATH=$PATH:/opt/jdk1.8.0_102/bin:/opt/jdk1.8.0_102/jre/bin

Now when running SQLcl everything worked like a charm. Hurray :)

Categories: Development

Oracle SESSIONTIMEZONE Function with Examples

Complete IT Professional - Thu, 2016-08-04 06:00
In this article, I’ll explain what the Oracle SESSIONTIMEZONE function is, and show you some examples. Purpose of the Oracle SESSIONTIMEZONE Function The SESSIONTIMEZONE function returns the timezone offset of your session, in the format of [+|-]TZH:TZM, or a time zone region name. This can be helpful to know, especially if you’re doing a lot of […]
Categories: Development

SQLcl to the rescue when the Database and APEX fail (with JSON limitations)

Dimitri Gielis - Wed, 2016-08-03 17:29
In the last two years I've been using JSON in almost every project I was involved in.
For example with APEX Office Print our plugin is sending JSON to the backend. This JSON you can actually see yourself in the Remote Debug screen in your AOP Dashboard.
Another example is the wearables project (IoT) I showed at KScope 16; the wearable is sending data to a smartphone or tablet, which in his turn is doing a call to our backend (in ORDS) and sending JSON across.

At the end of the day we want the data in the Oracle Database, so our APEX apps can work with that data.

Since Oracle DB 12c, JSON is supported straight from the database. I wrote a number of blog posts how to read JSON from SQL within the database. Here's a quick demo of JSON in the database:

SQL> create table tbl_with_json (
  2    json_clob  clob, 
  3    constraint json_clob_chk check (json_clob is json)
  4  );

Table TBL_WITH_JSON created.

SQL> insert into tbl_with_json (json_clob) values ('{
  2      "items": [{
  3          "client_id": -1,
  4          "registration_date": "2016-07-29T07:46:09.941Z",
  5          "question": "My Question",
  6          "description": "My huge clob"
  7      }]
  8  }');

1 row inserted.

SQL> select a.json_clob.items.question as question, a.json_clob.items.description as description 
  2    from tbl_with_json a;

My Question                                                                                                                                                           
My huge clob                                                                                                                                                          

Now the reason of this blog posts: what if your JSON contains some very big text (>32K) in a single node e.g. in the description field? 

If you want to follow along in your own test case, open the description record in SQL Developer for example and past a large text (>32K) in the description node (so replace "My huge clob" with some other big text). Tip: For my test cases I typically use a Lorem Ipsum generator where I can specify the number of characters for example 33000 characters.

How can we parse this JSON and store for example the content of that in a CLOB field?

As I'm on 12c, should be simple right? The database is supporting reading JSON from SQL, so I first tried with JSON_TABLE, but there you can only define VARCHAR2 or NUMBER as data type, no CLOB, so went with VARCHAR2.

Here's the result:

SQL> select jt.question, jt.description
  2    from tbl_with_json, 
  3         json_table(json_clob, '$.items[*]'
  4           columns (
  5             question     varchar2 path '$.question',
  6             description  varchar2 path '$.description'
  7           )
  8*        ) as jt;

My Question                                                                                                                                                           

Oracle just returns null (nothing - blank) for the description!

But it's definitely not blank:

Next I tried the query like in my initial example, but the result was the same:

SQL> select a.json_clob.items.question as question, a.json_clob.items.description as description 
  2    from tbl_with_json a;

My Question                                                                                                                                                           

So the database will return a value when there's less than 4K (or possibly 32K depending the setting of your varchar2 size in the database) and it returns null when it's over this limit.

Hopefully Oracle Database 12.2 fixes this issue, but at the moment there's no native way to get to that data by using the Oracle supplied JSON functions.

Ok, what can we try next?...

Since Oracle Application Express 5, APEX comes with a very nice package to work with JSON, APEX_JSON. This package has been heaven for us, especially with AOP.
So I thought to try to use the APEX_JSON.PARSE and store it in a temporary JSON so I can read it with the get_clob_output method:

SQL> declare
  2    l_data clob;
  3    l_json apex_json.t_values;
  4    l_return clob;
  5  begin
  6    select json_clob
  7      into l_data
  8      from tbl_with_json;
  9    apex_json.parse(l_json, l_data) ;
 10    apex_json.initialize_clob_output(, true, 0) ;
 11    apex_json.open_object;
 12    apex_json.write(l_json, 'items[1].description') ;
 13    apex_json.close_object;
 14    l_return := apex_json.get_clob_output;
 15    apex_json.free_output;
 16  end;
 17  /

Error starting at line : 1 in command -
  l_data clob;
  l_json apex_json.t_values;
  l_return clob;
  select json_clob
    into l_data
    from tbl_with_json;
  apex_json.parse(l_json, l_data) ;
  apex_json.initialize_clob_output(, true, 0) ;
  apex_json.write(l_json, 'items[1].description') ;
  l_return := apex_json.get_clob_output;
Error report -
ORA-20987: Error at line 6, col 18: value exceeds 32767 bytes, starting at Lorem ipsum dolor sit amet, consectetuer adipiscin
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 928
ORA-06512: at "APEX_050000.WWV_FLOW_JSON", line 993
ORA-06512: at line 9

But as you can see, there's a limit in there as well. So APEX 5 doesn't return null, but it returns an error. Hopefully a future version of APEX removes this limit ;)

When I work with data, I prefer to do it straight in the database, but now I was stuck. At those moments you have to go for a walk, get some sleep and talk to others to get more ideas... My preferred development languages (in this order) are APEX, SQL, PL/SQL, JavaScript, Node.js, ... (and then all others)

Then I remembered a blog post of Kris Rice that SQLcl has the ability to run JavaScript too because  SQLcl includes Nashorn (A Next-Generation JavaScript Engine for the JVM). So after looking at some SQLcl script examples, I wrote my own little SQLcl script that reads out the clob and puts it in a variable "content":

SQL> script
  2     var Types = Java.type("java.sql.Types")
  3     var BufferedReader = Java.type("")
  4     var InputStreamReader = Java.type("")
  6     var GET_CLOB = "declare " + 
  7                    "   l_clob CLOB; " + 
  8                    " begin " + 
  9                    "   select json_clob " + 
 10                    "    into l_clob " + 
 11                    "    from tbl_with_json; " +
 12                    "   ? := l_clob;" + 
 13                    " end;"; 
 15     var cs = conn.prepareCall(GET_CLOB);
 16     cs.registerOutParameter(1, Types.CLOB);
 17     cs.execute();
 18     var clob = cs.getClob(1);
 19     cs.close();
 21     var r = new BufferedReader(new InputStreamReader(clob.getAsciiStream(), "UTF-8"))
 22     var str = null; 
 23     var content = "";
 24     while ((str = r.readLine()) != null) { content = content + str; }
 25     ctx.write(content);
 26  /
{ "items": [{ "client_id": -1, "registration_date": "2016-07-29T07:46:09.941Z", "question": "My Question", "description": "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. Donec pede justo, fring

So the above reads the content of the clob which contains the JSON.
As we are in JavaScript I thought we can parse this JSON and navigate to the description field. Once we have it we store it in another table or do whatever we want with it.
Cool if it would work, no? And it did! :)

So lets finish this example. First we create a table to store the description field (the very big text).

SQL> create table tbl_with_description (description clob);


Here's the final script that will store the description node to another table :
- the ctx.write calls are there to send debug output
- the obj.items[0].description is how we get to the description node and we store that in a bind variable and execute another insert statement to save the description value:

SQL> script
  3  try {
  4     var Types = Java.type("java.sql.Types")
  5     var BufferedReader = Java.type("")
  6     var InputStreamReader = Java.type("")
  8     var GET_CLOB = "declare " + 
  9                    "   l_clob CLOB; " + 
 10                    " begin " + 
 11                    "   select json_clob " + 
 12                    "    into l_clob " + 
 13                    "    from tbl_with_json; " +
 14                    "   ? := l_clob;" + 
 15                    " end;"; 
 17     var cs = conn.prepareCall(GET_CLOB);
 18     cs.registerOutParameter(1, Types.CLOB);
 19     cs.execute();
 20     var clob = cs.getClob(1);
 21     cs.close();
 23     var r = new BufferedReader(new InputStreamReader(clob.getAsciiStream(), "UTF-8"))
 24     var str = null; 
 25     var content = "";
 26     while ((str = r.readLine()) != null) { content = content + str; }
 27     //ctx.write(content);
 29     var obj = JSON.parse(content);
 30     ctx.write("Question: " + obj.items[0].question + "\n");
 31     ctx.write("Description: " + obj.items[0].description + "\n");
 33     var binds =  {};
 34     binds.description = obj.items[0].description;
 36     var ret = util.execute("insert into tbl_with_description (description) values (:description)", binds);
 38     if (ret) {
 39       ctx.write("Insert done!\n");
 40     } else {
 41       ctx.write("Error :(\n");
 42       var err = util.getLastException();      
 43       ctx.write("\nERROR:" + err + "\n");  
 44     }
 46  } catch(e){
 47      ctx.write(e +"\n")
 48      e.printStackTrace();
 49  }
 51  /
Question: My Question
Description: Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis ... eu,
Insert done!


SQL> select count(*) from tbl_with_description;


SQL> select substr(description,1,50) from tbl_with_description;

Lorem ipsum dolor sit amet, consectetuer adipiscin                              


I was blown away by this... and I see a lot of potential be able to run JavaScript against the database.

There's actually a way to load Nashorn in your database too, so you can do JavaScript, Node.JS etc. straight from your database. Nashorn came with Java 8, but it should run in Java 7 too, now the default version of Java in the Oracle Database is 6, so there're some extra steps to do to get it to work. Running JavaScript from the database is something I've on my list to do R&D in and I actually submitted an abstract to KScope17 where I will present my results on this topic (if it gets accepted!) :) 

So to recap this (longer) blog posts:
1) JSON is being used a lot these days and having the possibility to work with JSON in the Oracle database is very nice, but as we have seen in the above example, it can't do everything yet. It has a real issue with large nodes.
2) Knowing other languages and thinking out-of-the-box might come in handy; I would even say that JavaScript becomes more and more important for an APEX developer.
3) SQLcl is a great tool, if you don't use it yet, I would definitely recommend looking into it. 
4) Oracle Nashorn opens up an entire new set of possibilities.

In the last paragraph of this blog post I want to thank Kris Rice for his help understanding SQLcl script. Although there are many examples, it took me some time to get going and I did struggle to understand how to get to error messages for example. Although it's mostly JavaScript in the script, having some Java knowledge makes it easier. Time to refresh that a bit, it has been 15 years ago I did some real Java coding.

Hope this blog post will help you work with JSON and JavaScript within an Oracle context.
Categories: Development

Oracle 12c New Features for Developers

Complete IT Professional - Mon, 2016-08-01 06:00
Oracle 12c is Oracle’s latest version of their database engine. Learn about all of the new Oracle 12c new features for developers in this massive guide. Introduction Oracle 12c comes with a range of new features. Many of these features are great improvements for developers, and some of them are more focused on database administrators. […]
Categories: Development

Oracle FROM_TZ Function with Examples

Complete IT Professional - Thu, 2016-07-28 06:00
In this article, I’ll explain what the Oracle FROM_TZ function is and show you some examples. Purpose of the Oracle FROM_TZ Function The purpose of the FROM_TZ function is to convert a TIMESTAMP value and a specified TIME ZONE to a TIMESTAMP WITH TIME ZONE value. It seems like a simple data conversion function, but […]
Categories: Development

Oracle TZ_OFFSET Function with Examples

Complete IT Professional - Wed, 2016-07-27 06:00
The Oracle TZ_OFFSET function is a useful datetime function. Learn how to use this function and see some examples in this article. Purpose of the Oracle TZ_OFFSET Function The purpose of the TZ_OFFSET function is to display a number that indicates the number of hours from UTC that a specified time is. You can specify […]
Categories: Development

CI, DevOps and ALM for Oracle SOA Suite with Oracle Developer Cloud Service

Shay Shmeltzer - Tue, 2016-07-26 18:31

We have a lot of developers who are using JDeveloper to develop applications with Oracle SOA Suite, and in this blog I wanted to show them how the combination of JDeveloper along with Oracle Developer Cloud Service can help automate their whole development and delivery lifecycle.

One unique aspect of Developer Cloud Service is that it has an instance of JDeveloper available in the build environment. This allows customers who are building Oracle SOA artifacts to leverage the OJDeploy mechanism to package their applications as part of a continuous integration cycle just like they do during development time.

With the improved DevCS integration that we added in JDeveloper 12.2.1, developers can go beyond integration with the Git server offered by DevCS and can now interact with the DevCS task tracking system directly as well as associate code changes to specific tasks they are working on.  

In this 10 minutes video I show:

  • Creating Ant based builds for Oracle SOA artifacts
  • Automating Continuous Integration build and packaging for Oracle SOA from Developer Cloud Service
  • Managing SOA project code with Git and Developer Cloud Service 
  • Tracking tasks from JDeveloper and monitor agile development in Developer Cloud Service

By the way, for those who rather use Maven to automate their builds - this is totally possible and supported in DevCS as well. There is a set of videos that show you how to do that here

(If you haven't seen how quick and easy it is to create a DevCS project, create a git repo, track tasks and create agile team boards - see this video and this one too).

Note that developers get access to Developer Cloud Service with every subscription to the Oracle SOA Cloud Service. 

Here are the two ant files used in the video:

Note that in the you'll want to change the application and project names to match the ones you are working on.

As you see in the video the build.xml is generated for you automatically and you just need to add the line:

<property environment="env" />



<?xml version="1.0" encoding="UTF-8" ?>
<!--Ant buildfile generated by Oracle JDeveloper-->
<!--Generated Jul 25, 2016 5:11:09 PM-->
<project xmlns="" name="ProcessOrder" default="all" basedir=".">
<property environment="env" />
<property file=""/>
<path id="library.SOA.Designtime">
<pathelement location="${install.dir}/soa/plugins/jdeveloper/extensions/"/>
<path id="library.SOA.Runtime">
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.fabric_11.1.1/fabric-runtime.jar"/>
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.fabric_11.1.1/tracking-api.jar"/>
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.fabric_11.1.1/tracking-core.jar"/>
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.fabric_11.1.1/edn.jar"/>
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.mgmt_11.1.1/soa-infra-mgmt.jar"/>
<pathelement location="${oracle.commons}/modules/"/>
<path id="library.BPEL.Runtime">
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.bpel_11.1.1/orabpel.jar"/>
<path id="library.Mediator.Runtime">
<pathelement location="${install.dir}/soa/soa/modules/oracle.soa.mediator_11.1.1/mediator_client.jar"/>
<path id="library.MDS.Runtime">
<pathelement location="${oracle.commons}/modules/oracle.mds/mdsrt.jar"/>
<path id="library.BC4J.Service.Runtime">
<pathelement location="${oracle.commons}/modules/oracle.adf.model/adfbcsvc.jar"/>
<pathelement location="${oracle.commons}/modules/oracle.adf.model/adfbcsvc-share.jar"/>
<pathelement location="${oracle.commons}/modules/commonj.sdo.backward.jar"/>
<pathelement location="${oracle.commons}/modules/commonj.sdo.jar"/>
<pathelement location="${oracle.commons}/modules/oracle.toplink/eclipselink.jar"/>
<pathelement location="${oracle.commons}/modules/"/>
<pathelement location="${oracle.commons}/modules/"/>
<pathelement location="${oracle.commons}/modules/"/>
<path id="classpath">
<path refid="library.SOA.Designtime"/>
<path refid="library.SOA.Runtime"/>
<path refid="library.BPEL.Runtime"/>
<path refid="library.Mediator.Runtime"/>
<path refid="library.MDS.Runtime"/>
<path refid="library.BC4J.Service.Runtime"/>
<target name="init">
<mkdir dir="${output.dir}"/>
<target name="all" description="Build the project" depends="deploy,compile,copy"/>
<target name="clean" description="Clean the project">
<delete includeemptydirs="true" quiet="true">
<fileset dir="${output.dir}" includes="**/*"/>
<target name="deploy" description="Deploy JDeveloper profiles" depends="init">
<taskdef name="ojdeploy" classname="oracle.jdeveloper.deploy.ant.OJDeployAntTask" uri="oraclelib:OJDeployAntTask"
<ora:ojdeploy xmlns:ora="oraclelib:OJDeployAntTask" executable="${oracle.jdeveloper.ojdeploy.path}"
<ora:parameter name="workspace" value="${oracle.jdeveloper.workspace.path}"/>
<ora:parameter name="project" value="${}"/>
<ora:parameter name="profile" value="${}"/>
<ora:parameter name="nocompile" value="false"/>
<ora:parameter name="outputfile" value="${oracle.jdeveloper.deploy.outputfile}"/>
<target name="compile" description="Compile Java source files" depends="init">
<javac destdir="${output.dir}" classpathref="classpath" debug="${javac.debug}" nowarn="${javac.nowarn}"
deprecation="${javac.deprecation}" encoding="UTF8" source="1.8" target="1.8">
<src path="SOA/SCA-INF/src"/>
<target name="copy" description="Copy files to output directory" depends="init">
<patternset id="copy.patterns">
<include name="**/*.GIF"/>
<include name="**/*.JPEG"/>
<include name="**/*.JPG"/>
<include name="**/*.PNG"/>
<include name="**/*.cpx"/>
<include name="**/*.dcx"/>
<include name="**/*.ejx"/>
<include name="**/*.gif"/>
<include name="**/*.ini"/>
<include name="**/*.jpeg"/>
<include name="**/*.jpg"/>
<include name="**/*.png"/>
<include name="**/*.properties"/>
<include name="**/*.sva"/>
<include name="**/*.tag"/>
<include name="**/*.tld"/>
<include name="**/*.wsdl"/>
<include name="**/*.xcfg"/>
<include name="**/*.xlf"/>
<include name="**/*.xml"/>
<include name="**/*.xsd"/>
<include name="**/*.xsl"/>
<include name="**/*.exm"/>
<include name="**/*.xml"/>
<exclude name="build.xml"/>
<copy todir="${output.dir}">
<fileset dir="SOA/SCA-INF/src">
<patternset refid="copy.patterns"/>
<fileset dir=".">
<patternset refid="copy.patterns"/>

Categories: Development

How to Find the Row That Has the Maximum Value for a Column in Oracle

Complete IT Professional - Mon, 2016-07-25 06:00
Do you need to find the data for a row which has the maximum value for a specific column in Oracle? Learn how to do it and see some alternative answers in this article. The Table Structure Let’s say you have a table called sales_volume that looks like this: CITY START_DATE SALES El Paso 27/May/15 […]
Categories: Development

Oracle INITCAP Function with Examples

Complete IT Professional - Thu, 2016-07-21 06:00
The Oracle INITCAP function is a handy string manipulation function. Learn what it does and see some examples in this article. Purpose of the Oracle INITCAP Function The Oracle INITCAP function translates a specified string into another string which has the first letter of each word capitalised, and all other letters in lower case. The […]
Categories: Development

Oracle LOWER Function with Examples

Complete IT Professional - Wed, 2016-07-20 16:41
In this article, I’ll take a look at the Oracle LOWER function, explain how it works, and see some examples. Purpose of the Oracle LOWER Function The LOWER function converts all letters within the specified string to lower case.   Syntax The syntax for the Oracle LOWER function is: LOWER ( input_string )   Parameters […]
Categories: Development

Extending Oracle SaaS with Oracle Application Builder Cloud Service - Simplified PaaS for SaaS

Shay Shmeltzer - Tue, 2016-07-19 16:18

One of the focus area for us when developing the new Oracle Application Builder Cloud Service (ABCS) was to create a tool that would make it very simple to enrich Oracle SaaS applications.

This integration is a key part of Oracle's PaaS for SaaS offering - where we have unique capabilities in our Platform as a Service offering for our Software as a Service customers.

With ABCS it is very easy to pick up objects from Oracle SaaS (through the built in service catalog) and then design new web and mobile interfaces that show data from those.

In addition you can then add your own custom fields and related objects with additional data you want to track.

Once your application is complete - you can then either run it as a stand-alone app, or embed it into an Oracle SaaS interface. 

I wrote two blogs and recorded two videos that show you the basics.

Creating Oracle ABCS application connected to Oracle SaaS

Embedding Oracle Application Builder Cloud Service Apps in Oracle Sales Cloud

<span id="XinhaEditingPostion"></span>

These videos are part of the new Oracle Application Builder Cloud Service YouTube Channel

And the blogs are published on the Oracle Application Builder Cloud Service OTN Community

We have more tutorials there to help you go even further - so check them out! 

Categories: Development

How Can You Create A Column With AUTO_INCREMENT in Oracle SQL?

Complete IT Professional - Mon, 2016-07-18 06:00
If you’ve used MySQL, you’re probably familiar with the AUTO_INCREMENT feature. But it’s not available in Oracle. Read this article to find out how you can auto increment a column in Oracle SQL. What Is Auto Increment? An auto increment column, or an identity column in other databases, is a column that has its value […]
Categories: Development

Oracle RAWTOHEX Function with Examples

Complete IT Professional - Thu, 2016-07-14 06:00
The Oracle RAWTOHEX function is a simple conversion function. Learn how to use it and see some examples in this article. Purpose of the Oracle RAWTOHEX Function The purpose of the RAWTOHEX function is to convert from a RAW value into a hexadecimal value. As mentioned in the HEXTORAW function article, a RAW value is […]
Categories: Development

Continuous Integration for Database Developers - Updated

Shay Shmeltzer - Wed, 2016-07-13 17:25

About a year ago I posted a demo showing how to manage the full development lifecycle of your database code with the help of Developer Cloud Service. Since then we released new versions of both Developer Cloud Service and JDeveloper that make the experience even smoother and add more features - so I figured I'll record a small updated demo.

In this demo I'm starting from an existing project that has a list of tasks being tracked in a development sprint in the new Agile tab in Developer Cloud Service - which gives you a great view of your development effort and progress. 

(If you want to see how you create the initial project and add issues to it check out the previous demo). 

A few new things you'll see in this demo:

  • The new Agile/Sprint management dashboard in Developer Cloud Service
  • Task tracking integration in JDeveloper
  • Updating definition of database objects in JDeveloper and generating SQL scripts
  • Branching Git repositories
  • Code review for SQL files
  • Build automation for DB changes with Ant - including deployment to a cloud database

Note that in this video I'm using a cloud instance of an Oracle database where port 1521 is open for SQLNet communication. The instructions for opening this port for communication on an Oracle Cloud Database are here.

Another approach that you can take is to use scripts that execute commands using SSH on the Database Cloud Service - you can see this approach in action in this video showing Developer Cloud Service managing an Oracle APEX Lifecycle.  

The Ant script used in the sample is:

 <?xml version="1.0" encoding="UTF-8" ?>
<project xmlns="" default="init">
  <target name="init">
  <path id="antclasspath">
    <fileset dir=".">
      <include name="ojdbc7.jar"/>
  <target name="deploy">
    <sql driver="oracle.jdbc.OracleDriver" userid="C##xxxx" password="xxxx"
         url="" src="./database/DATABASE1/dbcreate.sql"
         classpathref="antclasspath" onerror="continue"/>

Note that you need the Oracle JDBC jar file to be accessible from the Ant script - I just included it in my git repository.

If you like to try this out with your own database development project - get a trial of Oracle Developer Cloud Service here

Categories: Development

How to Avoid The “ORA-00979 not a group by expression” Error

Complete IT Professional - Mon, 2016-07-11 06:00
Have you tried to run a query and got the “ORA-00979: not a group by expression” error? Learn what it is and how to resolve the error in this article. Generating the ORA-00979 Error This error can be generated when your run SELECT queries on your database. Here’s an example of a query that will […]
Categories: Development

Politicians won't move on climate change cause they know we don't REALLY care.

FeuerThoughts - Sun, 2016-07-10 10:23

Yep. That's the truth (at the least the truth that seems to be taking shape between my ears these days).

I've been thinking about the Great Pacific Garbage Patch of late....

Lots of us seem to know it exists, and we are disgusted by it. Disgusted by us - humans who are disastrously trashing our planet.

And what are we going to do about it?

We are going to demand that Congress DO SOMETHING!

And our demands are going to be expressed in extremely powerful ways:
  • Online petitions
  • Facebook rants
  • Lots and lots of outraged tweets
Oh yes. Those. Lots of them, lots of indignation, shared outrage, thank you Facebook Echo Chamber.

And yet, and yet...somehow those awful Congresspeople ignore the Will of the People. How can this be? 

Time for more outraged and indignant rants and sarcastic memes on Facebook.

How ridiculous on two fronts:

1. Online "activism" is largely ineffective. 

2. Politicians will only listen to us when we take action that demonstrates our seriousness.

And this is where we really fall short.

So you read about all the awful plastic clogging up our oceans, killing fish and whales and dolphins and....everything, really, just about everything.

And what do you actually do?

Do you change even one iota of the way you live your life? It doesn't seem that way to me. We bitch and moan for a while, and then watch Game of Thrones or go to Six Flags or buy another case of plastic bottled water.

And since we don't seem to be willing to make the smallest sacrifices in our lives, politicians know they can just keep on serving their real masters: lobbyists of corporations.

Let's face it: if you consume and discard plastic, it's going somewhere, and it's going to be nasty, no matter the location. 

But if you don't consume that plastic, you will have not contributed to the problem. You will have not made things worse. And if millions of people did this same thing - took action in their life to change patterns of consumption - the impact would be enormous.

Here are some of the things I do to avoid plastic consumption:

1. I never, never, NEVER (well, hardly ever) buy plastic bottled water. And I especially never buy cases of plastic bottled water that is wrapped in plastic. How grotesque. Instead, buy a glass or stainless steel bottle and refill the damn thing, people.

2. I hardly ever buy processed food. I mostly buy food, like broccoli and fruit and eggs. Sure, they all require some processing. But nothing like buying a Lunchable. So gross.

3. I travel with a set of bamboo "silverware" so I can avoid using plastic-wrapped plastic forks and knives. I so detest those.

4. When I get ice cream, I get a cone: no need for a plastic dish, no plastic spoon. Of course, if I go to a lovely ice cream shop like Oberweis and eat my delight there, they use glass bowls and glasses and real silverware. So then I will treat myself to a milkshake or sundae. Yummy and no plastic.

5. I make my own yogurt instead of buying lots of plastic containers of the stuff. It's easy to do: just buy one of these

6. I buy milk in reusable glass containers. Again, thanks Oberweis!

And there's more, but you get the idea. It mostly comes down to being more intentional about how you go through the day: think ahead, always carry your water bottle and bamboo silverware, just say no to treats that come in plastic that you do not really need to eat, etc.

If millions of humans took action like this, the amount of garbage going to landfills and into the ocean would decrease substantially. 

With reduced demand, less plastic would be produced in factories, less pollution would be produced, etc.

But if you do not do things like this, if you direct your outrage to distant politicians who will never pay you attention and do not address some of that outrage at yourself, well...

Then the coral and whales and sharks and fish and birds and eventually even (dare I say it!) humans will suffer. 

Bottom line: if you want politicians to change their behavior, first change yours

That way, when they still don't give a rat's ass about you, at least you will have helped make the planet a little bit healthier.

Multiple by a million or a billion, and maybe the coral will notice.
Categories: Development

Oracle HEXTORAW Function with Examples

Complete IT Professional - Thu, 2016-07-07 06:00
The Oracle HEXTORAW function is a basic conversion function used in Oracle SQL. Learn how to use it and see some examples in this article. Purpose of the Oracle HEXTORAW Function The purpose of the HEXTORAW function is to convert a hexadecimal value into a raw value.   Syntax The syntax for the Oracle HEXTORAW […]
Categories: Development

Getting Started with Oracle JET

Shay Shmeltzer - Wed, 2016-07-06 14:31

Last week I did an "Introduction to Oracle JET" session at the KScope16 conference, and I wanted to share the demo I used there with more people.

Specifically the demo shows how you can adopt the code from the Oracle JET cookbook samples to work in the quick start template project.

In this demo you'll learn how to create your first JET application and build a basic JET page.

Specifically it shows the following steps:


Hopefully this video can help you build your first Oracle JET page.

Now that you watched this video that shows how to use the pre-configured project provided as a quick start, you might want to follow up and watch the video that shows you how to work with the base distribution and hook up the JET libraries. 

Need more help with Oracle JET? Join the JET community on OTN

Categories: Development


Subscribe to Oracle FAQ aggregator - Development