Here is a great tool for formating SQL:
Tag: Post Needs to Move
These posts still need to move from Wordpress.com
Blog Update: Updated Resources
Just a quick update: I updated the PeopleBooks Resources links with some new links and removed the old broken ones. Hope that helps.
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:
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 = '<file Layout Name>'
) B
WHERE B.CORRECT_SEQ <> B.ACTUAL_SEQ
ORDER BY B.FLDSTART
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.
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 = '<file Layout Name>'
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;
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.
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 = '<file Layout Name>'
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;
Added Filelayout Tables to Tables Page
Just a quick update: I added the File Layout Definition tables to the PeopleTools Tables page. Hope it helps.
I had used one of the tables on this post, but I never added the table to the reference page.
PeopleTools Tip: PeopleCode Dump
Application Designer has an option to search through the text of the code, but it takes way too long to search the whole system on a regular basis. Thankfully, Application Designer also has a way to dump all of the code to a text file.
From Application Designer, choose Edit > Find In…
Next, enter a semicolon(;) for the search text and check the option “Save PeopleCode to File”
Important: This will take a long time and a lot of memory to finish. So, increase your virtual memory and let it run overnight. If you have a team of developers, have one person do the dump and put it on a network location where everyone can view it.
If you don’t enough memory, you may get an error message like this:
If so, go to the control panel and open the System icon. Go to the Advanced tab and click the Settings button under performance. Under the “Advanced” tab in the performance options dialog, change the virtual memory. The steps to increase the memory might be slightly different depending on your flavor of Windows.
PeopleCode Decoder
Ok, I have seen this several places, but I have link it here too: A java program has been written that will read PeopleCode from a PeopleSoft database.
Here are the blogs where I have seen this
PeopleSoft/Oracle Tips (describes how to compile it)
PeopleSoft Pros (I think this is where I first saw it)
It looks like the program was created with information from PeopleCode Secrets.
I would love to create a project on SourceForge for this code. There are so many things that you could do with it. I would love to index the PeopleCode in the system to make searching and refactoring easier. Oh well, maybe one day.
Application Designer Automation
In going through the registry, I found a reference to “PeopleSoft.ApplicationDesigner”. But, I cannot find any documention on how to use it.
I was able to create an object in WScript:
var obj = new ActiveXObject("PeopleSoft.ApplicationDesigner");
But, I cannot figure out what methods to use with the obj variable.
I posted a question on Oracle Mix, but no one has answered, yet. Jim Marion has pointed out Grey Sparling Solutions, but I haven’t found any information there. I really like some of the solutions they have created — they are very innovative.
Please let me know if you have any ideas on how to get more information.
ORA-01502: Indexes in unusable state
I kept getting error messages like this:
ORA-01502: index 'SYSADM.PS_PSAESTMTDEFN' or partition of such index is in unusable state
I found the answer to the problem here:
ORA-01502 Oracle Index in Unusable State
As Katie mentioned in the comments, the status in 10g is UNUSABLE instead of INVALID.
Here is a quick script to rebuild all of the problem indexes:
declare
begin
for index_rec in (select owner, index_name
from dba_INDEXES
where status = 'UNUSABLE')
loop
execute immediate 'alter index ' || index_rec.owner || '.' ||
index_rec.index_name || ' rebuild';
end loop;
end;
Move a Tablespace
This may be pointless, but maybe some of the SQL may help someone. It is an attempt at boiling all the steps from another post into one SQL script. The rename at the end didn’t seem to work for some reason, and I didn’t want to put any more time into it.
declare
tblName varchar(25);
tempName varchar(25);
segmentCount number(10);
osOutput number(3);
begin
tblName := 'AAAPP';
dbms_output.put_line('Moving tablespace: ' || tblName);
tempName := tblName || 'NEW';
dbms_output.put_line('Using temporary tablespace: ' || tempName);
select count(*) into segmentCount from dba_segments
where tablespace_name = tblName;
dbms_output.put_line('Currently ' || segmentCount || ' segments');
execute immediate 'create tablespace ' || tempName ||
' datafile ''e:\oradata\HCM90\' || tempName || '.DBF''' ||
' size 10m ' ||
' extent management local autoallocate ' ||
' segment space management auto ';
execute immediate 'ALTER DATABASE DATAFILE ''e:\oradata\HCM90\' ||
tempName || '.DBF'' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED';
for index_rec in (select owner, segment_name
from dba_segments
where segment_type = 'TABLE'
and tablespace_name = tblName)
loop
execute immediate 'alter table ' || index_rec.owner || '.' ||
index_rec.segment_name || ' move tablespace ' || tempName;
end loop;
for index_rec in (select owner, table_name, column_name
from dba_lobs
where tablespace_name = tblName)
loop
execute immediate 'alter table ' || index_rec.owner || '.' ||
index_rec.table_name || ' move lob(' || index_rec.column_name ||
') store as (tablespace ' || tempName || ')';
end loop;
select count(*) into segmentCount from dba_segments
where tablespace_name = tblName;
dbms_output.put_line('Now ' || segmentCount || ' segments');
execute immediate 'alter tablespace ' || tblName || ' offline';
execute immediate 'drop tablespace ' || tblName;
execute immediate 'alter tablespace ' || tempName || ' rename to ' || tblName;
dbms_output.put_line('Renaming ' || tempName || ' to ' || tblName);
dbms_pipe.pack_message('cmd /c del e:\oradata\HCM90\' || tblName || '.DBF');
osOutput := dbms_pipe.send_message('HOST_PIPE');
dbms_output.put_line('Deleted e:\oradata\HCM90\' || tblName || '.DBF -- ' || osOutput);
end;