Opening of APEX R&D New Zealand

We're really excited to announce that next to our Belgium office, we have opened an office in New Zealand too. This will allow us to cover multiple timezones and better serve the Australian and New Zealand market. You can read more about it on Lino's blog. 

 

Contact our team of experienced professionals for innovative, reliable and cost effective Oracle Application Express solutions. 

Regarding any custom application development, training/coaching or consulting related to Oracle APEX technology we would love to hear from you.

For more information please contact our Belgium or New Zealand office.

We look forward working with you.

Submit your abstract for KScope 16 - only 3 days left

Only 3 days left to submit your abstract for KScope 16... so don't wait longer, submit now! :)

My first ODTUG conference was in 2006 and it was awesome. On this blog you'll find different blog posts about my experience and why I love it, so I won't go over that... but from time to time I get some questions about being a presenter at the event.

"What should I submit, I don't know what to talk about?"

The topics in the Oracle Application Express track are following:

• Integration: Relates to any work where other products are integrated with APEX or a gap is bridged between platforms. Perhaps is integration with EBS, SAP, Raspberry Pi, Node.js, OAuth, REST, etc...
• New Release: Presentations that relate to the latest version of APEX. Typically the Oracle development team is presenting those topics.
• Real World: Everybody loves real life examples. This is anything where a real life system is presented. The problem is explained and how APEX was used to solve it.
• Detail Plunge: These are presentations on any topic, but the topic, even if it seems narrow, is explored in detail and with focus. It doesn't have to be complicated. I could be building plugins or Dynamic Actions.
• Other Application Express: Everything else goes here. This is the catch-all bucket.

You can submit up to 4 abstracts.

The way I chose my topics typically fall in one of following categories:

• I learned different things and combine them in a presentation. E.g. my presentation on the way we develop 
• I worked on something in the last months and think it might be useful for others. For example my printing in node.js presentation
• I read about something cool I want to investigate, so by submitting the topic I force myself to have it completely researched before a certain date. An example of that was my presentation on Web Components.

"Why even bother, I won't be selected"

Well, without submitting you don't know. What do you have to lose? I'm sure I don't speak for myself when I say, I would love to meet new people and hear about what they do and discover. There's not a single day I don't learn anything, tech or non-tech. This weekend I learned something new and the feature has been in APEX for many years! (but that's for another blob post)
The level of presentation is very broad as well. We need presentations of all levels; beginners, intermediate and advanced.


"I don't know how to write an abstract"

On the internet there're many links how to present and write an abstract. On the ODTUG site you'll find some tips and examples.


Give it a go, you'll feel good when you've done it. Here's the link. It's all about the learning experience!

All the best and I look forward meeting you in Chicago.

Column Group Headings in an Interactive Report (APEX 5.0)

In 2008 I blogged about how to add Group Headings into an Interactive Report in Oracle Application Express (APEX 3.x!).

Here's an example what I mean with Group Headings, you want to logically group certain columns (e.g. Empno, Ename) and have a group name above them (e.g. Group 1):

The approach I used at that time worked, but it required a lot of manual work. In 2009 Martin D'Souza improved it and wrote a blog post about that. In 2010, when plugins were introduced in APEX 4, Martin wrapped it all in a nice APEX plugin which made adding Column Group Headings almost declarative. The plugin was further improved and put on GitHub.

Today, having APEX 5, and the Interactive Reports being changed a lot behind the scenes, the plugin was broken. As we're in the middle of restructuring how to share code, plugins etc., I forked Martin's git repo and made the plugin compatible with APEX 5 and Universal Theme (see version 1.0.4), so when you need it, you can grap it from the forked repo. At a later time the plugin will be moved to OraOpenSource, so depending when you read this post, check out OraOpenSource as Martin, I and many other developers will start putting our open source code on OraOpenSource.

Here's a quick how to use the plugin.

Step 1: Download the file dynamic_action_plugin_com_clarifit_apexplugin_ir_column_grouping.sql
Step 2: Import the plugin in your APEX application
Step 3: Define the Groups in the Interactive Report (you can right click on Column Groups in the  Attributes of your Interactive Report and add the Groups you need):

Step 4: Assign the group to the columns of your report (you can multi select the columns and in the right pane you can select the group):

Important: define for every column that has a group defined, a static id equal to the name of the column (e.g. ENAME).

Step 5: Add the dynamic action plugin to the page
When:
- Event: After Refresh
- Selection Type: Region
- Region:
Action:
- IR Column Grouping [Plug-In]

That's it... note that when you scroll down and you have sticky headers, the group will stay with it.

Custom image on your APEX app login page

In a comment on my post APEX 5.0: pimping the Login page I got a question how to put your own logo or a custom image on the login page, instead of an icon.

You only need a bit of CSS to do the trick:

You can add the above CSS to your login page - Inline CSS in page attributes or you can add it to your page template or custom CSS in Universal Theme.

The result is this:

To differentiate the login page you can do a lot more, here're some things we show during our APEX UI training:

• add transparency to the login box
• add a background image to your entire page (blurred or not)

Again, you can do that with just some CSS.

Update (feedback from Daniel Javer); if you are on mobile you can use in CSS of your page:

body {
  webkit-background-size: cover;
    -moz-background-size: cover;
    -o-background-size: cover;
    background-size: cover;
    background-image:url('#APP_IMAGES#upbase/pic.jpg');
  }
  .ui-content.ui-body-a{
    background: rgba(0, 0, 0, 0.1);
  }
  .ui-responsive-panel.ui-page.ui-page-theme-a.ui-page-active{
    background: rgba(0, 0, 0, 0.1);
  }

ORDS... Yes we can!

On September 13th I got a nice surprise, an email of Steven Feuerstein with the message: "You have been selected as a finalist in the ORDS category for the 2015 Oracle Database Developer Choice Awards!"

It's always nice to get recognition for the efforts you do, so thanks so much already for the nomination. I didn't make publicity yet in order to get some up-votes, but I hope by reaching out to the people who read my blog, I gain some more up-votes :)

 

To be honest, I wondered why I deserved this nomination, especially in a category that I'm probably less "known" for.
After giving this more thought, I remembered ORDS actually started being very much linked to APEX. In the early days ORDS was even being called the "APEX Listener", and I was one of the early adopters and promoters of using the APEX Listener (now ORDS) in your APEX architecture.

In 2012 I literally travelled around the world (Belgium, the Netherlands, UK, San Francisco, San Antonio, Uruguay, Brazil, Chile) to talk about why you should move to the APEX Listener.

Moving to the APEX Listener from Dimitri Gielis

When looking back at that presentation, today it still stand; you still have different options for your APEX architecture, but we don't have to convince anymore about the benefits of choosing for ORDS. ORDS is now main-stream, widely adopted and proven technology. Unless it's a legacy system, I don't really see any reason anymore why you should not use ORDS in your architecture.

ORDS evolved a lot over time, and the new name reflects more what the core feature is "Oracle REST Data Services". REST web services became so important in the last years and that is exactly what I've been blogging and talking about lately (see further).

In my presentation of Microsoft Sharepoint and Oracle APEX integration (given in San Francisco, BeNeLux and Germany) I talk about the architecture and how you get your APEX data in Microsoft Sharepoint by using ORDS. But also the other way round, by using Sharepoint APIs, REST web services come into play. When you want to integrate with other systems, ORDS can really help.

Oracle Application Express (APEX) and Microsoft Sharepoint integration from Dimitri Gielis

I didn't blog much yet about a really interesting R&D project we've been working on in the last months - using wearables to capture sensor and patient data. At the Oracle Mobile day in the Netherlands I did a presentation which explains it in more detail. We developed native smarth phone applications that call REST web services all build in ORDS. ORDS is getting or pushing the data into our Oracle Database using JSON. Next to that we've dashboards in APEX to "see" and work with the data. We learned and still learn a lot of this project; about the volume of data, the security etc. but without ORDS it would not have been possible.

APEX Wearables from Dimitri Gielis

And finally a product where I'm really proud of APEX Office Print (AOP). I found it always a challenge to get documents out of APEX. I'll do some more blog posts about it in the future, but where APEX is so declarative to build your web applications, it's far from declarative to get documents out in Word, Excel, Powerpoint or PDF (at least without BI Publisher). With APEX Office Print we hope to address that. Just one APEX plugin where you define the template and which data you want to use and presto you get your document. Behind the scenes we use JSON and having ORDS makes it really easy to generate it. If you want to know more about the components behind the printing solution (and do it yourself), you'll find that in my presentation about Printing through Node.js which I presented in different countries and will present at Oracle Open World.

How to make APEX print through Node.js from Dimitri Gielis

Furthermore, if you're interested in reading more about JSON, I've done a series of blog posts and was interviewed by Oracle about it, you find the links here.

Hopefully by doing this post you see the power of ORDS and you get some ideas yourself how to best leverage the power of this wonderful piece of software. If you liked my "ORDS-efforts" and want to give me an up-vote in the Database Developer Choice Awards, I really appreciate that.

Thanks so much,
Dimitri

Report Carousel in APEX 5 UT

The Universal Theme in APEX 5.0 is full of nice things.

Did you already see the Carousel template for Regions? 

When you add a region to your page with a couple of sub-regions and you give the parent region the "Carousel Container" template it turns the regions into a carousel, so you can flip between regions.

I was asked to have the same functionality but than on dynamic content.

So I decided to build a report template that would be shown as carousel. Here's the result:

You can see it in action at https://www.apexrnd.be/ords/f?p=ADVANCED_UT:REPORT_CAROUSEL:0:::::

I really like carousels :)

Here's how you can have this report template in your app:

1) Create a new Report Template:

Make sure to select Named Column for the Template Type:

Add following HTML into the template at the given points:

That's it for the template.

Now you can create a new report on your page and give it the template you just created.
Here's the SQL Statement I used:

select PRODUCT_ID          as id,
       PRODUCT_NAME        as title,
       PRODUCT_DESCRIPTION as description,
       product_id,       
       dbms_lob.getlength(PRODUCT_IMAGE) as image,
       'no-icon'           as icon,
       null                as link_url 
  from DEMO_PRODUCT_INFO

Note 1: that you have to use the same column aliases as you defined in the template.
Note 2: make sure you keep the real id of your image in the query too, as otherwise you'll get an error (no data found)

To make the carousel a bit nicer I added following CSS to the page, but you could add it to your own CSS file or in the custom css section of Theme Roller.

Note: the carousel can work with an icon or an image. If you want to see an icon you can use for example "fa-edit fa-4x". When using an image, define the icon as no-icon.

Eager for more Universal Theme tips and tricks? check-out our APEX 5.0 UI training in Birmingham on December 10th. :)

For easier copy/paste into your template, you find the source below:

 *** Before Rows ***  
 <div class="t-Region t-Region--carousel t-Region--showCarouselControls t-Region--hiddenOverflow" id="R1" role="group" aria-labelledby="R1_heading">  
  <div class="t-Region-bodyWrap">  
   <div class="t-Region-body">  
   <div class="t-Region-carouselRegions">  
 *** Column Template ***  
 <div data-label="#TITLE#" id="SR_R#ID#">  
  <a href="#LINK_URL#">  
   <div class="t-HeroRegion " id="R#ID#">  
        <div class="t-HeroRegion-wrap">  
         <div class="t-HeroRegion-col t-HeroRegion-col--left">  
          <span class="t-HeroRegion-icon t-Icon #ICON#"></span>  
          #IMAGE#  
         </div>  
         <div class="t-HeroRegion-col t-HeroRegion-col--content">  
          <h2 class="t-HeroRegion-title">#TITLE#</h2>  
          #DESCRIPTION#  
         </div>  
         <div class="t-HeroRegion-col t-HeroRegion-col--right"><div class="t-HeroRegion-form"></div><div class="t-HeroRegion-buttons"></div></div>  
        </div>  
   </div>    
  </a>    
 </div>    
 *** After Rows ***  
   </div>  
   </div>  
  </div>  
 </div>  
 *** Inline CSS ***  
 .t-HeroRegion-col.t-HeroRegion-col--left {  
  padding-left:60px;  
 }  
 .t-HeroRegion {  
  padding:25px;  
  border-bottom:0px solid #CCC;  
 }  
 .t-Region--carousel {  
  border: 1px solid #d6dfe6 !important;    
 }  
 .t-HeroRegion-col--left img {  
  max-height: 90px;  
  max-width: 130px;  
 }  
 .no-icon {  
  display:none;  
 }  

Expert Oracle Application Express (second edition) available

During my holiday Apress released the second edition of Expert Oracle Application Express.

In the second edition we not only updated the content to APEX 5.0, but there're some new chapters in too from new authors. In total you get 14 chapters from 14 different authors.

I believe it's a very nice book with great content and it's for a good cause. Roel describes it very nicely in his blog post "the goal is to raise as much money as we can for the funds that support the relatives of two of the greatest Oracle APEX Development Team members who passed away a few years ago: Carl Backstrom and Scott Spadafore."

You can get it from the Apress website or from Amazon. Happy reading :)

First patch set (5.0.1) released for APEX 5.0

I know some people waiting till the first patch set that comes available after a major Oracle APEX release... today you no longer have to wait, APEX 5.0.1 is now available.

In the patch set notes you can read what changed.

If you're still on APEX 4.x you can go immediately to APEX 5.0.1, you only need to download the latest version on OTN.

If you're already on APEX 5.0, you can download the patch set from support.oracle.com, search for patch number 21364820. Applying the patch took less than 5 minutes in my environment.

This patch set updates the Universal Theme too, so don't forget to update your images folder. When you login in APEX after the patch, it will check if you have the correct images folder, if not it will give you an alert. Although I updated the images directory I still got that alert due to browser caching. Refresh your page and it should be ok.

Note that it's important to be on APEX 5.0.1 when you use APEX Office Print - currently available in beta for a select audience, public release end of this month (July 2015). Behind the scenes we use the APEX_JSON and APEX_WEB_SERVICE packages which got an update in APEX 5.0.1.

And finally, there's a nice new D3 chart available in APEX 5.0.1 called "D3 Collapsible Treemap Chart"

Happy upgrading...

Update 18-JUL-2015: there're still a couple of known issues, please read those too and install some patchset exception when necessary.

Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham

APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.

Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way. 

The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference.

More information and registration see www.apextraining.eu. 

If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!

Fixing Super LOV in Universal Theme

When you migrate to APEX 5.0 and the Universal Theme you might see that some plugins are not behaving correctly anymore. In this post I'll discuss the Enkitec Modal LOV plugin.

When I ran the plugin in my APEX 5.0 app with the Universal Theme it looked like this:

There's too much space in the search bar and the close button is not showing up with an icon.

Here're the steps I did to fix it. First you need to download the css file of the style you use and the js file from the plugin in Shared Components. I use the smoothness.css style most of the times, so I'll use that as an example.

To fix the close icon, add !important to the png:

.ek-ml .ui-state-default .ui-icon {
background-image: url(ui-icons_888888_256x240.png) !important;
}

Note: you can do that for all those png on line 467 till 489.

To fix the height, add following css to smoothness.css:

.superlov-button-container {
  height:50px;
}

And finally in enkitec_modal_lov.min.js change the height of the searchContainer from a dynamic height (r) to 0px:

$searchContainer.css("height","0px")

Next upload those files again to the plugin.

When you run the plugin it should give you this result:

Now the bigger question is; do we still need that plugin? In APEX 5.0 there're native Modal Pages, so you could create an Interactive Report and set the page as a Modal Page. Next you can hook that up to a button or link and you've just build your own Modal LOV.

I still like to use the plugin at the moment (as it's just one item on the page), but it could use a refresh to make it look nicer and more inline with Universal Theme.

Wonder what you think - would you build your own Modal LOV in APEX 5.0 or would you still prefer to use a plugin? 

2 minute Tech Tip: Working with JSON in APEX

On Monday Bob Rhubart did a video call with me in his series of 2MTT (2 Minute Tech Tip) on YouTube. You find my 2MMT here.

I talked about using JSON and APEX and gave two examples were we use it.

In previous blog posts I gave more details on those techniques. Here's a quick overview:

• Generate nested JSON from SQL with ORDS and APEX 5
• JSON for APEX Developers (part 1): REST and JSON
• JSON for APEX Developers (part 2): Consuming JSON in JavaScript
• JSON for APEX Developers (part 3): Querying JSON in Oracle DB 12c

Change first day of week in APEX 5.0 Calendar

APEX 5.0 comes with a new calendar region, which is way nicer than the previous calendar in APEX. It has more features, looks better and is also responsive. Behind the scenes you'll see the calendar region is based on Full Calendar.

In Belgium we use Monday as first day of the week, whereas in the US they seem to use Sunday as start of the week in the calendar overview. I've been integrating Full Calendar before, so I knew that library had an option to set the first day of the week. You could either specify an option called firstDay and set that to 1, or you could change the language, and depending the language it would adjust the start day of the week.

In APEX 5.0 I looked for that option, but there's not a specific attribute to set the first day of the week, instead it's based on the language of your application. If you go to Shared Components > Globalization Attributes by default it's set to en, which has Sunday as start of the week. If you set it to en-gb it will have Monday as start of the week.

I searched some time to find how to do it, so hopefully this post will help others to find it more easily. Thanks to Patrick for sharing the way it was implemented.

Enable Real Application Security (RAS) in APEX 5.0

Oracle Database 12c introduced Oracle Real Application Security (RAS), the next generation Oracle Virtual Private Database (VPD). In APEX 5.0 RAS is declaratively build-in. Follow the below steps to enable it:

Login to the INTERNAL workspace and go to Manage Instance > Security:

In the Real Application Security section set Allow Real Application Security to Yes.

Next login to the Workspace your Application is build in and go to your Authentication Scheme.
You'll see a new section in there called Real Application Security.

The dropdown has following possibilities:

• Disabled: Real Application Security does not get used in the application. 
• Internal Users: APEX creates a RAS session and assumes that all users are internal and passes false via the is_external parameter to dbms_xs_sessions.assign_user. 
• External Users: RAS session created and true gets passed via the is_external parameter to dbms_xs_sessions.assign_user. 

The last two options enable RAS Mode and make the Dynamic Roles and Namespaces shuttle available. (from the help in APEX) Make sure that the users get privileges to access the application's schema objects. For External Users, you can for example grant database privileges to a RAS Dynamic Application Role and configure it in this authentication scheme as a Dynamic Role. You can also enable roles via a call to apex_authorization.enable_dynamic_groups, e.g. in a Post-Authentication procedure.

You can read more about Oracle Real Application Security and view an entire example how to set up RAS at the database side. I'm still learning about all the RAS features myself too, but thought to already share the above. I plan to include a chapter in my e-book about RAS and APEX 5.0 with a real-case example, as I see a big benefit for using it in a highly regulatory, secure and audited environment.

JSON for APEX Developers (part 3): Querying JSON in Oracle DB 12c

In previous blog posts I talked about JSON for APEX Developers:

• Generate nested JSON from SQL with ORDS and APEX 5
• JSON for APEX Developers (part 1)
• JSON for APEX Developers (part 2)

In this post I want to show how you store and query JSON data straight in the database.

To start, create a table to store the JSON object:

CREATE TABLE ssn_json
   (id          NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
    date_loaded DATE,
    json_document CLOB
    CONSTRAINT ensure_json CHECK (json_document IS JSON));

I inserted a couple of records in the table:

Here's in more detail the JSON in the json_document column:

Now to query the JSON data I can do :

You basically say j (table) . json_document (column) . analyses (first entry in JSON) . ... (other fields of the hierarchy you want to navigate to)
 
You see the data I get back is actually two records as I actually get back the JSON array.

To go into the array and see for example the first record, I can use json_value:

Note that the array start with 0.

And finally to see both records that are in the JSON array I can make use of json_table like this:

The above is just a grasp of what you can do with JSON directly in the database (12c) by using SQL.
If you're interested to read more about how to manipulate JSON in the database, have a look at the documentation, it contains a wealth on information with great examples.

As you can basically query the JSON with SQL you can use this SQL in your APEX reports... in the coming days I'll show you a real case where I used the above techniques to do some interesting searching in data.

Oracle APEX 5.0 released today

After 2.5 years of development, today is the day APEX 5.0 is publicly released and ready to be downloaded to install on your own environment.

In my view it's the best release ever. Not so much of the new Page Designer - although that is definitely a piece of art and it increased productivity even further - but because it's the first time whole of APEX got refreshed and every piece was put under a radar to see how it could be improved. All the small changes and the new UI, together with the Page Designer makes it a whole new development tool, without losing it's strengths from before.

Also note that APEX 5.0 enables many new features build on top of the Oracle Database 12c features, so if you're on that database, you'll see even more nice features.

If you wonder if you should wait with upgrading to APEX 5.0 because you're afraid that your current APEX applications break, I can only share that I upgraded many of my applications as part of EA/beta and most of my apps kept running without issues. As always you have to try your applications yourself, but the APEX development team spend a lot of time trying to keep things backwards compatible. But make sure to have a look at the APEX 5.0 release notes and known issues as they contain important information about changes, expected behaviour and workarounds.

You can develop online on apex.oracle.com or you can download APEX 5.0 and install into your own environment.

Working with multiple Trees on the same APEX Page (4.2/5.0)

Ever tried to put multiple Trees on the same page in APEX 4.2?

Here's my example:

On the left Tree the "Selected Node Link with" is set to hidden item P1_TREE1, on the right Tree the value that sets the tree is set to P1_TREE2. At least it should do that, but by default if you're values are not unique - in this case empno is the value behind both trees - it doesn't work...
Your first tree will be highlighted twice; one time for ALLEN and one time for SCOTT and not as it should be, on the left ALLEN selected and on the right SCOTT selected.

To fix this issue in APEX 4.2, you need to adapt your select statement so you have unique values for both trees. I typically put a meaningful character in front of the value; for example you can concatenate 'N' for new values (new tree) and 'O' for old values (old tree).

So that fixes the issue of the "Selected Node Page Item" issue with non unique values.
Behind the tree implementation in APEX 4.2, jsTree is used. APEX is not using the most recent version and the way the JavaScript is coded doesn't really work that nicely. If you really like jsTree and for example want to search in it, or you have a very large dataset, check out Tom Petrus' tree plugin and explanation.

So what about APEX 5.0? The tree implementation in APEX 5.0 has the same functionalities, but is a lot better. Behind the scenes jsTree is not used anymore, instead the tree that is also used in the Page Designer is used. The issue with unique values is off the table, so no worries anymore in APEX 5.0.

APEX 5.0: pimping the Login page

When you create a new application in APEX 5.0, the login page probably looks like this:

I love the build-in login page of APEX itself - luckily it's easy enough to build that in our own apps too. Thank you APEX Dev team!

The first step is to change the region type to be of Login Region Template:

We want to add a nice icon on top of the Login text. You can use the Icon CSS Class in the Region options - in this case I opted for fa-medkit:

Next up is making the Login button bigger and make it the complete width like the items.

In APEX 5.0 you can use the Template Options to do that:

Once we stretched the Login button it fits the entire size.

Next up is getting some icons in the username and password field.

For the username we use the "icon-login-username" css class.

Instead of the label we make that hidden and specify a placeholder, so before you start typing you see the word username and when you start typing the text disappears.

For the password field we do the same thing, but for the css class we specify "icon-login-password".

Finally your login screen looks like this:

Great? Absolutely - and so easy with APEX 5.0!

What's next? Is there anything better? Euh... yes, what about live validation?
Sure we can do that in APEX 5.0 without too much hassle :)) Thanks once again APEX Dev Team!

In the item make sure the item is set to Value Required and add in the Post Text following span:

That will give you a nice visual indication if you entered text:

Cool? Creating login pages in APEX 5.0 is ... (you fill in the word)

Interested in more? We're doing an APEX 5.0 UI Training in May.

Loading CSV files with special characters in Oracle DB

I often need to load the data of Excel or CSV files into the Oracle Database.

Ever got those annoying question marks when you try to load the data? or instead of question marks you just get empty blanks when the file is using special characters? Here's an example:

My database characterset is UTF-8, so ideally you want to load your data UTF-8 encoded.

With Excel I've not found an easy way to specify the encoding to UTF-8 when saving to a CSV file.

Although in Excel (OSX) - Preferences - General - Web Options - Encoding, I specified UTF-8, it still saves the file as Western (Mac OS Roman).

I've two workarounds I use to get around the issue. Open the file in a text editor e.g. BBEdit and click the encoding option and select UTF-8.

Another way is to open Terminal and use the iconv command line tool to convert the file

iconv -t UTF8 -f MACROMAN < file.csv > file-utf8.csv

If you get a CSV file and you want to import it in Excel first, the best way I found is to create a new Workbook and import the CSV file (instead of opening directly). You can import either by using File - Import or Data - Get External Data - Import Text File. During the import you can specify the File origin and you can see which data format works for you.

After the manipulations in Excel you can save again as CSV as outlines above to make sure you resulting CSV file is UTF-8 encoded.

Finally to import the data you can use APEX, SQL Developer or SQLcl to load your CSV file into your table.

Oracle Database Tools updated - check out SQLcl

Today Oracle released new versions of:

• SQL Developer 4.1 EA2
• SQL Developer Data Modeler 4.1 EA2
• SQLcl (below SQL Developer downloads)

Also Oracle REST Data Services 3 got a new EA2 version.

You may want to check Kris Rice's blog for new features.

I already blogged about all of the tools before, but not yet about SQLcl.

This is a command line tool, I call it "SQL*Plus on steroids" (or as Jeff calls it SQL Developer meets SQL*Plus). It's particularly useful when you're on your server and quickly need to run some queries. Or if you're a command line guy/girl all the time, this tool is for you.

Here's a screenshot how to connect to your database with SQLcl from Linux.

Typing help will show you a list of quick shortcuts.

For example if you type APEX you get a list of your APEX applications

What I really like about SQLcl is that it formats the output so nicely. With SQL*Plus you had to set column widths, page sizes etc. Not with SQLcl, it's smart and formats it nicely.

Next to that you can quickly output your query in JSON format by typing "set sqlformat json":

There're many more features - a good starting point is this presentation and video by Jeff Smith.