Saturday, 26 July 2008
Oracle Apex Password Reset
Wednesday, 23 July 2008
Handy values for Oracle Date Functions
One way to test the use of the above:
SQL>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL>SELECT SYSDATE FROM DUAL;
SQL>SELECT SYSDATE+1/86400 FROM DUAL;
SQL>SELECT SYSDATE+1/1440 FROM DUAL;
SQL>SELECT SYSDATE+1/24 FROM DUAL;
Wednesday, 16 July 2008
Spending Money
It's rare though to find them being produced for general circulation. Some nice examples are the Bradman 20 cent coin from Australia and the 50 cent Jonty Rhodes coin from South Africa. Also a 10 pound note from the Bank of England showing a cricket scene from Dicken's Pickwick Papers is pretty cool.
In my collection so far I have the following coins/medals Coins 'n More. Some I'm still trying to get hold of are World Cup commemoratives from Sri Lanka and India.
Interesting Memorabilia
PeopleSoft Status Lookups
Run Status:
Distribution Status:SELECT XLATSHORTNAME , FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME='RUNSTATUS' ORDER BY TO_CHAR(FIELDVALUE,'00');
Output Destination Type:SELECT XLATSHORTNAME , FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME='DISTSTATUS' ORDER BY TO_CHAR(FIELDVALUE,'00');
Output Destination Format:SELECT XLATLONGNAME , FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME='OUTDESTTYPE' ORDER BY TO_CHAR(FIELDVALUE,'00');
SELECT XLATLONGNAME , FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME='OUTDESTFORMAT' ORDER BY TO_CHAR(FIELDVALUE,'00');
PeopleSoft BBL Rogues
To check on a unix server if a suspect pid is registering on the PeopleSoft Bulletin Board, run the following command:
tmadmin < bbi.in | grep {pid}
If the pid is not known to the BBL, you can use the unix kill command to get rid of it, or through tmadmin utility run bbclean.
Tuesday, 15 July 2008
Checking for Overdue Jobs in PeopleSoft
SELECT CASE WHEN sdate > 0 THEN to_char(trunc(sysdate)+sdate,'hh24:MI:SS') ELSE '--:--:--' END OVERDUE , PRCSINSTANCE , PRCSTYPE , PRCSNAME , SERVERNAMERQST , SERVERNAMERUN , OPRID , RUNDTTM , XLATSHORTNAME FROM ( SELECT SYSDATE - A.RUNDTTM sdate , A.PRCSINSTANCE , A.PRCSTYPE , A.PRCSNAME , A.SERVERNAMERQST , A.SERVERNAMERUN , A.OPRID , A.RUNDTTM , X.XLATSHORTNAME FROM PSPRCSRQST A , PSOPRDEFN B , PSPRCSPRFL C , PSXLATITEM X WHERE B.OPRID = A.OPRID AND B.PRCSPRFLCLS = C.CLASSID AND A.RUNSTATUS = X.FIELDVALUE AND A.RUNSTATUS = 5 AND X.FIELDNAME = 'RUNSTATUS' AND A.RUNDTTM < SYSDATE + 60/1440 order by A.RUNDTTM);
Monday, 14 July 2008
Oracle Database Cloning
PeopleSoft Jobs Stuck in Posting Mode
The secret is to check the PS_CDM_TRANSFER and PS_CDM_LIST tables. This is where the jobs for posting are handed over. The following query will reveal what is stuck.
From the above SQL we can try and narrow down to a particular process which may be having trouble posting, by checking the distribution agent log and the webserver log of the report repository.select b.prcsinstance , b.prcsname , b.prcstype , c.xlatshortname , a.transferinstance , a.servernamerun , a.transferstatus , a.nexttransferdttm , a.transferattemptcnt from ps_cdm_transfer a , ps_cdm_list b , psxlatitem c where a.transferinstance = b.transferinstance and b.diststatus = c.fieldvalue and c.fieldname='DISTSTATUS';
A sledgehammer approach could be to clear out the transfer table and place all processes with a distribution status of "Posting" to "Not Posted". This enables the option to Re-send Content the process via the Process Monitor. The SQLs to achieve this may look like:
The Re-send Content task can also be done via PL/SQL, but is not recommended as it allocates a single TRANSFERINSTANCE number to all of the stuck processes, which I don't think is an ideal situation. It may make further troubleshooting difficult and could possibly cause additional load on the distribution agent as it may submit the whole lot of the processes as one large job?? In any event, it's interesting to see the script you could use in conjunction with the above steps:update psprcsrqst set diststatus=4 where diststatus=7; update psprcsque set diststatus=4 where diststatus=7; truncate table ps_cdm_transfer;
Another basic place to check is the Distribution tab under the Servers Definition page. Often the transfer method e.g. HTTP is overlooked there and this could cause the distribution to fail even if the Report Node is configured correctly.declare cursor prcs is select prcsinstance from psprcsrqst where diststatus=4; -- Not Posted begin for i in prcs loop update psprcsrqst set diststatus=7 where prcsinstance=i.prcsinstance; update psprcsque set diststatus=7 where prcsinstance=i.prcsinstance; update ps_cdm_list set diststatus=8, transferinstance=0 where prcsinstance=i.prcsinstance; commit; end loop; end; /
Saturday, 12 July 2008
Troubleshoot PeopleSoft Integration Broker
Well, I think I've simply been over-thinking the process and hence have over-complicated the task too much. The last couple of occasions I was able to fix things quite nicely and did so by following some very simple steps:
Step 1. Check the integrationGateway.properties file which lives on the webserver. Use Beyond Compare or some other such program to compare differences with an environment which is working. Make sure all entries are correct, especially the various gateways are set correctly.
If any changes are made, load the integrationGateway.properties file up through the PeopleSoft gateway page. Make sure to click "Apply" to Refresh this file.
Step 2. Check the gateway URL on the PeopleSoft gateways page.
Step 3. Check the PSMSGNODEDEFN table entries. Pay particular attention to the IBPASSWORD values to ensure they are correct. This can be tricky as they are encrypted, but have to make sure the password here is identical to the password on the external system.
Step 4. Perform ping tests via the Monitor Message pages.
These four simple steps seem to address majority of all issues.
Oracle Autonomous Transactions
The task at hand was to create a web-based stored procedure which would enable people from our Security team to add database users themselves, rather than forward on calls to the ever-busy DBAs.
So I started out by creating a mod/plsql stored procedure on an admin database, which would use database links to connect to the target database where the account needs to be created.
Problem Encountered:
Oracle does not allow DDL commands to run across a dblink.
Solution
Use the dblink to execute another stored procedure on the target, which would run the DDL commands.
Pretty straight forward at this point. Then comes the kicker ...
I needed to add an OUT parameter to the target procedure, so I could pass a message back to the calling source procedure advising the success/failure of the procedure at the target.
Problem Encountered:
Oracle Bug: where a combination of dblink, OUT parameter and DDLs are NOT allowed. According to MetaLink this would be rectified in version 11, but was hardly of any use to me at this point.
Solution:
Autonomous Transactions. Stumbled upon the use of Autonomous Transactions, which as I understand them, allow procedures to execute autonomously from the calling procedure. By running something like:
execute immediate 'DECLARE PRAGMA AUTONOMOUS TRANSACTION BEGIN
I was able to have my cake and eat it too!
I Luv Cricket ..... but .....
I was contemplating doing myself a favour and deleting the blog. Then I thought, I may as well keep it going (boo, hiss, bad decision) and just use it for a variety of purposes. So starting today Nazim's Cricket Blog will contain 90% less cricket!