Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Workaround for Database Bind Issues

DZone's Guide to

Workaround for Database Bind Issues

Need a workaround for using node-oracledb with a PL/SQL out parameter to bind_out to an array? I got you covered. Read on and I'll show you how it's done.

· Database Zone
Free Resource

Learn how to move from MongoDB to Couchbase Server for consistent high performance in distributed environments at any scale.

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):

TYPE t_member_r is record (member_id dd_members.member_id%type,
                           dino_name dd_members.dino_name%type);

TYPE t_members is table of t_member_r
 INDEX BY PLS_INTEGER;

PROCEDURE text_only (member_id_p IN INTEGER,
 search_string IN VARCHAR2,
 member_array out t_members);

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

binds.memberIdArray = {
 type: oracledb.?????,
 dir: oracledb.BIND_OUT
};

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:

PLS-00306: wrong number or types of arguments in call to 'TEXT_ONLY'

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. He immediately told me, “The driver only supports 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:

/* defined in package spec */
TYPE SearchType IS RECORD (
 member_id NUMBER,
 dino_name VARCHAR2(400) );

 TYPE SearchTypeSet IS TABLE OF SearchType;


/* defined in package body */
FUNCTION text_only(
  member_id_p IN INTEGER,
  search_string IN VARCHAR2)
  RETURN SearchTypeSet PIPELINED
IS
 retSet SearchType;
BEGIN
  FOR v_rec IN (
     SELECT member_id,
            dino_name
       FROM dd_members
      WHERE contains (about_yourself, 
                         search_string) > 0
        AND member_id != member_id_p) LOOP
            retSet.dino_name := v_rec.dino_name;
     retSet.member_id := v_rec.member_id;
     pipe ROW (retSet);
  END LOOP;
  RETURN;
END;


JavaScript:

var binds = {memberId:123, keywords:'words'};

connection.execute(
 'SELECT member_id as "memberId", dino_name as "name" ' +
 'FROM TABLE(text_only(:memberId, :keywords)) ' +
 'ORDER BY dino_name ',
 binds,
 {
 outFormat: oracledb.OBJECT
 },....

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

/* defined in package spec*/
TYPE SearchType IS RECORD (
 member_id NUMBER,
 dino_name VARCHAR2(400) );

 TYPE SearchTypeSet IS TABLE OF SearchType;


/* defined in package body*/
FUNCTION text_only_wrapper(
 member_id_p IN INTEGER,
 search_string IN VARCHAR2)
 RETURN SearchTypeSet PIPELINED
 IS
 retSet SearchType;

TYPE member_id_array_type IS TABLE OF INTEGER
 INDEX BY PLS_INTEGER;

TYPE dino_name_array_type IS TABLE OF VARCHAR2 (500)
 INDEX BY PLS_INTEGER;

member_id_array member_id_array_type;
dino_name_array dino_name_array_type ;
BEGIN
/*
   Call the original procedure
   Loop through the results
   Pipe the output
*/
  text_only (member_id_p,
             search_string,
             member_id_array,
             dino_name_array);

  FOR indx IN 1 .. member_id_array.COUNT 
  LOOP
     retSet.dino_name := dino_name_array(indx);
     retSet.member_id := member_id_array(indx);
     pipe ROW (retSet);
  END LOOP;
  RETURN;
END;

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. DinoDate and the node-oracledb project are both on GitHub. If you are able to add support for compound types such as arrays, please submit a pull request.

Want to deliver a whole new level of customer experience? Learn how to make your move from MongoDB to Couchbase Server.

Topics:
oracle database ,javascript ,nodejs

Published at DZone with permission of Blaine Carter. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}