Node-oracledb: Support for cursor column in select

[rodrigoassumpcao]

There are a plan to support cursor column in a select ?

Example:

var sql = "" + 
"select d.department_id,\n" +
"       d.department_name,\n" +
"       cursor (select employee_id,\n" +
"                      first_name,\n" +
"                      last_name,\n" +
"                      phone_number\n" +
"                from employees e\n" +
"               where e.department_id = d.department_id) emps\n" +
"  from departments d\n" +
" where department_id = :department_id";

connection.execute(sqlPo, { department_id: 80 }, function(err, result) {  
    if (err) {  
        console.error(err.message);  
        doRelease(connection);  
        return;
    }  

    callback(result);
}); 

Result:
NJS-010: unsupported data type in select list

[cjbj]

It's on the long todo list. @dmcghan has been waiting for it too.

What output(s) do you want?

[rodrigoassumpcao]

Hi @cjbj

Hope you can develop this!!

The rows output can be something like that, but the metadata could be a problem...

{
   "rows": [
      {
         "DEPARTMENT_ID": 30,
         "DEPARTMENT_NAME": "Purchasing",
         "EMPS": [
            {
               "EMPLOYEE_ID": 114,
               "FIRST_NAME": "Den",
               "LAST_NAME": "Raphaely",
               "PHONE_NUMBER": "515.127.4561"
            },
            {
               "EMPLOYEE_ID": 115,
               "FIRST_NAME": "Alexander",
               "LAST_NAME": "Khoo",
               "PHONE_NUMBER": "515.127.4562"
            },
            {
               "EMPLOYEE_ID": 116,
               "FIRST_NAME": "Shelli",
               "LAST_NAME": "Baida",
               "PHONE_NUMBER": "515.127.4563"
            },
            {
               "EMPLOYEE_ID": 117,
               "FIRST_NAME": "Sigal",
               "LAST_NAME": "Tobias",
               "PHONE_NUMBER": "515.127.4564"
            },
            {
               "EMPLOYEE_ID": 118,
               "FIRST_NAME": "Guy",
               "LAST_NAME": "Himuro",
               "PHONE_NUMBER": "515.127.4565"
            },
            {
               "EMPLOYEE_ID": 119,
               "FIRST_NAME": "Karen",
               "LAST_NAME": "Colmenares",
               "PHONE_NUMBER": "515.127.4566"
            }
         ]
      },
      {
         "DEPARTMENT_ID": 100,
         "DEPARTMENT_NAME": "Finance",
         "EMPS": [
            {
               "EMPLOYEE_ID": 108,
               "FIRST_NAME": "Nancy",
               "LAST_NAME": "Greenberg",
               "PHONE_NUMBER": "515.124.4569"
            },
            {
               "EMPLOYEE_ID": 109,
               "FIRST_NAME": "Daniel",
               "LAST_NAME": "Faviet",
               "PHONE_NUMBER": "515.124.4169"
            },
            {
               "EMPLOYEE_ID": 110,
               "FIRST_NAME": "John",
               "LAST_NAME": "Chen111",
               "PHONE_NUMBER": "515.124.4269"
            },
            {
               "EMPLOYEE_ID": 111,
               "FIRST_NAME": "Ismael",
               "LAST_NAME": "Sciarra",
               "PHONE_NUMBER": "515.124.4369"
            },
            {
               "EMPLOYEE_ID": 112,
               "FIRST_NAME": "Jose Manuel",
               "LAST_NAME": "Urman",
               "PHONE_NUMBER": "515.124.4469"
            },
            {
               "EMPLOYEE_ID": 113,
               "FIRST_NAME": "Luis",
               "LAST_NAME": "Popp",
               "PHONE_NUMBER": "515.124.4567"
            }
         ]
      }
   ],
   "metaData": [
      {
         "name": "DEPARTMENT_ID"
      },
      {
         "name": "DEPARTMENT_NAME"
      },
      {
         "name": "EMPS",
         "metaData": [
            {
               "name": "EMPLOYEE_ID"
            },
            {
               "name": "FIRST_NAME"
            },
            {
               "name": "LAST_NAME"
            },
            {
               "name": "PHONE_NUMBER"
            }
         ]
      }
   ]
}

[cjbj]

@rodrigoassumpcao thanks for feedback. I suspect outFormat should have an effect.
And, yes, the format of metaData is worth thinking about.

[dmcghan]

Interesting, I hadn't thought about metadata before :)

Another thing to consider is whether an array should be returned or a ResultSet object. The array would be simpler but also subject to the maxRows limit (which limits its usefulness while working with very large result sets). A ResultSet would be more flexible/powerful but also more complex for simple things. Perhaps an option to get either one would be nice.

[cjbj]

At first thought, I'd keep it simple and let the current outFormat and resultSet properties equally affect both top level and nested results. The interesting thing would be how queryStream() should behave if resultSet is false.

[dmcghan]

Ah yeah, that makes sense...

queryStream currently overrides the value of resultSet, ensuring it is true.

Basically, you'd get a stream that would give you the rows as you'd expect, except that some of the columns may be ResultSets (which could then be converted to QueryStreams if desired).

Hmmmm...

[volkmarbuehringer]

you can use simple-oracledb to do your sql in 2 parallel sql-statements
after that you combine the results with map for arrays or simple insert the array if you
only have 1 row

for more complicated stuff you can use lodash.groupBy and combine the data using
the foreign-key

it is a fast and flexible solution for building complex json-objects, until oracle-db supports
json building ( 12.2 )

pool.parallelQuery( [{
sql: select * from los where l_id = :1,
bindParams: [req.params.l_id]
},
{sql: select * from (select * from zahlung_los where zl_lid = :1 order by zl_id desc ) where rownum < 11 order by zl_id,
bindParams:[req.params.l_id]},
{sql: select * from gewinn where g_lid = :1 order by g_id,
bindParams:[req.params.l_id]},
{sql: select * from einzug_los where ezl_lid = :1,
bindParams:[req.params.l_id]}
])
.then((results)=>{
const tt= _.groupBy(results[2],"G_ZLID");
results[1]= results[1].map((r)=>Object.assign(r,{gewinn: tt[r.ZL_ID]}));
return results;
})
.then( (results)=> Object.assign(results[0][0],{zahlungen:results[1]},{einzug:results[3]}) );

[nicholas-ochoa]

Is this something on the radar for the 4.x releases? We use nested cursors pretty extensively in our existing PHP codebase. As we've moved towards NodeJS we've had to do some workarounds to move queries over without too much modification, converting them to XML before passing the results back to NodeJS and then converting back to an object. It would be great if this was supported natively without the overhead of conversion.

[anthony-tuininga]

I can't commit that this will happen for the 4.x releases -- @cjbj is the one who can do that if he wants to do so -- but the code changes involved are minimal so there is definitely a possibility anyway! Right now, though, we are focused on preparing for the OpenWorld conference so it won't happen for a while yet. Please continue to be patient!

[cjbj]

@rage8885 I'd like to see this supported too. No promises, of course!

[nicholas-ochoa]

Thanks for the quick follow up, hopefully it's something that can be added before year end! 😊

[cjbj]

@rage8885 your comments on the earlier discussion on the interface and behaviors is welcome.

[nicholas-ochoa]

@cjbj I've done some testing of the nested cursor functionality added in 5.x and it works very well! A few notes/comments:

1. Regarding metadata, I think the example above (nested metaData properties where applicable) is perfect.

2. When using PL/SQL to return a cursor bind variable, the nested cursor ResultSets did not have any metadata. I assume this is expected since PL/SQL results don't have metadata, but it is a bit confusing.

3. The documentation states that each ResultSet should be closed once done, but when testing using a PL/SQL function returning a cursor bind variable I noticed that I could only close the entire result set once I completed fetching rows. If I close one of the nested cursor ResultSets after fetching NJS-018: invalid ResultSet is thrown when trying to close the main ResultSet.

[anthony-tuininga]

1. When using PL/SQL to return a cursor bind variable, the nested cursor ResultSets did not have any metadata. I assume this is expected since PL/SQL results don't have metadata, but it is a bit confusing.

Can you provide an example? The nested cursors should have metadata once they are fetched and it shouldn't matter that they are in PL/SQL.

1. The documentation states that each ResultSet should be closed once done, but when testing using a PL/SQL function returning a cursor bind variable I noticed that I could only close the entire result set once I completed fetching rows. If I close one of the nested cursor ResultSets after fetching NJS-018: invalid ResultSet is thrown when trying to close the main ResultSet.

Can you provide an example of this as well? I'd like to see what is happening. Thanks!

[nicholas-ochoa]

Can you provide an example? The nested cursors should have metadata once they are fetched and it shouldn't matter that they are in PL/SQL.

It looks like I was incorrect, metadata is being returned correctly for the nested cursor result sets on cursors returned from PL/SQL. I noticed it once or twice during testing but it's entirely possible I did something wrong. :)

Can you provide an example of this as well? I'd like to see what is happening. Thanks!

Here is a gist containing 3 tests: https://gist.github.com/nicholas-ochoa/bd5dccdf17b55009c09077425701191e

Each test contains a variation of the function unwindResultSet which is somewhat based on the traverseResults example within the docs (section 15.1.5).

• test1 works and is a close approximation to the final code I ended up with (we're using typescript and do a few other transformations, such as changing the object keys to lowercase, I removed all of that for simplicity). For this case, my unwindResultSet function only closes the ResultSet when the ResultSet is not a nested cursor.

• test2 fails with DPI-1039: statement was already closed. unwindResultSet here closes the ResultSet after fetching all available rows.

• test3 fails with NJS-018: invalid ResultSet. unwindResultSet here closes the ResultSet column instance after the recursive function call.

[anthony-tuininga]

Can you provide an example? The nested cursors should have metadata once they are fetched and it shouldn't matter that they are in PL/SQL.

It looks like I was incorrect, metadata is being returned correctly for the nested cursor result sets on cursors returned from PL/SQL. I noticed it once or twice during testing but it's entirely possible I did something wrong. :)

Well, if you discover it again, please let us know!

Thanks for the test cases. I'll take a look at them and get back to you with my findings.

[anthony-tuininga]

test2 and test3 are actually the same as you are attempting to close the result set twice in test3 and that's not supported. You are doing the close at the end of the function and also after calling the function for a nested cursor. I'm still looking into test2.

[anthony-tuininga]

I have discovered the source of the problem with test2. An attempt was made to set up the nested cursor even though no rows in the top-level cursor were fetched. Here is the patch if you wish to try it before @cjbj pushes the changes to GitHub officially:

diff --git a/src/njsVariable.c b/src/njsVariable.c
index 32948c45..7818c747 100644
--- a/src/njsVariable.c
+++ b/src/njsVariable.c
@@ -856,6 +856,10 @@ static bool njsVariable_processBuffer(njsVariable *var,
             }
             break;
         case DPI_ORACLE_TYPE_STMT:
+            // if no rows have been fetched or query variables have already
+            // been set up for a nested cursor, no need to do anything further
+            if (buffer->numElements == 0 || buffer->queryVars)
+                break;
             stmt = buffer->dpiVarData->value.asStmt;
             if (dpiStmt_getNumQueryColumns(stmt, &buffer->numQueryVars) < 0)
                 return njsBaton_setErrorDPI(baton);

[cjbj]

The doc needs tweaking too, I believe.

[cjbj]

I've pushed the updates.

[nicholas-ochoa]

Thanks - I just gave it a spin and now both test1 and test2 are working as expected. I'll hammer on it a bit more within my code base (I need to remove the XML wrappers and accompanying code we've been using to work with nested cursors).

[cjbj]

@nicholas-ochoa how did your nested cursor testing go?

[nicholas-ochoa]

So far everything is working as expected. I'm slowly converting code to remove the XML wrappers and haven't seen any new issues or concerns.

[cjbj]

@nicholas-ochoa good to hear. How's the performance?

[nicholas-ochoa]

When testing using a small result set (single row) I'm seeing a 3x-4x improvement in performance over our current method (convert cursor to an xmltype then to a clob in PL/SQL - then converting the XML string to a JavaScript Object after execution).

When testing using somewhat larger result sets the gains are smaller but still noticeable - usually a 2x improvement.

Datatypes are now maintained which is great - our current method converts everything to a string during the XML conversion step which wasn't a huge deal but still caused headaches. I suspect this is the reason for the lesser gains with larger result sets however - not due to any fault of node-oracledb. With our current method, the XML conversion simply removes any columns that are null - the resulting CLOB no longer has any reference to the column. The data set I'm testing against contains rows with a couple hundred columns where more than half of the columns are null.

Besides the improvement in performance and the datatype improvements, the simpler code is definitely a huge benefit. I've got two layers of wrappers on the PL/SQL side (wrapper to a utility function that converts cursors to XMLTYPE/CLOB, and a wrapper to our functions that return cursors to the utility wrapper), and a couple layers on the JS side (xml2js, cleanup functions to "fix" the resulting output of that operation).

[cjbj]

@nicholas-ochoa Simple is always good. Stay tuned as @anthony-tuininga works through some implementation buglets found during our testing.

[cjbj]

Nested cursors are now supported in node-oracledb 5.0. See Fetching Nested Cursors.