How to move from a LONG datatype in Oracle to a BLOB/CLOB?

Author: knut.dybendahl@gmail.com (Knut)

We've got two issues here;

  1. UFORM et al have been created using the uf scripts and as such, the DDL is using a CLOB in the schema. However, our ASN file have had a "u4_compatibility" setting - thus, I would presume, limiting the amount of data stored in the CLOB. I'm stating this based on the fact that 1000+ of our forms have 'spilt' over into OUFORM. How can we export from UFORM/OUFORM and ensure all the data end up in the CLOB and not "reinserted" into the OUFORM table?
  2. We have other tables where the table was created using the LONG statement, and we'd like to switch over to a BLOB/CLOB structure. Given that we can export the data from these tables - the issue we have is that there will be records in the Otablename export. How do we ensure the records from the Otablename end up in the BLOB/CLOB and not in the Otablename if we export/import the data?

Knut

8 Comments

  1. Hi Knut, We are going back to old times here...as far as I remember procedure should be the same for URepo as well as for your data: 1) Export/Copy to TRX-XML: Uniface does not export O tables separately but add extensions to main table 2) Get rid into your application models of all references to combinations going to LONG/LONG RAW substituted with combination going to CLOB/BLOB 3) Get rid in your ASN of settings maintaining compatibility with the past, like u4_compatibility 4) Compile all 5) Generate tables with new structure 6) Reimport/Copy from TRX-XML A small test should confirm it... Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  2. Hi Gianni, Appreciate it - will give it a shot a little later in the week. Knut


    Author: Knut (knut.dybendahl@gmail.com)
  3. I beg to differ.... From the ora6xdt.sql file included with 9.7.03 build; CREATE TABLE "UFORM"( "UTIMESTAMP" DATE, "UTRANSACT" CHAR(8), ..... "TPLACTUAL" CLOB, It shouldn't matter that Uniface uses variable length techniques - because those are control characters embedded in the data. As far as Oracle is concerned, a CLOB is a CLOB no matter whichever way you look at it.... In fact, going through the abovementioned sql file, there are numerous examples of CLOB and BLOB use.... For example, UGLYPH uses a BLOB, yet there is a definition for a OUGLYPH... Having said that - I'll stay away from the repository changes... Wink Knut


    Author: Knut (knut.dybendahl@gmail.com)
  4. Hi Knut The 8K maximum size per record can be avoided by using segmented fields or using only fixed length (no * in field interface) .   From the Uniface library in "Record Management on Oracle": Length of record A Uniface record (not including segmented fields) has a maximum of 8192 bytes. Note:  The maximum record size for entities that have variable length fields is 8KB, the rest goes into the overflow table. For entities with only fixed length fields the maximum record size is 2GB-1.   Yes Oracle can handle more data in CLOB  however Uniface has this historical 8K limitation for C*. The impact on customers changing this 8K behavior would be too big. Uniface needs this 8K overflow mechanism also to be database independent.   Peter


    Author: PBeugel (peter.beugel@uniface.com)
  5. Hi Peter, Thank you for clarifying. In essence - a CLOB/BLOB is not relly a CLOB/BLOB... Which then raises the question - how does Uniface deal with a real CLOB/BLOB defined by another application - and the DDL imported into Uniface? Regards, Knut


    Author: Knut (knut.dybendahl@gmail.com)
  6. Knut said ... In essence - a CLOB/BLOB is not relly a CLOB/BLOB... ... 

    Knut, I recognize saying "segmented field" and real CLOB/BLOB together sounds as a contradiction...but AFAIK: it is a real CLOB/BLOB/NCLOB if you map your unstructured field to SC*/SR*/SW* field interface (segmented field); all other combinations lead to overflow tables as Peter has explained above. Looking into ULibrary for "Oracle Mapping": - CLOB and BLOB These are used to store CLOB and BLOB data, respectively, where Uniface fields are defined with data type S and packing code SC. When you define fields with the segmented packing code SC, Uniface can store data to a maximum length of 4 gigabytes. These fields are treated like any other except that they may not be used in a condition, either explicitly, through a u_where or where Proc, or implicitly, by entering data in a CLOB or BLOB field before requesting a retrieve. - NCLOB (National Character Large Object) is an Oracle data type that can hold up to 4 GB of character data. It's similar to a CLOB, but characters are stored in a NLS or multibyte national character set. Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  7. Hello Knut   What you want is not possible. The Uniface metadefinitions use field interface definitions with Uniface specific techniques. Import umeta.xml and utempl.xml and check field definitions used for fields in UFORM in the DICT model. You'll see for example W*VI\^31^211 Even with CLOB there will be UFORM and OUFORM needed. What you want is only possible with segmented field interfaces: These are not used in the Uniface meta entities like UFORM.   Peter


    Author: PBeugel (peter.beugel@uniface.com)
  8. Yes, Peter is right! It could be done...but only for your data, not for URepo tables. Gianni


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