Support PL/SQL and SQL types like Oracle Spatial SDO

[bmulcahy]

I keep getting NJS-010: unsupported data type in select list, when I try to retrieve sdo geometries from a database.

I then tried running a stored function(SDO2GEOJSON) but received the same NJS-010 error but didn't know if that was do to functions not being supported or geometries.

Or this could all be user error on my part.

[cjbj]

@bmulcahy there's no support for complex data types. It's on the long list of things to look at but hasn't been prioritized. You could write a wrapper PL/SQL block to decompose the SDO into simple types and call the PL/SQL code.

[bmulcahy]

What data-types are supported?

If i have a stored function on the server to return a string will that work?

Thanks for the help.

[cjbj]

@bmulcahy You can use string, date and number OUT binds from PL/SQL blocks. Binding in node-oracledb as string lets a wider range of scalar PL/SQL types be returned.

[soichih]

I am seeing "Error: NJS-010: unsupported data type in select list" when I do "SELECT *" from various tables that I am looking at. I am not sure which column / field type that node-oracledb is having trouble accessing.. Is there a way to enable more verbose output - to show which column and which field type is not supported?

[dmcghan]

@soichih I agree that it would be nice if the column name was provided. However, for this type of ad-hoc exploration, I'd recommend using a tool like SQL Developer. It's free and a lot more productive than using the Node.js driver for that type of task.

[dmcghan]

If anyone lands here and needs an example of how to bind record types and/or arrays of record types until there's built-in support for this, here you go: https://jsao.io/2017/01/plsql-record-types-and-the-node-js-driver/

[smtl]

@dmcghan Thanks for the example. For a function I have

:ret := my_package.my_function( p_the_thing => l_plsql_thing );

In my bind variable, do I still have to have a type attribute?

var bindVars = { input : '111', ret: { dir: oracledb.BIND_OUT, type: oracledb.??? } }

EDIT: In my case the PLSQL is returning a user defined type. I have it working by returning a SYS_REFCURSOR from my PLSQL but wondering if that is the correct way.

[dmcghan]

@smtl Whether a SYS_REFCURSOR is right for you depends on what you're doing. If you want a cursor, then that should be fine. But if you wanted, say, an array of record type variables, then you'd have to do the reverse of what I do in my example.

In other words, rather than assign the result of the function call to a bind variable, you'd assign the result to a locally declared variable in the PL/SQL block. Then you'd have to break the single array up into multiple arrays of type number or varchar2. Those arrays would be the out binds.

When the arrays make it to the JavaScript context, you could reformat them as needed.

Does that make sense?

[smtl]

Yep, I get what you mean. Thanks.

[teddykh]

Is the ability to bind complex types (record types and/or arrays of record types) anywhere in the roadmap? It's a very important addition especially for users who need to integrate with eBusiness Suite because many "PL/SQL APIs" are exposed as Stored procedures with Table of records type; so instead of interacting directly with the built-in public procedures of EBS, we have to add an extra wrapper for each integration point. On the other hand, would it be possible to think of a "generic wrapper" (dynamic)? From the first look, it seems to be complex... any thoughts?

[anthony-tuininga]

The announcement made in the past few days (https://github.com/oracle/node-oracledb/issues/601) lays the groundwork for this support. The ODPI-C library has support for this built in. I think I can say that it is somewhere in the roadmap....but I'll let @cjbj comment on where it is found. :-)

[cjbj]

There are a lot of different users who prioritize different features. Hearing feedback helps us judge which way to head, so thanks for updating this issue. At this stage we are focused on 1.13 and 2.0 beta (which have effectively the same feature set, see #601). We can then do a review of priorities and decide what we should look at next. There will always be this option too, a point I hope is taken in good spirit since I know not everyone will be able to contribute!

[ahummel25]

@cjbj Has this been discussed/prioritized any further? Mainly referring to the enhancement in #726.

[cjbj]

@ahummel25 it is 'closer' because the ODPI-C layer in node-oracledb v2 has this. ODPI-C replaced the DPI layer which didn't have it.

[ragFOEX]

Hi all, just stopping by to express my interest in supporting complex datatypes, such as record types (and arrays) in the node-oracldb driver. Thanks @dmcghan for your blogpost showing the possible workaround. Of course I'd like to have that supported natively :-)

[ahummel25]

@cjbj Any progress on this regarding being able to bind a PL/SQL table structure to a procedure argument?

[anthony-tuininga]

Not yet, unfortunately. It is still under consideration, though!

[lkiii]

@cjbj Any news on custom type support?

[cjbj]

@lkiii Nothing's on paper yet. It's not going to be a small project.

[cjbj]

The first, major drop of code to support binding named Oracle types is on the dev-4.0 branch. See https://github.com/oracle/node-oracledb/issues/1053#issuecomment-504703826

[cjbj]

Node-oracledb 4.0 has been released with an enhancement for querying and binding of objects. See https://oracle.github.io/node-oracledb/doc/api.html#objects.

A specific Spatial demo is at https://github.com/oracle/node-oracledb/blob/master/examples/selectgeometry.js

The 4.0 release announcement is at https://blogs.oracle.com/opal/oracle-db-named-objects-and-advanced-queuing-support-new-in-node-oracledb-40