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):
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 |
binds.memberArray = { 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:
|
1 |
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. 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:
|
1 2 3 4 5 6 |
/* defined in package spec */ TYPE SearchType IS RECORD ( member_id NUMBER, dino_name VARCHAR2(400) ); TYPE SearchTypeSet IS TABLE OF SearchType; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
/* 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:
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 6 |
/* defined in package spec*/ TYPE SearchType IS RECORD ( member_id NUMBER, dino_name VARCHAR2(400) ); TYPE SearchTypeSet IS TABLE OF SearchType; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
/* 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; |
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.