clob and overlay tables nightmare

Author: yves.de.montmollin@aonhewitt.com (yves2m)

 Hello Folks,   I've a table with an XML file in a BLOB field.  (I already had to ask to change the definition of the table to put the BLOB as last field to respect Uniface obligation to have the LONG/BLOB in last column) When I read (retrieve) the table, I receive a -38 error, because the column has been truncated. Yes, the file is more than 6000 chars. And as far as I've tested, the limit of 6000 is where Uniface generates an overlay table to put the rest of the data... I think I can do a view and read all the other columns but the BLOB.  And use a PL-SQL to read the BLOB ... But when I have to change something in the XML, it's again a PL-SQL to put back the data in the BLOB... My question ... How can we stop Uniface using overlay tables. Because a BLOB/CLOB in Oracle is NOT limited. So why Uniface is limiting this.  This table is shared by different programs, and only Uniface have this limitation ...   So if anyone knows a parameter to tell Uniface NOT using overlay tables  Thanks in advance   Yves

8 Comments

  1. In MSS, defining the field as SC* will switch from overlay tables to 'text' (clob), which (it appears) actually maps okay to varchar(max).  Try adding S to the front of the current field interface definition and generate the sql to see what data type it maps to.  According to the Oracle data packing page in the help, SC1-SCn, SC*, SU1-SUn an SU* all map to CLOB. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  2. Hi Yves, Uniface does NOT have the limitation you talk about since a loooooong time... You mentioned PL-SQL so I suppose you are working with Oracle. Open your documentation (CHM) file coming with Uniface and search the word "Oracle"... In the first ten positions you should find a "Oracle data packing" item...open it! You will have in front of you the mapping between Uniface packing codes and Oracle datatypes... I think you will find easily the right combination for your case! Best Regards, Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  3. Iain Sharp said In MSS, defining the field as SC* will switch from overlay tables to 'text' (clob), which (it appears) actually maps okay to varchar(max).  Try adding S to the front of the current field interface definition and generate the sql to see what data type it maps to.  According to the Oracle data packing page in the help, SC1-SCn, SC*, SU1-SUn an SU* all map to CLOB.   

    Hi Iain and Gianni Thank for your answers. I had already checked in the documentation. But when you don't know the answer, it's not clear that the difference with the S or not the S makes the point on generating the table.  So now that thanks to Iain I know what to check, effectively did I find in the doc an explanation.   It will be helpfull in many situations. Thanks and regards   Yves


    Author: yves2m (yves.de.montmollin@aonhewitt.com)
  4. Hello Yves, this topic seems to be duplicate of your previous topic here? xmlStream to blob I don't want to duplicate my answer, but the answer is still the same as in the other topic - please read it here. As a summary: We use CLOB and BLOB fields in Uniface + Oracle with no problem. We got rid of those "stupid" overflow tables many years ago. For a BLOB field use Data Type "Raw data" and interface "SR*". And in your .asn file in the [DRIVER_SETTINGS] section set USYS$ORA_PARAMS = open cursors 200, nclob_ext. This works for us, so it should work for you as well. PS: we do our best to keep those LOB fields at the end, but it works even in the middle of the table. Kind regards, Zdenek Socha


    Author: sochaz (zdenek.socha@fullsys.cz)
  5. Also, for the relational database drivers, it doesn't (really) matter if the fields in the table are in a different order from those in Uniface. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  6. Iain Sharp said Also, for the relational database drivers, it doesn't (really) matter if the fields in the table are in a different order from those in Uniface.   

    Well, this is not true. We had (and still have now and then) serious problems with this. If the order of fields in DB and in Uniface does not match, the entity just does not work and Uniface throws errors. But it's very tricky... sometimes it works fine, sometimes just does not. According to our tests, it works as long as order of fields in the model in Uniface is the same as order of parameters in the package. So, if you generate a new package, it will stop working. Since tables with a LOB field does not have package, it might work for you. But I strongly recommend you to have exactly the same order in both, Uniface and DB. Just my 2 cents. :P PS currently on Uniface 9.6 and Oracle 11.2.


    Author: sochaz (zdenek.socha@fullsys.cz)
  7. Ah sorry, yes, I do not have experience of using the stored procedures method of storage. Our app updates directly without the stored procedures, using MSSQL, we have no issues with field order. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  8. Hi all, could it be the difference is that lain is using interactive SQL while sochaz is accessing DB using generated packages? Other 2 cents on the table... :-) Gianni P.S. Sorry for the duplication...I had written my post after sochaz's one while for reason I do not know went published after lain's answer...


    Author: gianni (gianni.sandigliano@unifacesolutions.com)