Oracle 11g DB returns threads instead of rows

Question

I have a new database here, and the updated version from Oracle 10g to Oracle 11g - the main problem is the LOB columns, and every time any function returns LOB as a result, the new database will not return rows like the old one did:

Old DB:

["C"]=> string(23) "3874163,3874197,3874201" 

New DB:

 ["C"]=> resource(182) of type (stream) 

Now when reading threads, sometimes an error occurs for a non-existent resource of the referenced stream, and everything fails. I assume that the connection is closed in the meantime if the stream is not being read, and therefore access is lost.

When changing statements, enable casting against varchar, for example:

 CONVERT(VARCHAR, C, 120) 

Or like this:

 SELECT TO_CHAR(FUNC()) 

The value is returned as a string again, but this is not a very optimal solution, since each statement must be changed.

Is there a way / option to prevent the delivery of large objects to threads so that they are sent as strings, for example, in Oracle 10g?

Edit:
We use the oci feature set to access db.

Answer 1

This is actually not an answer, but a few elements that I hope will help.

It seems that there is a slight difference in the fact that LOB files are returned between 10g and 11g, under 11g there are some notes about converting from btyes to byteStreams when LOB files exceed a certain value in the JDBC reference manual (I understand that this does not affect OCI calls because they use a different set of drivers).

From what I see in terms of OCI8 functions in php, the default function for the default fetch function is that LOBs are returned as a reference and should be accessible using functions ->read() ->load() and so on. .d. (see http://au.php.net/oci_fetch_array - for mode and default).

Now I don’t know if you are using OCI functions to access your oracle system, since it is not mentioned in your question.

A couple of other elements that would help to understand this would be if you could tell us if you recompiled php or updated the oracle drivers with a newer version of the client in general.

I know this is not a complete solution, but if you use oci_fetch_* to return a string, add the second argument to the OCI_RETURN_LOBS call, this will cause fetch to return the LOB field string instead of the stream reference or use $variable["C"]->load() to access this LOB, this will force it to load the full stream and act like a regular string.

Hope this helps.

Answer 2

If you are using PDO, you may need to change from PDO :: PARAM_LOB to PDO :: PARAM_STR. For example, in combination with a binding column:

 $statement->bindColumn(1, $as_string, PDO::PARAM_STR, 256); $statement->bindColumn(1, $as_lob, PDO::PARAM_LOB); 

Answer 3

When using LOB, you must use OCI :: read or OCI :: load to get their contents.

 [...] $row = oci_fetch_assoc($result); $lobContents = $row['LOB_COLUMN']->load(); //fetches the whole LOB //or while ($buffer = $row['LOB_COLUMN']->read(4096)) { //sequential read $lobContents .= $buffer; }