In my Oracle REST Services Demos I always show things working exactly as planned. But what happens when your user does something your program doesn’t expect? Or what happens when you code does something you don’t expect? In the web (HTTP) world, you get a 500. 10.5.1 500 Internal Server Error The server encountered an unexpected condition which prevented it from fulfilling the request. Thankfully, PL/SQL provides EXCEPTIONS. We can say, when this bad thing…

Today’s question: I am looking for a way to list out all and count all objects by all schema, any idea? So the connection tree is nice in that it makes it easy to see specific types of objects by schema – but if you want a FLAT view of a schema, it’s not so great. Ok, so what’s a SQL Developer user to do? Try our Data Dictionary Reports The Reports panel is there,…

In case you haven’t figured this out by now, 90% of my material here is generated by questions from folks like you out there. So thanks for the never ending supply of content! And it gives me an opportunity to keep learning as well! So the question: I believe this json data type in MySQL would probably map to either CLOB or VARCHAR2 in Oracle. Is this something which can be achieved by defining custom…

Someone asked on StackOverflow how to get loading a 1,000,000 records to be faster in SQL Developer. My answer was basically, don’t use SQL Developer to load 1,000,000 records, use SQL*Loader. Now, I also reminded folks that you can USE SQL Developer to build your SQL*Loader scenario. There’s 2 ways actually: Query existing records, export to Loader (Keep Reading this post!) Point to a CSV, and use the Import Data Wizard But Wait, What is…

Was helping someone on Stackoverflow this morning, and thought you might enjoy seeing this process as well. PROBLEM: You’ve created a schema script, you’ve generated the DDL, and you ran it on your brand-spankin’ new database. Except. Except, you ran that script as SYS, and your schema scripts didn’t have a SCHEMA defined. So you’ve corrupted your SYS schema!!! How to fix that (FLASHBACK!) is a different post. This post is about how to assign…

Things to know: MANY bug fixes The Windows Embedded JDK is now Version 8 update 181 You can have nested connection folders in the main and DBA connection panels Real Time SQL Monitoring reports can be exported to (non-flash) HTML Connections Some of you out there work with MANY databases, and so of course have tons of connections to work with in SQL Developer. Now you can have as many levels of folders as you…

Why would I use SQLcl over something else? History recap: we introduced a new command-line interface for the Oracle Database in 2016. It’s everything you like about SQL*Plus, and everything you didn’t like about SQL*Plus – fixed. Do you have a minute? Watch this, then take 10 seconds to consider how YOU might use it. Everyday Things, Easier Just in 50 seconds I showed you: Object completion In-line editing Automatic SQL Formatting (pretty and CSV)…

The forwarding bit isn’t a new feature, but we have introduced new ORDS response header variables in version 18.3 of ORDS. You can now also read in the body of your request as a CLOB vs a BLOB. This post shows how to take advantage of both features. The only thing ‘new’ here is the name of this ORDS parameter. From the early days of the Oracle Application Express Listener, we always had X-APEX-STATUS-CODE and…

It’s easier to run things unsecured. It’s unconscionable to run things unsecured. DON’T BE THIS GUY. So Let’s Secure My Database and my Web Services So I have a collection of REST enabled objects (tables, view, and procedures) and a SLEW of RESTful Service modules. I want ALL of them locked down. So I need to create a Role and Privilege. And then I need to assign that privilege to what I want protected. Without…

Stumbling around on StackOverflow this morning, I came across this person’s HOMEWORK question, and the answer chided them on not using ANSI style JOINs. I’m not going to post the actual SQL here, but if you follow the previous link, it’s there for you to grab and have a go. I hope they get an A+ Something I like to do is take REAL code off the innerwebs and use it to test our parser,…

The question: We have big database with a lot of stuff and I want to use version control (Git) to manage changes. There are a lot of articles how to do it step by step but one piece is missing for me. Is there standard or recommended way for file structure of whole database (data excluded) and how it can be obtained from existing database? So I’m not sure about standard or recommended, but we…

After presenting this topic a few times over the years, I decided on a different take this Spring: I would start by introducing some requirements for a new project, and how ORDS fits into that and delivers what you need. I’ve also embedded our latest 15 minute video demonstration – enjoy! REST Enabling your Oracle Database (2018 Update) from Jeff Smith

I was inspired by Joel’s post on this subject for APEX so much, that I thought I’d copy his idea. So you’re reading this post on my blog, so you found me – maybe probably via Google. Or you walked into my mom at the grocery store. Either way, so glad you got here. But did you know, there are LOTS of places you get help or learn about SQL Developer out there? Let’s cover…

I was using the Instance Viewer today and ran into the drill down for Storage. Let’s take a closer look at that report. I like this report enough, that I want to be able to run it in SQLcl. SQLcl is a command-line interface, so I’ll have to make a few changes. The links won’t work, boo. Those are easy to fix up – just remove the SQLDev:Link code. The Used (proportion) column is a…

A follower reached out to me late yesterday with this request: And the answer is yes, we can pretty much get there. I’ve noticed a few things by looking at his hand-formatted code on the bottom section. He prefers: everything lowercase right-aligned keywords no line breaks for the sub query ()’s no line breaks after SELECT/FROM/WHERE keywords So let’s go tell the formatter to follow these same rules. Note that all other formatter preferences are…