Follow ThatJeffSmith on Feedspot

Continue with Google
Continue with Facebook

ThatJeffSmith by Thatjeffsmith - 4d ago

Of course you can debug your PL/SQL from SQL Developer when you start it in SQL Developer.

But what if your PL/SQL is being executed from ‘somewhere else’ – and you want to debug it form there?

That’s known as remote or external or just-in-time debugging, and we support that.

Here are some slides:

Setup in a nutshell

You are going to tell your APEX app to connect back down to your machine where SQL Developer is running, so you need to make sure:

  • your laptop can be reached on the network from where your APEX app (db) is running
  • if your db is 12c or higher, that there’s an ACL rule in place to let the database reach out on the network
  • start a remote listener in SQL Developer
  • have your PL/SQL compiled for debug
  • have at least one breakpoint set

Here’s what the APEX setup looks like…

We’ll come back to the highlighted code in a minute.

I have a APEX form with some custom processing built-in, which calls this PL/SQL program.

This is what I want to debug.

In SQL Developer, I right-click on my connection, and ask for Debug – Remote:

This is the IP address of YOUR machine.

With this listening now, we can almost get started.

Let’s go back and look at the code tied to the in page processing in APEX again.

I’ve added this line:

dbms_debug_jdwp.connect_tcp('', 4000);

When the database runs this, it’s going to go to that IP address and port, and it’s going to give that machine control of the session once some PL/SQL is executed. And it just so happens the next bit of PL/SQL to be ran is what I want to debug.

So just run your APEX app. Or in other words, ‘exercise the code.’ Then when the PL/SQL is encountered, your app will ‘freeze’, and SQL Developer will immediately take over.

Now we’re debugging!

Make sure you have the debugger set to start with a ‘Step’ or you have at least one breakpoint set, or the code will run right through SQL Developer and your application will take back control. It will work, but you wont have noticed any debugging..unless you hit an Exception.

The default is ‘Run until a breakpoint occurs’
Need a general overview/refresher?

Here’s the movie:

Oracle PL/SQL Debugging with SQL Developer - YouTube
Less than 8 minutes
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Every year the Database teams sends a delegation of experts to the ODTUG KScope conference. This year (June 23-27, 2019) is no different – look at all these awesome database product managers, developers, and advocates scheduled to appear:

This isn’t even everyone, click the link for a full list!! Core Training on Sunday for $599

We worked with ODTUG to make 1 day passes available. If you’re in the Seattle area, consider this deal:

  • 5 hours training
  • coffee, lunch, and snack
  • conference networking and welcome reception (apps & drinks)

If you look at the training the Database Team is delivering, you’ll see we’re covering ALL the basics from our best resources. We wanted to do CORE concepts, that would be good for the most basic 101 level attendee AND for the seasoned pro that might need some help filling in some knowledge gaps.

Connor McDonald will teach you how SQL works.

Folks, this guy is travelling from Perth, Australia to deliver this content. He’s one of our best, and most entertaining speakers, and he’s half the team behind AskTom.

Maria Colgan will teach you how to read an Explain Plan.

You may know Maria from such hit shows as, “I was the product manager for the Oracle Optimizer” and “Keynoting Conferences is my Middle Name.” She’s literally a living legend inside and outside the company.

Steven Feuerstein will teach you how to write good, performant PL/SQL.

I would say that Steven wrote the book on PL/SQL, but it was more like 8 books, and ALL of you probably have at least one of them on your shelf or in your digital library.

Steven still does PL/SQL and he runs our Database Advocates team, AND he’s trying to make the world a better place.

Blaine Carter will get you going quickly with delivery secure, RESTful access to your Oracle Database.

Blaine wow’d me at Open World this past year. I saw him do a soup-to-nuts full presentation, complete with Live Demo, for installing ORDS, publishing a service, and securing it with OAUTH2. And it was fun, and it captivated the audience. And he’s also pretty handy with a soldering iron, python, and a raspberry pi.

Oh, and I will do an hour to kick things off around database design and data modeling.

But that’s not it!

All learning and no play is a recipe for Zzzzz, no matter how good the content is. So, we’re also going to be doing a fun game show with lots of prizes in the afternoon to keep us going until they bring out the coffee again.


Need to catch up on Application Express (APEX)? Or how about BI & Analytics? Your one day pass covers all those symposium events as well.

Don’t get me wrong, I’ll be upset if you don’t spend at least SOME time with us.

One day not enough?

The event goes until Thursday, with lots of tracks to cover all the Oracle application development areas you’re looking for, plus lots of fun and networking involved as well. But, this is the first year you have the opportunity to invest in as little as a single day.

Google ‘oracle training costs.’ Now look at the $599. Now look at who is doing the training. You’re not going to top that.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The wonderful world of Java has seen some pretty big changes in both licensing and technology lately.

New terms for using the Oracle JDK went into affect recently, and we’ve seen JDK 11/12 updates released.

What does this mean for Oracle SQL Developer users?

Your existing licenses gives you access to the Oracle JDK at no additional cost.

The details behind this can be found in these resources:

The TL/DR; version of those links basically comes down to this – Oracle products requiring Java, get to use the Oracle JDK at no additional cost.

Oracle SQL Developer, Oracle SQL Developer Data Modeler, Oracle SQLcl, and Oracle REST Data Services are all Oracle products. They’re also all Oracle products that are included with Oracle Database. Oracle Database also requires a JDK.

You get to use the Oracle JDK for our products.

When will you support Oracle JDK 11?

Yes, I know it’s 2019 and SQL Developer still requires Java 8. The good news is that very soon this year we’ll have official Java 11 support going.

We have it working internally, and should be good to go soon.

You can already use Java 11 for Oracle REST Data Services and SQLcl.

Now, about the OpenJDK. It should probably work, especially now as that is now directly based off the Oracle commercial distribution and pretty much includes everything.


We won’t officially support it. That means we won’t test or certify our products with OpenJDK and if you have problems and want to open an SR with MOS, you’ll need to test your scenario with Oracle Java first.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

With some excellent feedback from our friends on the In-Memory and Real World Performance tuning groups here, we made the following improvements to the SQL Monitoring interface in SQL Developer:

  • Availability of Other stats now indicated with a button on the report grid
  • In-memory stats available, shown in a different color and an expanded toolbar message
  • Columnar encodings stats from xml now included

And, because we knew we needed to, we got the Performance Charts added to the HTML report exports.

When we know there’s more information available, we indicate that with a little camera button –

When there’s more info available, we’ll mark it with that button highlighted above.

Click on said button, and I can see my Degree of Parallelism was DOWNGRADED TO 1!!!

Downgrade reason: your query sucks!

Actually, it’s because of reason 354. You know these by heart, yes?

Thanks Yasin for this query.

The query (as that’s not a fun one to type, and yeah x$ – you’ll need high privs to see this, but you already need high privs to run these reports, so hopefully it’s OK):

SELECT	qksxareasons,indx		
FROM	x$qksxa_reason		
WHERE	qksxareasons LIKE '%downgrade%';
Nicer, more complete HTML exports.

Now when you use the Save button, you have the option for HTML (no flash!), a screenshot, or the raw XML you need to do a transform. 99% of you will be fine with the HTML.

Pick your poison.

And finally, the HTML now includes these (the Metrics Graphs) –

Ta-da…again. Remember folks – this feature requires the Tuning Pack.

I show you the features, but you need to know when/where you can use them. If in doubt, check with your Oracle account manager BEFORE you start running these reports.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Oracle REST Data Services has for the past 5-6 years given you the ability to develop and deploy RESTful Services for your Oracle Database – by just writing some SQL and PL/SQL.

ORDS, in a Nutshell

Want to GET some table data out? Build a GET handler with a SELECT statement. Want to submit a sales order? Build a POST handler that calls your application PL/SQL package. And your responses are automatically transformed to {JSON} and obey all the rules of the HTTP road.

Now with ORDS version 19.1, we’re offering a new feature – the ability to manage your Oracle instances via REST calls.

This from the 19.1 README

Important Changes to Note
Database Management REST API
This release sees the introduction of the Database Management REST API. Please consult the ORDS documentation library for instructions on how to enable this feature, which is disabled by default.


So, I don’t want our current ORDS customers to freak out, thinking by upgrading or deploying 19.1, that you’ll be exposing the guts of your database via this API. It MUST be enabled for it to be accessible, otherwise you’ll see 404’s when you attempt to call it.

The ORDS 19.1 Docs Page. Enabling the Feature, Privileges Required

You need to add a line to your defaults.xml file:

<entry key="database.api.enabled">true</entry>

With ORDS started and this parameter enabled, you won’t get 404’s anymore. Now you’ll get 401’s.

You’ll need to REST enable a schema, and that schema needs the DBA role. Then, using Database Authentication, you can access an endpoint from the DB REST API.

Note, if you’re in a multitenant environment/Pluggable Database, your user will need the PDB_DBA role.

Versions of the Database Supported

  • 11gR2
  • 12cR1
  • 12cR2
  • 18c
  • 19c

Obviously you can’t make calls to do pluggable operations on 11gR2, but any environment where you can configure and run ORDS, the DB API supports that database.

And speaking of PDB stuff, if you want to use the lifecyle management end points, there’s an additional amount of setup required – which I’ll cover in a follow-up post.

The API as Described by OpenAPI, Swagger Doc

You can find the entire API online here –

The tasks are spit into different functional areas:

For the moment, all but Fleet Patching are delivered via ORDS.

Let’s say I want to pull a list of objects…INDEXES…for a given SCHEMA. I’ll find a ton of GET end points under Data Dictionary – not to mention 100+ other end points available throughout the API.

A few of these could work, but I”m going to go with ‘Get all indexes’

My base URL for all my DB API REST calls will be:

 'http://localhost:8080/ords/hr/_/db-api/19.1.0/' or


  • HR – the REST enabled user the call will be ran as, also the DB user you need to use for DB authentication on the request
  • _/db-api/ – the ‘module’ or namespace for the API
  • 19.1.0 – the version of the API or
    • latest – the current version of the API that ships with ORDS install

For version 19.1, latest and 19.1.0 will be synonymous, as there’s only one version of the API today. If you want to ensure responses don’t change between end points, consider using the 19.1.0 in your URIs. If you’re cool with the latest and greatest, then latest is your cup of tea.

Making the call.

I want all the INDEXES for MDSYS.

So I’ll use the Query Parameters filtering feature to say, just give me indexes where OWNER = MDSYS


Note each item in the collection includes a link to said item, so I can traverse my collection by going to


Which will give me this:

  "owner": "MDSYS",
  "index_name": "UNIQUE_LAYERS",
  "index_type": "NORMAL",
  "table_owner": "MDSYS",
  "table_name": "SDO_GEOM_METADATA_TABLE",
  "table_type": "TABLE",
  "uniqueness": "UNIQUE",
  "compression": "DISABLED",
  "prefix_length": null,
  "tablespace_name": "SYSAUX",
  "ini_trans": 2,
  "max_trans": 255,
  "initial_extent": null,
  "next_extent": null,
  "min_extents": null,
  "max_extents": null,
  "pct_increase": null,
  "pct_threshold": null,
  "include_column": null,
  "freelists": null,
  "freelist_groups": null,
  "pct_free": 10,
  "logging": "YES",
  "blevel": 0,
  "leaf_blocks": 0,
  "distinct_keys": 0,
  "avg_leaf_blocks_per_key": 0,
  "avg_data_blocks_per_key": 0,
  "clustering_factor": 0,
  "status": "VALID",
  "num_rows": 0,
  "sample_size": 0,
  "last_analyzed": "2017-01-26T20:11:48Z",
  "degree": "1",
  "instances": "1",
  "partitioned": "NO",
  "temporary": "N",
  "generated": "N",
  "secondary": "N",
  "buffer_pool": "DEFAULT",
  "flash_cache": "DEFAULT",
  "cell_flash_cache": "DEFAULT",
  "user_stats": "NO",
  "duration": null,
  "pct_direct_access": null,
  "ityp_owner": null,
  "ityp_name": null,
  "parameters": null,
  "global_stats": "YES",
  "domidx_status": null,
  "domidx_opstatus": null,
  "funcidx_status": null,
  "join_index": "NO",
  "iot_redundant_pkey_elim": "NO",
  "dropped": "NO",
  "visibility": "VISIBLE",
  "domidx_management": null,
  "segment_created": "NO",
  "orphaned_entries": "NO",
  "indexing": "FULL",
  "links": [
      "rel": "self",
      "href": "http://localhost:8080/ords/hr/_/db-api/latest/database/objects/indexes/MDSYS,UNIQUE_LAYERS"
      "rel": "describedby",
      "href": "http://localhost:8080/ords/hr/_/db-api/latest/metadata-catalog/"
      "rel": "collection",
      "href": "http://localhost:8080/ords/hr/_/db-api/latest/database/objects/indexes/"
Pulling up the Swagger Doc via ORDS

We have metadata-catalog and openapi end points for the DB API as well.


You can paste the response from this call into something like editor.swagger.io and generate a full test client for your DB API

Nicer than reading the raw JSON for sure. What’s Next?

I”ll be doing more demonstrations and examples here of course. But you can also expect this API to grow substantially as each new version of ORDS is released. We already have a good start on the Database Creation Assistant, and the Data Pump team is going to build out a complete api for that, so it’s not all GET’s around here.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Version 19.1 of SQL Developer, SQLcl, Data Modeler, and ORDS are now all available, and you can expect many posts over the next few weeks to cover all the new goodies.

Today I want to highlight some changes we made to how you define your connections.

One change you’ll see right away is a simpler connection dialog:

This is how it looks in version 19.1

The name and color of the connection is up top now, and it’s hopefully less likely you’ll type your password into the username input area by accident!

Proxy User info is moved up top next to the rest of the username stuff – it’s no longer on an advanced pop up dialog.

Now about that Advanced page you still see there, I want to call out a new feature exposed there:

You can now define connections as THICK or THIN.

Previously, SQL Developer would be configured for THICK or THIN connections, application wide, via the ‘Use OCI’ preference on the Advanced page.

If I enable it here, it will mean EVERY Oracle connection going forward will be of type OCI.

Just to back up a bit, your options for connecting to Oracle with our JDBC driver are:

  • thin = PURE JAVA, what most people should be using
  • thick = mix of Java and native compiled libraries via Oracle Client or Instant Client, required for some use cases like RADIUS authentication

And it’s totally fine to usually just go with THIN. Nothing else needed to install or configure.

There are however, some reasons you might need to go ‘thick.’ You can read all about how this works here.

But now, I can have connections in a single instance of SQL Developer going with both THIN and THICK configurations.

If we look at the connection properties for the THICK connection:

Yup, that’s it.

You’ll still need to go about configuring your $ORACLE_HOME or Instant Client in the Advanced Preferences page. And, I still recommend you use the TEST feature, to make sure your OCI connections will be available. The ‘show jdbc’ command ran through our script engine (F5) will also confirm your type of connection.

If you see Success and all OK’s, you should be good to go.
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
ThatJeffSmith by Thatjeffsmith - 1M ago

My wife and I are celebrating 20 years married (to each other!!!) , and I will not be here answering questions or posting new content for the next two weeks.

However, there is a LOT scheduled to happen in the database tools world while I’m out. So be sure to follow @krisrice for news and updates.

Oh, and if you like to torture yourself by watching others having fun, you can find me pretty easily on Instagram.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

We’ve talked about the AUTO REST feature of ORDS for TABLES quite a few times here. What I want to talk about today is how you can take advantage of that TABLE REST API in your favorite Excel spreadsheets.

The Oracle Visual Builder Add-in for Excel ‘allows Excel users to edit business data available from REST services.’ Well, that’s pretty much what ORDS offers. You can find the docs here.

The installer is very simple. Open Excel and you’ll see a new item in your ribbon.

This is what you’re looking for.

Hit the ‘Designer’ button, and you’ll get prompted for a URI that contains ‘open-api-catalog’, and I gave mine this


ORDS OpenAPI links for REST enabled tables includes the docs for the GET, PUT, POST, and DELETE end points on your collection – which in this case, is my UNTAPPD table.

Once it gets started, it’ll ask for a username/password. I left mine blank, but I’ve also tested the ORDS user/password basic auth, and both work just fine.

You’ll get the list of columns back in your table.

So far, so good.

Now comes the fun part – hit the ‘Download Data button’

It’ll fetch data until it gets to 499 rows, and then ask you if you want to GET the rest or not:

I said, ‘OK!

Now I have some data in my Excel sheet. Let’s change a value.

I changed a label for my beer, and marked the ‘Change’ column.

Gonna hit that Upload Changes button next.


Cool, cool.

This smells like witchcraft to me, let’s go check the database.

OK, I guess it works! So what does this mean?

The VBCS team built a nice plug-in for Excel. If you have an ORDS REST Enabled TABLE, you can now also use Excel as a front end for managing your data. Now, I might rather you build an APEX app for that, but folks do seem to like Excel an awful lot…

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Parallelism – where the database splits your task (Query) into multiple pieces, each running concurrently – theoretically making a lot of work happen for you, faster. Docs: How it Works.

How it works, from the Docs.

So, now you’re probably asking, OK, so I have a query, and I really, really want it to be running in this parallel fashion, how can I know if it’s doing that or not?

One thing you check is ‘the plan.’

SQL*Plus Plan Notes

Scroll to the end of your plan, and you’ll see some notes.

For this query:

SELECT /*+ PARALLEL */ first_name,
  FROM employees,
 WHERE employees.department_id = departments.department_id;

In SQL*Plus I see this:

Well, I don’t see the RED box outline, I added that.

We’re being told a couple of things. HR.DEPARTMENTS influenced the degree of parallelism to be applied. And, said degree of parallelism was ‘2’.

In SQL Developer

Step One: Enable the ‘Other XML’ Column.

The PLAN TABLE in Oracle has been around for a long time. At a certain point, instead of adding new columns for things like, plan notes, we just added an XML column, that we could throw any old thing into.

SQL*Plus parses that information and prints it.

SQL Developer let’s you see ALL of the information in the ‘Other XML’ column, AFTER you turn it on.

It’s THIS one.

So with that on, let’s get a plan, I’ll use the Cached Plan (v$sql_plan) feature.

That’s kinda small and hard to read, so let’s blow it up…

A bit bigger, so it’s easier to read:

It’s telling us the same thing SQL*Plus told us.

So again, we have the DOP, and the ‘Reason.’

This column has lots of goodies, including whenever Adaptive Plans have come into play, if an Outline or Baseline determined the plan, or what HINTS you could use to make the same plan pop out somewhere else.

P.S. Thanks @SQLInterstellar for today’s topic!

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview