Data Types

[CassidyHunt]

I have a need to be able to handle a varchar2 greater than 4000 characters. I haven't been able to figure out another way to do this other than producing XML. When I load the XML as a varchar2 I get an error as soon as I go over 4000 characters.

Job Number:  Q05458

Sales Line No.    SupplyID No.    Cost
1                 S36898          $200.00
                  S36899          $400.00
                  S36900          $100.00
2                 I05455          $1000.00
3                 Q00545          $15000.00
-------------------------------------------
Total                             $16700.00

Sales Line 1
Supply ID No. S36898  Lot ID 1  Sub ID 0

Operation    Resource   Setup Rate  Run Rate
10           01Weld     $72.00      $72.00

     Materials
     Part ID      Qty       Cost
     LHMS1000     10        $100.00
     AC1X54.2      2        $5.00

Services

Operation    Service ID  Service Base Chg  Service Run
20           WeldInsp    $50.00            $350.00


Sales Line 1
Supply ID No. S36899  Lot ID 1  Sub ID 0

...
Repeats above until all line items are detailed out for operations, materials, and services.

The ultimate solution for me would be a single nested resultset that I could return. Next best thing would be XML. I haven't had luck producing either.

Thank you

Cassidy

 

[SantaMufasa]

Cassidy,

I thought that you had Oracle 9i. If you have, then, by far, your best solution is CLOB. In Oracle 9i, CLOBs behave identically to VARCHAR2, and have max size of 2GB.

In the absence of Oracle 9i, Oracle presents an excellent reference manual on how to handle objects exceeding 4000 characters at Oracle8i Application Developer's Guide - Large Objects (LOBs). This material is inside of Oracle Technical Network (otn.oracle.com), but you probably already have a unsername and password for OTN; if not, it is easy to register.

Let us know of your findings there that specifically contribute to your resolution.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[CassidyHunt]

I only have 8i right now. Thanks for the link. I will take a look at it and get back.

 

[dspare]

If you are at 8.1.7.4 then you also have access to the CLOB datatype. Your only other choice at that point would be a LONG but handling text in a LONG is not simple and should be avoided if possible. The CLOB type has a lot more package support for searching and manipulating the text. Also look into the CONTEXT (is it still called that??). I know it is available for 8i.

 

[SantaMufasa]

dspare...all excellent points.

ConTEXT is Oracle's name for that product...two names ago. Following ConTEXT Oracle called the next incarnation, "InterMedia"; now they call it "Oracle Text".

So, Cassidy, as dspare suggests, "Oracle Text" is your method for accessing long objects residing in an Oracle 8i database.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.