1

I am trying to fetch a collection of custom objects from an oracle db (v21) from a .net client. Since i cant do any type mapping i want to fetch it as json.

Here is the query:

select json_array("UDTARR") from sys.typetest

This is the result i see in sql developer (expected output): This is what i get when i execute the same query via .net:

"[]"

The same strategy (json_array()) seems to work fine in .net for collections of primitive types as well as for non-collection-type fields of the same custom object.

Please someone tell me i´m missing something obvious?

Here are the type definitions in case someone wants to try to replicate the issue:

The type that is used in the field "UDTARR":

create type udtarray AS VARRAY(5) OF TEST_DATATYPEEX;

Type "TEST_DATATYPEEX":

create type TEST_DATATYPEEX AS OBJECT
(test_id NUMBER,
vc VARCHAR2(20),
vcarray stringarray)

Type "STRINGARRAY":

create type stringarray AS VARRAY(5) OF VARCHAR2(50);

Code for executing the query and reading the value:

string query = "select json_array(\"UDTARR\") from sys.typetest"
using (var command = new OracleCommand(query, con))
using (var reader = command.ExecuteReader()){
    while (reader.Read()){
        Console.WriteLine(reader.GetString(0))
    }
}

In the Eventlog both queries are recorded, in both cases the user is connected with dba privileges:

(from sql developer)

Audit trail: LENGTH: '362' ACTION :[45] 'select json_array("UDTARR") from sys.typetest' DATABASE USER:[3] 'SYS' PRIVILEGE :[6] 'SYSDBA'

(from .net)

Audit trail: LENGTH: '361' ACTION :[45] 'select json_array("UDTARR") from sys.typetest' DATABASE USER:[3] 'SYS' PRIVILEGE :[6] 'SYSDBA'

Paul
  • 5,524
  • 1
  • 22
  • 39
  • Just a try: are you using the same user (better same connection string) in the .Net program and when you run the query from sql developer? Just a try to be sure that the different output does not depend on connected user – Gian Paolo Jul 08 '22 at 20:49
  • @GianPaolo good idea but its the same user and permssions, i have already checked and both sessions are connected with dba privileges, i´ve attached logs – Paul Jul 08 '22 at 20:50

2 Answers2

1

Ok looks like i might be retarded... closing sql developer gave me a prompt that there where uncommited changes, after commiting and closing sql developer i am now also receiving the expected data in .net.

I have never seen behaviour like this in any other sql management tool but hey you live and you learn :)

Paul
  • 5,524
  • 1
  • 22
  • 39
1

UnCOMMITted data is only visible within the session that created it (and will ROLLBACK at the end of the session if it has not been COMMITted). If you can't see the data from another session (i.e. in C#) then make sure you have issued a COMMIT command in the SQL client where you INSERTed the data (i.e. SQL Developer).

Note: even if you connect as the same user, this will create a separate session and you will not be able to see the uncommitted data in the other session.

From the COMMIT documentation:

Until you commit a transaction:

  • You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.
  • You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK).
MT0
  • 143,790
  • 11
  • 59
  • 117