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.