WORKAROUND FOR DATABASE BIND ISSUES

Update: Thanks to Chris Jones for letting me know there has been an update to node-oracledb that handles the original problem.

I had originally written this post in November 2015, back then everything ‘didn’t work’ the way I had described it. But the node-oracledb project is very active and shortly after that, they added a feature which allows you to bind out to single value arrays. If you have tried my example code since then you would have noticed that the problem I described actually does work if you follow the comment from Chris Jones.

The point of the post is to demonstrate ways to work around problems that may come up when binding to PL/SQL programs. It could be bind issues like below, or maybe you have a very complex query in your code and you want to use PL/SQL to handle the complex part but still use it as a query.

I’ve updated this post to use a different example that will throw the same error. If/when my new example becomes obsolete due to new features in node-oracledb, just pretend the problem is real and try the solutions.

Using node-oracledb version: 1.9.3

I’ve been working on DinoDate, a demonstration application that shows how to implement the same functionality in different languages, all on top of an Oracle Database foundation. I had the Python code working and it was time to work with JavaScript and NodeJS, using the node-oracledb driver for the first time.

How I learn

When I’m learning something new, I prefer learning from code examples over reading documentation.  I pick up little extra nuggets of knowledge this way since I tend to break things in the examples, which I then have to sort out and fix.

However, I will only bang my head against the wall so many times before I read the docs. At last count, that was 57 times.

The Problem

One of the application examples demonstrates how to use a PL/SQL stored procedure to perform some different searches.  The procedure uses OUT parameters to pass the data back.

Here’s the PL/SQL code (defined in a package):

In Node, I attempted to bind to the out parameters like this:

This had worked several times before in other functions when I returned a single value from an out parameter.  This time, I couldn’t find a single node-oracledb type that would work. Most of them resulted in this error:

So I read the doc, fired up my trusty old search engine and searched, and then did what I should have done right from the start: lean on another member of my Oracle Developer Advocates team:  Dan McGhan.  Who immediately told me “The driver only support scalar types right now.”  Update: The driver also supports arrays of a single string or number.

When you learn by experimenting on code rather than reading the docs, sometimes you miss out on the ‘real words.’  I assumed ‘scalar types’ means a variable that holds a single value opposed to an array.  Just to be sure, I found a decent explanation on Stack Overflow.

Well, game over, right?

Ha! The game is only over when you quit. It was time to explore some options.

Change the PL/SQL procedure to a function
Return the results using JSON.

I considered running the query, building a JSON object from the results and returning them as a string in a clob.

But this doesn’t fit with the way the other application functions work. The database calls return unformatted results which are returned to the API functions and then those functions convert the results to JSON (or other formats if required) and sent back in the response.

Use a ref cursor

This could also be a viable solution and here’s a good example of how to use a ref cursor.

One goal of this demonstration application is that all functionality will be reproduced in multiple languages; Python, Ruby, PHP and others. I am trying to keep the functions as simple as possible. Other languages may not be able to work with a ref cursor.

Pipelined function

I decided to use a pipelined table function.  Table functions are very cool. In combination with the TABLE operator, I can return a collection to the FROM clause of a SELECT, and then have it treated as if it were a table, full of rows and columns. I can apply all the usual SQL SELECT operations on this dataset. Plus, the results are returned unformatted, so a developer should be able to use just about any language  to run a simple query built around this feature.

PL/SQL:

JavaScript:

This works great but what if you can’t change the PL/SQL procedure to a function because it’s used elsewhere?

Add a PL/SQL wrapper function
Now call text_only_wrapper in the JavaScript function above.

More options

Of course, there are other ways to work around the original issue and I’m sure there are better ones than what I’ve chosen.  If you have a better solution, please share it.

Share

Speaking of sharing.  The node-oracledb project is on GitHub under the Apache License, Version 2.0.  If you are able to add support for compound types such as arrays, please submit a pull request.

Leave a Reply