Category: Data Mover

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:


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';

It gave me this error:

Importing RQ_GRP_TBL
Error: Syntax error in where clause for RQ_GRP_TBL 

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


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;

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:


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;

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:


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');

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

Import/Export with Data Mover

A common development task is to move data between two different PeopleSoft environments.  You can use App Designer to move code, table structures, and pages, but data is a different story.  Data Mover is your tool for moving your data.

My biggest challenge is that I don’t use it enough to have the syntax memorized.  This post is a list of common tasks with the syntax to make it easier to remember…

Read More