Category: File Layouts

Jethro List: Ctrl + A

This post  is part of my Jethro List.  You can read more about the list on the Jethro List page.

Can I make another suggestion?  Some of the text boxes in App Designer don’t work like regular Windows controls.  Namely, they don’t support things like Control-A to select the entire text in the box.  This is slightly frustrating and also makes App Designer feel antiquated.

One of those places is the File Layout Field Properties dialog.  If you go to the “Field Name” text box, you can press Ctrl+A to select the entire field name easier.  That makes it easy to either copy the name or type a new name in it’s place.

File Layout Field Name

 

If you go to the Field Description box in the lower right hand corner, you will find that Ctrl+A doesn’t work.  You have to either use the mouse or hit Home, Shift + Ctrl + End.

File Layout Field Description

 

On a rabbit trail, the file layout UI could use some attention.  A feature that I would like for Flat Files is the ability to sort by the Start Position.  If you come behind someone who wasn’t as organized and didn’t put the fields in order, it can be annoying.  Also, file layouts seem to have issues with caching.  I have had many a time that I changed a file layout and saved only to reopen it and find my changes missing.

The Application Engine UI has a similar problem.  The descriptions for the sections, steps, and actions all don’t allow the use of Ctrl + A.

App Engine description fields

In conclusion, I think it would be helpful if Oracle could update the App Designer UI to match the standard features that we have come to expect in Windows applications.  I can live with it this way and can get my job done, but it sure would be nice to have a nicer feel to development.

 

Updated: File Layout SQL

My post yesterday was a little wrong. Here is a better SQL to help with the sequence of the fields in the layout:

[sourcecode language=’sql’]SELECT * FROM (

SELECT A.FLDFIELDNAME, A.FLDSTART,

(SELECT COUNT(*) FROM PSFLDFIELDDEFN

WHERE FLDDEFNNAME = A.FLDDEFNNAME

AND FLDSTART < A.FLDSTART) + 1 CORRECT_SEQ, A.FLDSEQNO ACTUAL_SEQ FROM PSFLDFIELDDEFN A WHERE A.FLDDEFNNAME = '

) B

WHERE B.CORRECT_SEQ <> B.ACTUAL_SEQ

ORDER BY B.FLDSTART[/sourcecode]
I had trouble rearranging the fields though. You can drag and drop the fields, but when you reopen the file layout, you find that it didn’t actually adjust the field sequence (FLDSEQNO). So, the fields are back in the order they were in. This is true for tools 8.46; I didn’t test 8.49.The only way I could find to rearrange the fields was to delete the field from the layout. Then, click on the field that you wanted it after and re-insert it.

SQL for Maintaining File Layouts

Here are a couple of SQL statements that might help you maintain file layouts.

This SQL shows gaps in a flat file. It assumes that the next field should start at the previous field’s start + the previous field’s length. Make sure to substitute the file layout name.

[sourcecode language=’sql’]SELECT A.FLDFIELDNAME, A.FLDSTART, A.FLDLENGTH, A.FLDSTART + A.FLDLENGTH SHOULD_START_POS,

B.FLDFIELDNAME NEXT_FIELD, B.FLDSTART ACTUAL_START_POS

FROM PSFLDFIELDDEFN A, PSFLDFIELDDEFN B

WHERE A.FLDDEFNNAME = ‘

AND A.FLDDEFNNAME = B.FLDDEFNNAME

AND B.FLDSTART = (SELECT MIN(FLDSTART) FROM PSFLDFIELDDEFN

WHERE FLDDEFNNAME = A.FLDDEFNNAME

AND FLDSTART > A.FLDSTART)

AND A.FLDSTART + A.FLDLENGTH <> B.FLDSTART

ORDER BY FLDSTART;

[/sourcecode]
This SQL shows if the fields are out of order. If the order in the file layout designer is different from the order based on the field start position, the layout will be a little confusing and hard to maintain. This should help you correct the order. To re-order the fields in App Designer, drag the field on top of the field that you want it to come after.
[sourcecode language=’sql’]SELECT A.FLDFIELDNAME, A.FLDSTART, A.FLDLENGTH, A.FLDSEQNO, A.FLDSEQNO + 1 SEQ_SHOULD_BE,

B.FLDFIELDNAME NEXT_FIELD, B.FLDSTART NEXT_START_POS, B.FLDSEQNO ACTUAL_SEQ,

C.FLDFIELDNAME ACTUAL_NEXT_FIELD

FROM PSFLDFIELDDEFN A, PSFLDFIELDDEFN B, PSFLDFIELDDEFN C

WHERE A.FLDDEFNNAME = ‘
AND A.FLDDEFNNAME = B.FLDDEFNNAME

AND A.FLDDEFNNAME = C.FLDDEFNNAME

AND B.FLDSTART = (SELECT MIN(FLDSTART) FROM PSFLDFIELDDEFN

WHERE FLDDEFNNAME = A.FLDDEFNNAME

AND FLDSTART > A.FLDSTART)

AND C.FLDSEQNO = A.FLDSEQNO + 1

AND A.FLDSEQNO + 1 <> B.FLDSEQNO

ORDER BY FLDSTART;

[/sourcecode]

File Layout Order

One problem with the file layout editor in Application Designer is that you cannot see the fields in order in which they appear in the file. For XML files, this does not matter, but for CSV files or flat files, the order is very important, especially in troubleshooting.

Here is some SQL that will show you the fields in the correct order (Just replace the <File Layout Here>):

[sourcecode language=’sql’]SELECT * FROM PSFLDFIELDDEFN
WHERE FLDDEFNNAME = ‘
ORDER BY FLDSTART[/sourcecode]