Oracle ref cursor oddities and size question

25 views
Skip to first unread message

Martin Landvoigt

unread,
Feb 2, 2017, 11:18:34 AM2/2/17
to dbfit
I managed to run some tests for oracle and an OUT refCursor. Two open points remained:

Using a long cursor  with somwhat of 70 cols I can retreive (by query) some cols, others not. Got then:

java.lang.Exception: Unknown column status_code
	at dbfit.fixture.RowSetFixture.findColumn(RowSetFixture.java:63)
	at dbfit.fixture.RowSetFixture.bind(RowSetFixture.java:74)
	at fit.ColumnFixture.doRows(ColumnFixture.java:18)
	at dbfit.fixture.RowSetFixture.doRows(RowSetFixture.java:93)
	at fit.Fixture.doTable(Fixture.java:156)

But the col exists as well as the others, which returns proper vals. 

---
If I call a test with no return rows in cursor is generated, I can test it by ommiting rows in the finesse table. There is no green indication that the assertion had met. 

But if there would have been one or more rows, a surplus error would have been generated. Also, if an exception would have been raised, it would have been shown. 
Nevertheless, there is no green positive test result.

Probably, there may be a rowcount in the cursor, which indicate 0, but have no clue how to access and test it. 

Mark Matten

unread,
Feb 2, 2017, 2:29:48 PM2/2/17
to dbfit
Is the list of columns that appear to be missing always the same for this procedure? What happens if you remove status_code from the output?

Yavor Nikolov

unread,
Feb 2, 2017, 5:27:35 PM2/2/17
to dbfit
Hi Martin,

Would you be able to share a concrete example where the issue is reproducible?

On Thu, Feb 2, 2017 at 9:29 PM, Mark Matten <mark_...@hotmail.com> wrote:
Is the list of columns that appear to be missing always the same for this procedure? What happens if you remove status_code from the output?

--
You received this message because you are subscribed to the Google Groups "dbfit" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbfit+unsubscribe@googlegroups.com.
To post to this group, send email to db...@googlegroups.com.
Visit this group at https://groups.google.com/group/dbfit.
For more options, visit https://groups.google.com/d/optout.

Martin Landvoigt

unread,
Feb 3, 2017, 5:30:01 AM2/3/17
to dbfit
It works fine when I remove those cols which raises exeptions.

Yavor Nikolov

unread,
Feb 3, 2017, 6:39:19 AM2/3/17
to dbfit
What if you keep just a single column in your test - e.g. "status_code". Is the issue still reproducible?
Is there anything special about that column - data type, name in lowercase, etc.?

Again, if you manage to setup and share a concrete simplified test case - it might help us to diagnose the behavior.
 

--

Martin Landvoigt

unread,
Feb 3, 2017, 11:10:12 AM2/3/17
to dbfit
Hi - I tried to build a spin off to show ... and solved the problem. May be it is of some interest to learn:

The procedure OUT var was declared with the type declared as

TYPE c_account IS REF CURSOR
      RETURN r_account;

In the record declaration the proper name STATUS_CODE was defined. But when the cursor was opened the referred query said:

...
              , a.status_cid
              , pc_sys_map_appl_code_value.get_code (pc_code.appl_code, a.status_cid) 
              , a.email_yn
...

This mean, the mapping was fine, but the query did not explicitly assign the name.  When I changed it to an alias name in the query, the problem was solved.

              , pc_sys_map_appl_code_value.get_code (pc_code.appl_code, a.status_cid) AS status_code

Java driver referes rather to the query than  to the type declaration. It is not an error, but you may come to failed expectation.



Am Donnerstag, 2. Februar 2017 23:27:35 UTC+1 schrieb Yavor Nikolov:
Hi Martin,

Would you be able to share a concrete example where the issue is reproducible?


---------------------

The only thing left: Is there a way positivly test for an empty cursor like

!| query | select count(*) AS cnt from <<refCursor |
| cnt ? |
| 0     |
 
Any ideas?

Yavor Nikolov

unread,
Feb 3, 2017, 12:18:55 PM2/3/17
to dbfit
Hi,

Good to hear that you managed to solve the issue with the problematic columns. (It might be interesting to check what column names would be shown by other tools when you skip the column alias - e.g. in sqlplus or something else).
--

The empty query test can be done the way you did (just skip the rows). The thing is that currently not the query tables as a unit but the individual rows are counted as "Assertions" (you can see that in the page summary at the top).

--

Martin Landvoigt

unread,
Feb 6, 2017, 5:28:39 AM2/6/17
to dbfit
Thanks for your reply ... I got the solution via an empty table, gut would appreate to get a positiv feedback. 
Additionally to the empty cursor, it may be helpful to check positivly any command, like query, procedure call or execute: If there is no exception, there may be a green feedback. 

Perhaps an idea for the next release.


Am Freitag, 3. Februar 2017 18:18:55 UTC+1 schrieb Yavor Nikolov:

The empty query test can be done the way you did (just skip the rows). The thing is that currently not the query tables as a unit but the individual rows are counted as "Assertions" (you can see that in the page summary at the top).
The only thing left: Is there a way positivly test for an empty cursor like

!| query | select count(*) AS cnt from <<refCursor |
| cnt ? |
| 0     |
 
Any ideas?

-- 
Reply all
Reply to author
Forward
0 new messages