How to move from a LONG datatype in Oracle to a BLOB/CLOB? | Uniface 9 | Forum

Avatar

Please consider registering
guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

—  Results per page  —








— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters
For a group of consecutive words like 'end of support' use Match phrase

sp_Feed Topic RSS sp_TopicIcon
How to move from a LONG datatype in Oracle to a BLOB/CLOB?
20 Feb 2017
10:32 pm
Avatar
Knut
Member
Forum Posts: 154
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

20 Feb 2017
11:27 pm
Avatar
gianni
Member
Forum Posts: 203
Member Since:
01 Oct 2012
sp_UserOnlineSmall Online

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

21 Feb 2017
2:11 pm
Avatar
Knut
Member
Forum Posts: 154
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

Hi Gianni,

Appreciate it – will give it a shot a little later in the week.

Knut

14 Mar 2017
4:59 pm
Avatar
PBeugel
Member
Forum Posts: 19
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

Kind regards Peter Beugel Sr. Technical Support Analyst Uniface B.V.
15 Mar 2017
7:08 am
Avatar
gianni
Member
Forum Posts: 203
Member Since:
01 Oct 2012
sp_UserOnlineSmall Online

Yes, Peter is right!
It could be done…but only for your data, not for URepo tables.

Gianni

15 Mar 2017
1:52 pm
Avatar
Knut
Member
Forum Posts: 154
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

17 Mar 2017
3:38 pm
Avatar
PBeugel
Member
Forum Posts: 19
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

Kind regards Peter Beugel Sr. Technical Support Analyst Uniface B.V.
22 Mar 2017
12:40 am
Avatar
Knut
Member
Forum Posts: 154
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

22 Mar 2017
8:21 am
Avatar
gianni
Member
Forum Posts: 203
Member Since:
01 Oct 2012
sp_UserOnlineSmall Online

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

Forum Timezone: Europe/Amsterdam

Most Users Ever Online: 131

Currently Online: gianni
16 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

ulrich-merkel: 1648

Iain Sharp: 566

Theo Neeskens: 323

rogerw: 212

gianni: 203

lalitpct: 194

istiller: 175

-GHAN-: 171

sochaz: 165

Knut: 154

Member Stats:

Guest Posters: 2

Members: 3029

Moderators: 0

Admins: 8

Forum Stats:

Groups: 1

Forums: 62

Topics: 1876

Posts: 8123

Newest Members:

MichaelEcornads, Evgeniywab, Kevin Tai, JerryCoN, aapedtup, MorrisWep, AndreyHoato, RaghdaMorsy, DemuRAviK, Maluvat

Administrators: admin: 23, Adrian Gosbell: 258, diseli: 683, Bob Maier: 2, Nico Peereboom: 54, Michael Rabone: 4, richiet: 406, JanCees: 23