Note: Technical Talk being moved to:

Friday, 22 August 2008

Oracle Apex Installation

Steps for Installation of Application Express using Oracle 10g Express Edition Database and Apex 3.1.1 on Windows XP - SP2.
Pre-requisites ....

  • Download Oracle 10g Express Database for Windows (OracleXE.exe) from Oracle's website.

  • Download Oracle Application Express for Windows ( from Oracle's website. Install the Database ....

  • Install the Oracle Database by double clicking on the OracleXE.exe.

  • Accept the License Agreement.

  • Define the Installation Directory. The default is c:\oraclexe

  • Set a System password. In my case I've used oracle as the password.

  • Review the summary information. The destination folder is c:\oraclexe, port for the database listener is 1521 and port for the http listener is 8080. Click to continue the installation process.

  • Click Finish at the end of the installation process. Configure a Database user for installation and administration of Apex. This user will require sysdba privileges ....

  • Go to the database Home Page All Programs > Oracle Database 10g Express Edition - Go to Database Homepage (Note: This is started up if the option is checked at the end of the database installation).

  • At the database login page enter Username as sys and password as set earlier oracle.

  • After successful login, select the Administration link, then select the Database Users link. Click on the Create button.

  • On the Create User page specify the username e.g. admin and password e.g. oracle. Confirm the password oracle.

  • The default tablespace is USERS and temporary tablespace will be TEMP. These need not be changed.

  • Specify the User Privilege Roles to be Connect, Resource, DBA.

  • Select the check all button for the directly granted system privileges section. Install Application Express ....

  • Extract the file to a desired location for the Apex Software. In my case, I have chosen to keep it with the Database Home. Therefore my extract is to c:\oraclexe\. The extract will create a folder/directory called apex with a number of sub-directories under it.

  • Start a DOS session Start > Run > cmd and change directory to the Apex home (cd c:\oraclexe\apex).

  • Invoke sqlplus from the DOS prompt and login as the Database user created in the previous step i.e. sqlplus admin/oracle as sysdba .

  • This will take you to a sqlplus prompt SQL>. At the prompt, type in command SQL>SHOW PARAMETER SHARED_POOL_SIZE. Set the shared pool size to 100MB using the command SQL>ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=BOTH;.

  • Check if the value was set correctly by repeating the command SQL>SHOW PARAMETER SHARED_POOL_SIZE.

  • Commence installation of Apex by issuing the following command at the sqlprompt SQL>@apexins oracle USERS USERS TEMP /i/. This command runs a sql script called apexins.sql and passes in the parameters: Tablespace for the Apex application user (USERS), Tablespace for the Apex files user (USER), name of the Temporary Tablespace (TEMP) and virtual directory for the images folder (/i/).

  • The installation will take some time to complete and at the end of it you will be disconnected from the database.

  • Go to the Application Express homepage http://localhost:8080/apex/apex_admin and log in with username admin and password oracle. One Last Thing ....
    If after installation you cannot see any images on the page, you can reset the prefix /i/ by running sql c:\oraclexe\apex\utilities\reset_image_prefix.sql. In my case, I was unable to get the prefix to work correctly, so I ran the following command SQL>@reset_image_prefix at the prompt Enter the Application Express image prefix [/i/] I typed in my full path i.e. c:\oraclexe\apex\images/ (Note the path ends with "/" which is a requirement of apex). I then left all other prompts blank and the script ran successfully and images were viewable to me.

  • Footnote:  I'm told that you need to change the application images directory alias in the application properties too.

    Tuesday, 19 August 2008

    DOS - Append DateTime to Filename

    In DOS, you can append the date/time to a filename as follows:

    SET DAY=%DATE:~4,2%
    SET MTH=%DATE:~7,2%
    SET YR=%DATE:~10,4%
    SET HR=%TIME:~0,2%
    SET HR0=%TIME:~0,1%
    IF "%HR0%"==" " SET HR=0%TIME:~1,1%
    SET MIN=%TIME:~3,2%
    SET SEC=%TIME:~6,2%
    The above construct sets the date and time formats and can be adjusted according to whatever format is desired

    The variable MYDATE can then be appended to the file as follows:

    C:\dir > filename_%MYDATE%
    Resulting in something like:

    Oracle - Playing Around with Views

    Just mucking about with some SQL to see what tables are referenced in views:

    select name
      from dba_dependencies 
     where type='VIEW'
       and referenced_name in ('EMP','DEPT','BONUS');
    To narrow this down further to views which use all three tables, we could add:
    select name
      from (
    select name
      from dba_dependencies 
     where type='VIEW'
       and referenced_name in ('EMP','DEPT','BONUS')
    having count(*) = 3 -- Same count as the number of tables
    group by name;
    To then check the actual SQL script for the views returned here, we could add:
    select view_name
         , text
      from dba_views
     where view_name in 
    select name
      from (
    select name
      from dba_dependencies 
     where type='VIEW'
       and referenced_name in ('EMP','DEPT','BONUS')
    having count(*) = 3
    group by name  
    I am sure there are better ways of doing these, but there you have it, a simple SQL to get more information about views and the tables used by them.

    Sunday, 17 August 2008

    Oracle Create Table

    To create a table identical to another table, yet without any of the data, simply use the following syntax:

    Friday, 15 August 2008

    Oracle Append DateTime to spool file

    How to append the date/time to an output spool file in SQLPLUS:

    SQL>column dttm new_value v_dttm noprint;
    The above construct is used to select a value into a user variable

    SQL>select to_char(sysdate,'yyyymmddhh24miss') dttm from dual;
    Here we see the user variable data being passed in, i.e. the date and time format. Note I have not used spaces, colons etc in between my date and time strings as sometimes these can lead to SP-0332 Spooling Errors.

    SQL>spool filename_&v_dttm..log
    Note the two fullstops (..) are deliberate

    Tuesday, 12 August 2008

    Oracle Send Mail Procedure

    A useful way of sending email through Oracle.

    Parameters such as the SMTP host servername, SMTP port may be entered in as default values or be passed in for greater flexibility

    (  p_smtp_host in varchar2 default {'smtp_host'} 
     , p_smtp_port in number default {smtp_port}
     , p_sender in varchar2 default null
     , p_recipient in varchar2 default null
     , p_subject in varchar2 default null
     , p_message in varchar2 default null
    ) IS
    connection UTL_SMTP.CONNECTION;
     connection := utl_smtp.open_connection( p_smtp_host, p_smtp_port );
      utl_smtp.helo( connection, p_smtp_host );
      utl_smtp.mail( connection, p_sender );
      utl_smtp.rcpt( connection, p_recipient ); connection, 'Subject: ' || p_subject  || utl_tcp.crlf ||       p_message );
      utl_smtp.quit( connection );

    The procedure may be called using syntax as follows.
    Note: Parameters which are passed in with default values on the SEND_MAIL procedure, e.g. p_smtp_host and p_smtp_port may be excluded from this call

    exec send_mail  ( p_smtp_host => 'smtp_host', -
                      p_smtp_port => smtp_port, -
                      p_sender    => 'sender@sender_address', -
                      p_recipient => 'recipient@recipient_address', -
                      p_subject   => 'Subject Line - Cricket Site', -
                      p_message   => 'Message Body - Check out' -

    Thursday, 7 August 2008

    Grenada Loves Warnie

    Mobile companies aren't the only ones who adore Shane Warne!

    First there was this homage (on stamps) for being one of Wisden's Cricketers of the Century.

    Now he's being revered on an entire stamp issue. Designed by artist Phillip Howe. These are, in my opinion, simply spectacular.

    I must obtain a copy for my collection soon.

    Here is a sample.

    PeopleSoft Processes by Department

    A query to see how many jobs are run per department through the process scheduler.
    select d.deptid
         , d.descr
         , count(*) kount
      from ps_job a
         , psopralias b
         , psprcsrqst c
         , ps_dept_tbl d
     where a.emplid = b.emplid
       and b.oprid = c.oprid
       and a.deptid = d.deptid
       and a.effdt = (select max(effdt)
                        from ps_job
                       where emplid = a.emplid)
       and d.effdt = (select max(effdt)
                        from ps_dept_tbl
                       where d.deptid = deptid)
    group by d.descr, d.deptid
    order by count(*) desc;

    Wednesday, 6 August 2008

    PeopleSoft Customisations/Migrations

    Here is one way of keeping tabs of customised projects and migrations across various PeopleSoft environments.

    I have used database links to simultaneously query multiple databases. In this case:

    DEV (Development) TST (Testing) UAT (User Acceptance Testing) PRD (Production) RPT (Reporting)

    The DEV environment is the benchmark as I have assumed this is where the project begins its life.

    The SQL queries the DEV environment first and compares to see if the project exists in the other environments. Where it does, it displays the LASTUPDDTTM and the LASTUPDOPRID, which I'm interpreting to be the migrator of the project. Where the project does not exist, I'm displaying the text "Not Migrated".

    In order to pick up customised projects only, I'm filtering out projects last updated by the PPLSOFT user, which signifies it as being Vanilla.

    One final detail is that I'm using Outer Joins to ensure I pick up all projects from DEV.

         , A.DESCRLONG
    Note: I have also included the LASTUPDDTTM separately in the select statement as they can be handy to perform some date calculations, example the number of jobs migrated to a certain environment between a date range. The same of course can be achieved by grabbing the LASTUPDDTTM from the decode statement, but it saves having to perform substrings and date conversions on that string.

    TIP: If you've run the query a few times and hit a limit on the number of database links open, you could always get around this by performing a Commit. There are Oracle documents available supporting the notion of using commits against Select statements across database links.

    Windows Memory Problems

    Been having tons of issues with my PC running into memory problems on a daily basis, attributed to the high number of applications I need to have open at any given time. Like most organisations, throwing more memory at the problem is not always feasible, so I've deployed a tip sent to me by Ken W.

    (Applies to Windows XP - SP2)

    Using regedit Change the key: HKEY_LOCAL_MACHINE\\System\\CurrentControlSet\\Control\\Session Manager\\SubSystems

    %SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16


    %SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,16384,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16

    A quick reboot and we suddenly get a whole lot more out of our memory.

    Note: Use Registry Hacks at your own risk. The default settings are there for a reason, so don't blame it on some blogger who is willing to take a chance!

    Tuesday, 5 August 2008

    PeopleSoft Messaging Query

    A useful query to check which message nodes and associated "Active" Transactions exist.
         , Y.RQSTMSGNAME
         , Y.TRXTYPE
         , Y.EFFDT
         , Y.EFF_STATUS
         , (SELECT *
              FROM PSNODETRX A
                                FROM PSNODETRX A_ED
                               WHERE A.RQSTMSGNAME = A_ED.RQSTMSGNAME
               AND A_ED.EFFDT <= SYSDATE)
               AND A.EFF_STATUS = 'A') Y
    ORDER BY 1;

    Monday, 4 August 2008

    PeopleSoft TPESVCFAIL error

    The following error is sometimes seen at the login prompt:

    CHECK APPSERVER LOGS. THE SITE BOOTED WITH INTERNAL DEFAULT SETTINGS, BECAUSE OF: bea.jolt.ApplicationException: TPESVCFAIL - application level service failure.

    This basically means that there is a problem with the user account used to retrieve the web profile from the database. By default this account is set as PTWEBSERVER.

    To rectify this error check the PSOPRDEFN table (or the Security > User Profile section through the front end) to see if the PTWEBSERVER account is perhaps locked or has an incorrect password etc.

    Note: This user is configured in the webserver file, however passwords are usually encrypted differently between the file and the psoprdefn table, so no correlation may be found.

    Changing this can be as simple as:

  • Changing the file as follows:
    WebUserId=PTWEBSERVER WebPassword=PTWEBSERVER (this is okay to be in plain text.

  • Using datamover, run the following SQL:
    update psoprdefn set OPERPSWD = 'PTWEBSERVER', encrypted = 0 where oprid = 'PTWEBSERVER';
    encrypt_password PTWEBSERVER;