Today, I ran into an error importing a PeopleTools tree into an instance of PeopleSoft. Probably, the problem was that I was using an old version of PeopleTools (8.46).
If this were a production system, the fix would be to upgrade PeopleTools to 8.51. But, this is for testing purposes, and I kind of want to keep the old version so that I can make sure things run with it.
Here is the error message that I received from Data Mover:
SQL error. Stmt #: 0 Error Position: 0 Return: 1400 - ORA-01400: cannot insert NULL into ("SYSADM"."PSTREEDEFN"."PT_ALLVALUEAUDOPT")
With all that said, you probably don’t want to follow these directions. (This is a don’t try at home moment.) I don’t even know why I am posting this. Maybe it will help some one with SQL syntax or something. Most likely, I will need it again for a later update into this same instance.
First, I moved my PSTREEDEFN table off to a different name:
ALTER TABLE PSTREEDEFN RENAME TO PSTREEDEFN_ORIG; [\sourcecode] Then, I created a copy of the original table just so that I could force the data mover script to run without breaking the original table: CREATE TABLE PSTREEDEFN AS SELECT * FROM PSTREEDEFN_ORIG; [\sourcecode] Next, I dropped the offending field from the table: ALTER TABLE PSTREEDEFN DROP COLUMN PT_ALLVALUEAUDOPT; [\sourcecode] Then, I ran the data mover script, which this time ran to success because the field was not there. Now, it is time to see what changed. At first, I tried to find new rows: SELECT * FROM PSTREEDEFN A WHERE NOT EXISTS (SELECT 'X' FROM PSTREEDEFN_ORIG B WHERE A.SETID = B.SETID AND A.SETCNTRLVALUE = B.SETCNTRLVALUE AND A.TREE_NAME = B.TREE_NAME AND A.EFFDT = B.EFFDT); [\sourcecode] When that came back with no new rows, I opened the DMS data file and determined which tree row changed. And then, I could compare the two tables with this SQL: SELECT 'N', A.*, ' ' FROM PSTREEDEFN A WHERE TREE_NAME = 'QUERY_TREE_HR' UNION SELECT 'Y', A.* FROM PSTREEDEFN_ORIG A WHERE TREE_NAME = 'QUERY_TREE_HR'; [\sourcecode] When I noticed that the Node_Count and the LastUpdDttm where the only two fields, I just simply updated the original table with the new values: UPDATE PSTREEDEFN_ORIG SET NODE_COUNT = 2030, LASTUPDDTTM = ( SELECT MAX(LASTUPDDTTM) FROM PSTREEDEFN A WHERE TREE_NAME = 'QUERY_TREE_HR' ) WHERE TREE_NAME = 'QUERY_TREE_HR'; [\sourcecode] Finally, we drop our copy of the table: DROP TABLE PSTREEDEFN; [\sourcecode] And, rename the table back: ALTER TABLE PSTREEDEFN_ORIG RENAME TO PSTREEDEFN; [\sourcecode] That may not be the best way to get the job done, but it got my problem fixed! Update: It happened again before I could publish this post. The second time, I just altered the table to add a default to the field. This way, the DMS script didn't have to know about the field, but the field didn't have to be removed from the table: ALTER TABLE PSTREEDEFN MODIFY PT_ALLVALUEAUDOPT DEFAULT 'D';
Resources
SQL Table Rename
Java2s.com: Changing the Default Value of a Column