Category: Jethro List

Jethro List: Data Mover Woes

Ok.  Maybe this is a rant, but Data Mover can sometimes get under my skin!  Some things you just expect to work, and when they don’t, it is very frustrating.

Here’s my problem.  I created this very, very simple data import:

[sourcecode]

set log C:\temp\aa_setup_import_FADEV.log;
set input C:\temp\aa_setup_export_AATST.dat;

replace_data RQ_GRP_SHR_SET;
delete PS_RQ_GRP_TBL where RQRMNT_USEAGE = ‘ADV’;
import RQ_GRP_TBL where RQRMNT_USEAGE = ‘ADV’;

[/sourcecode]

It gave me this error:

[sourcecode]Importing RQ_GRP_TBL
Error: Syntax error in where clause for RQ_GRP_TBL [/sourcecode]

Then, when I changed it to use parameters, it suddenly works:

[sourcecode]

set log C:\temp\aa_setup_import_FADEV.log;
set input C:\temp\aa_setup_export_AATST.dat;

replace_data RQ_GRP_SHR_SET;
delete PS_RQ_GRP_TBL where RQRMNT_USEAGE = ‘ADV’;
import RQ_GRP_TBL where RQRMNT_USEAGE = :1;CHAR,ADV;

[/sourcecode]

In my opinion, there is no call for that error.  I thought both syntaxes were supposed to work the same.  Am I missing something?  Or, is this a bug in data mover?

 

Next, I ran into a worse problem.  I couldn’t make my subquery work on the import, so I had to move it to the export script instead.  I tried this:

[sourcecode]

set log C:\temp\aa_setup_export_AATST.log;
set output C:\temp\aa_setup_export_AATST.dat;

export RQ_GRP_SHR_SET;
export RQ_GRP_TBL where RQRMNT_USEAGE = :1;CHAR,ADV;
export RQ_GRP_DETL_TBL where
EXISTS (SELECT ‘X’ FROM PS_RQ_GRP_TBL A
WHERE A.RQRMNT_GROUP = PS_RQ_GRP_DETL_TBL.RQRMNT_GROUP
AND A.RQRMNT_USEAGE = :1);CHAR,ADV;

[/sourcecode]

Every time that I tried it, it would crash Data Mover.  Data Mover closed with no error message, warning, or anything.  When I changed it to this, it worked fine:

[sourcecode]

set log C:\temp\aa_setup_export_AATST.log;
set output C:\temp\aa_setup_export_AATST.dat;

export RQ_GRP_SHR_SET;
export RQ_GRP_TBL where RQRMNT_USEAGE = :1;CHAR,ADV;
export RQ_GRP_DETL_TBL where
EXISTS (SELECT ‘X’ FROM PS_RQ_GRP_TBL A
WHERE A.RQRMNT_GROUP = PS_RQ_GRP_DETL_TBL.RQRMNT_GROUP
AND A.RQRMNT_USEAGE = ‘ADV’);

[/sourcecode]

Again, why?  I don’t care what your argument is, it is a bug when a program closes with no message.  If I did something wrong, tell me.  Don’t just close.

Resources

Oracle Forums: DATAMOVER: Error: Syntax error in where clause for PSOPRDEFN

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.

 

Using Open COBOL with PeopleSoft

COBOL in PeopleSoft has long been one of my frustrations.  I have had trouble with change assistant not getting the right version of the files in the correct directories.  The compiler is an extra set of steps for installing and getting the system working.  The license has to be installed in addition to the compiler, and most likely several run time environments.  Typically, you would have to ship the COBOL programs between servers assuming you only have one server that is licensed to compile.

I can understand paying money for a program that you use, but COBOL doesn’t fit that description in my mind.  Every client that I have worked at won’t touch a COBOL program: every customization must be outside the COBOL programs.  So, why then should I have to pay thousands of dollars for a COBOL compiler?

So, I thought I would play with an open source compiler.  I thought it would do two things:

  1. Maybe in some small way influence toward Open COBOL as an official certified alternative
  2. Help me better learn how the COBOL system works

So, here’s what I learned:

Read More

Jethro List: Disable Restart

For my first item on the Jethro List, I want to suggest changing the default on the “Disable Restart” option for an Application Engine program.  I think it should be checked by default.

Just to introduce the Jethro List: these posts are a list of good influences on the PeopleSoft world that I would like to make.  Hopefully, they are good suggestions that I would love to get noticed and implemented.  We’ll see if it goes anywhere.

So, what about the Disable Restart option?

The default right now as of PeopleTools 8.52 is that the Disable Restart is unchecked.

The problem is that the first time your new program abends or crashes, you have extra work to do.  You have to either restart the same process instance or delete the AERUNCONTROL row.  You can see the PeopleTools Tip — App Engine Restart If you get … post for more info on fixing the problem.

Why should it default to checked?  Because you have to design a program to be restartable before the user should be able to restart it.  Here are some of the changes that you need to make:

  • Control the Commits: The program will restart at the last commit.  So, you need to pay attention to where you have the program commit.  I believe that when left alone, it will only commit at the end.
  • Set the Do Select types: The default Select/Fetch will fail if the program crashes in the middle of the selection.  You need to choose either Restartable or Reselect.
  • Track the Progress: The program needs to save which row it is processing so that it can restart at the same place.  You either need to add a processed flag to each row, or save the last row processed where you can process only rows greater.

So, that’s why I wish I could change this default option.