Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can I create a fixed length type in oracle database?

aceincJun 19 2017 — edited Jun 20 2017

Using Oracle 11gR2, I have a need to create a fixed length string comprised of 200 + fields from a table. The resulting output string is a clob over 20,000 bytes long.

I have created a dynamic select statement that creates this by reading a table that has the relationship between the fixed length string and the database fields. I end up with something like;

select rpad(char_field1, 20,' ') || lpad(num_field1,6,'0') || rpad(' ',8,' ') AS FIXED_STRING from my_table

It works fine, but is CPU intensive with all the concatenation and padding etc. It is a bit more complex with the requisite to_clobs.

I noticed that there is the ability to create an external table of type fixed, but my data never needs to be written to disk, just passed to a program for processing.

I wondered if there was an equivalent in memory structure similar to;

TYPE MY_RECORD_TYPE IS RECORD ( CHAR_FIELD1 position(1:20) CHAR(20), NUM_FIELD2 position(21:6) CHAR(6), FILL_FIELD1 position(28,8) CHAR(8) );

That would allow me to create a string to pass to something else, in my case VB.NET?

Virtual Columns have been suggested, but my understanding is that would require the same string concatenation as well. Making it as inefficient.

My overall goal is to come up with the most efficient way of creating fixed length strings from column data in a table.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 18 2017
Added on Jun 19 2017
10 comments
797 views