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.