I had a request the other day to strip out invalid characters from a series of strings.  The easiest way you can do this is to put a list of invalid characters into a string and then use the Translate function to set those characters to null.

In my code I have a Defined variable called null which = ”


Let $invalid_characters = '!!@#$%^&*()-_+=[]|\:;",.<>?''' || chr(123) || chr(125)
Let $newstring = Translate($oldstring,$invalid_characters,{null})


You will notice that there are two exclamation points in the invalid character string, and this is because one exclamation by itself denotes a comment. As well there is three end quotes which allows for a single quote to be put into the string and then end the string. The Chr function allows you to enter in a ascii specific character, which in this case 123 = { and 125 = } — If you try to put the curly bracket into the string it will try to look for a defined variable to insert and it will generate an error. You can use the ascii value to control other characters as well, here is a lookup table to reference from lookuptables.com

Ascii lookup table

This little SQL is a sweet little piece of code I found somewhere in my travels.  It will find duplicate rows of data based on a keyset, however, this only works in Oracle.  I will try to find a similar piece for SQL Server, but for now here we go:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

ORA-00257: archiver error. Connect internal only, until freed.

If you are loading large volumes of data and you have archive logging turned on sometimes you will run into the ORA-00257 error.  This error occurs when the upper limit of the archive log dedicated space is hit.  You need to clean up some/all of the logs in order to set the database free to work again.

A relatively straightforward way to do this is to use the rman tool:

. oraenv (set your sid/oracle_home)
rman target /
crosscheck archivelog all;  (you should see all the logs)
delete archivelog all; (removes all the logs)
crosscheck archivelog all; (you should see no logs)
exit;

Now you should be able to access the database again without the ORA-00257 error.  Sometimes it maybe necessary to turn the archive logging off during these large loads, see the post on turning off archive logging.

Note: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common error you will encounter with Archive Logging turned on is the ORA-00257 error.  It should also be noted that absolutely nothing will process within the system until the archive logs are freed.  This can be a serious issue to an environment when doing maintenance, upgrades, or large data loads.  In those scenarios it is recommended to make backups of the environment and turn archive logging off for the duration of the high traffic work, then again backup and turn archive logging back on if needed.

This is just one of those things that drive a person nuts. Depending on how your environment is configured in UNIX, when you hit the backspace key you will get a ^H key instead of removing the character you wanted to delete.

A quick fix is to issue the following command:

stty erase ^H

I believe with release of PeopleTools 8.44, they introduced a new feature that requires a user id for internal web server communication to load web profile information from the database and also performance agent information.

By default PeopleSoft delivered the product with the default userid PTWEBSERVER.  Which uses a default delivered role of “PeopleTools Web Server”, which links to the delivered permission list “PTPT1500”.

When you build your PIA (PeopleSoft Internet Architecture) you will be asked to enter a userid and password for this internal web server communication.  If the information is incorrect you will often see the error: “CHECK APPSERVER LOGS. THE SITE BOOTED WITH INTERNAL DEFAULT SETTINGS, BECAUSE OF: bea.jolt.ApplicationException: TPESVCFAIL – application level service failure”.

This user information is stored in the configuration.properties file, which is located in the:

<webserv root>/applications/peoplesoft/PORTAL.war/WEB-INF/psftdocs/<piasite>/ folder (for weblogic)

You will notice that the userid/password are encrypted.  This encryption is done using the PSCipher utility that is delivered with the pia.  I haven’t verified this but in past releases you could enter the userid and password unencrypted and it worked fine, however, I always encrypt them now, so I have tested this against 8.5x.  It should also be noted that the userid and password are both case-sensitive.

To encrypt the password use the PSCipher script located at <webserv root>/bin. PeopleTools 8.51 moved utility files to the folder <webserv root>/piabin. I usually run the setENV first (however, it usually does it for you now) and then run PSCipher <newpasstoencrypt>.  It will return an Encrypted password in the form:   {V1.1}xxxxxxxxxxx= copy this password and place it into the configuration.properties file.

If you are still experiencing a problem, make sure that the user you are trying to connect with has the correct password entered into the user security screens, and that the account is unlocked.

With the release of the 8.51 Tools they also introduced a password for the domain.  You set this password in the configuration of the application server, and you have to enter it for things like integration broker and also in the PIA web configuration.  This password is encrypted the same way in the configuration.properties file.

Happy Configuring!