Saturday, 24 October 2015

Attending open world

I’m not too sure whether anyone out there (listening to this) is going to oracle open world, but I’m lucky enough to be attending.  Reach out to me if you’d like a demo of google analytics for JDE or perhaps a look at our enterprise mobility solutions.  We could also just talk about how cool 9.2 is until the wee hours of the morning…

I really look forward to see what is out there in the oracle space for the next year.  I’ll be sure to blog anything interesting that I learn.

Tuesday, 20 October 2015

getting OATS to work with OVR in E1–JDE load testing tips

This was not the easiest thing I’ve ever done, but interesting things never are.
I have been doing some load testing, but need to ensure that OVR reports are being load tested at the same time.  OVR, as you know runs on the xmlpserver / xmlpublisher technology from oracle.
Of course, I suspected that out of the box I might have a few issues getting this to work and I was not wrong.
I was getting a lot of errors like this: 0.112 10-20 00:19:23 0.109  Warning Invalid HTTP response code: 500  Internal Server Error , for Url:   



So getting 500’s and 404’s when running, right – we need some fixes.

In general the server maintains cookies or passes header values back and forth to ensure that you have the ability to see it’s resources, xmlp is the same in this respect.  Passing the values that are recorded by OATS are never going to cut the mustard on a replay.  this is where you need to work out what variables that you need to ensure are unique for each session and then create some internal OATS variables for these.

For me this is generally a bit of trial and error, as really there is no way of determining exactly.

Here are some likely candidates for XMLP:

Likely are _xdo and _xtoken, but what you’ll find is _xdo is unique for the report being called, so only xtoken needs to be replaced.

You’ll find this hidden in a jde screen, use a regular expression like this for extraction:

http.solve("xtoken", "_xf=analyze&_xtoken=(.+?)'", "", false,
      Source.Html, 0, EncodeOptions.None)

Then you’ll also need to replace the unique number in the following string”":  and pass this around to all commands that use it.

Note that this is the HTML returned by the command “http://jdebipubprod01.local:9704/xmlpserver/io/zip/allocate-resource-cache” search for allocate-resource-cache


Once both of the above are done, your load tests should start belting the OVR server just like they should.

Friday, 16 October 2015

weblogic cannot start E1 web instance with server manager

This is becoming a little frustrating.

Because weblogic redeploys the application after EVERY restart from the owl_deployment directories under jde_home – if you have any directories locked or explorer open on any of these directories accidently – the application cannot redeploy and will not start.


If you goto http://servername:7001/console and look at the application, you’ll see that it’s running!


You’ll see that it’s running in WLS, but JDE states it’s not.

Remove all of the locks on the runtime dirs, oracle\middleware\user_projects…

Then stop application from wls console

Then start from SM

Thursday, 15 October 2015

Schedule a basic oracle script

This is for me when I need to do this again in a week…

I keep forgetting


c:\windows\syswow64\wscript.exe //e:vbscript d:\myriad\scripts\CheckForCorruptVersionsInProd.vbs

The contents of the script:

The script is totally RAD, it does a query (nice), but also emails results and uses some basic password encryption for prying eyes.

'Username, password and URL settings
'Modify what is required to get this running, username, password (create read only innocuous users)
Const strSmartHost = ""
RecipientNames = ARRAY("", "")
'The password below has been encrypted with a top secret encryption key
'If the JDE password changes, then you'll need to reencrypt the new password
'This can be done by calling EncryptPasswordToFile fCrypt("newPAsswordText","Something99"),"passwordFile"


PasswordString="Password=" & fCrypt(EncryptedPassword, "Something99") & ";"

'EncryptPasswordToFile encrypted,"passwordFile"

Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset
set recset = wscript.createobject("ADODB.Recordset")
Dim cmd
set cmd = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")

Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=e1sys;" & _
"User ID=jde;" & _

Set cmd.ActiveConnection = Oracon
'cmd.CommandText = "Select count(*) from dvfindta.f0101"
cmd.CommandText = "SELECT vrpid, vrvers, vrjd, vrved FROM pd910.f983051 where vrvers not like 'XJDE%' and vrvers not like 'ZJDE%' and vrpodata is null and vrdstnm > ' '"
Set recset = cmd.Execute
'Note that often this is not supported by the DB, so... Need to use a variable
if recset.recordcount = -1 then
end if
emailBody=chr(10) & chr(13) & "*** This Script contains a list of versions that should not have NULL PO's (500 max) ***" & chr(10) & chr(13)
emailBody=emailBody & encrypted & chr(10) & chr(13)
emailBody=emailBody & cmd.CommandText & chr(10) & chr(13)
emailBody = emailBody & "vrpid, vrvers, vrjd, vrved" & chr(10) & chr(13)
while ((i < counter) and (recset.EOF <> true))
for each field in recset.fields
tuple=tuple & """" & field.value & ""","
emailBody=emailBody & chr(10) & chr(13) & tuple
tuple=" "

'wscript.echo emailBody
i=sendMail("WeeklySummary of NULL POs", emailBody,"")

set recset = nothing
set cmd = nothing
set oracon = nothing

function sendMail(subjectText, emailBody, attachment)
on error resume next
Dim iMsg
Dim iConf
Dim Flds
Const cdoSendUsingPort = 2

for each name in RecipientNames
'Create the message object
Set iMsg = CreateObject("CDO.Message")
'Create the configuration object
Set iConf = iMsg.Configuration
'Set the fields of the configuration object to send using SMTP via port 25.
With iConf.Fields
.item("") = cdoSendUsingPort
.item("") = strSmartHost
End With

'Set the message to,from,subject,body properties.
if strlen(attachment) > 1 then
With iMsg
.AddAttachment attachment
.To = name
.From = FromEmailAddress
.Subject = subjectText & " " & now()
.TextBody = chr(1) & " " & emailBody
End With
With iMsg
.To = name
.From = FromEmailAddress
.Subject = subjectText & " " & now()
.TextBody = emailBody
End With
end if
set iMsg = Nothing

end function

Function fCrypt(sPlainText, sPassword)
'This function will encrypt or decrypt a string using the RSA's RC4 algorithm.
Dim aBox(255), aKey(255), sTemp, a, b, c, i, j, k, iCipherBy, sTempswap, iLength, sO
i = 0:j = 0:b = 0
iLength = Len(sPassword)
For a = 0 To 255
aKey(a) = Asc(Mid(sPassword, (a Mod iLength)+1, 1))
aBox(a) = a
For a = 0 To 255
b = (b + aBox(a) + aKey(a)) Mod 256
sTempswap = aBox(a)
aBox(a) = aBox(b)
aBox(b) = sTempswap
For c = 1 To Len(sPlainText)
i = (i + 1) Mod 256
j = (j + aBox(i)) Mod 256
sTemp = aBox(i)
aBox(i) = aBox(j)
aBox(j) = sTemp
k = aBox((aBox(i) + aBox(j)) Mod 256)
iCipherBy = Asc(Mid(sPlainText, c, 1)) Xor k
sO = sO & Chr(iCipherBy)
fCrypt = sO
End Function

Function EncryptPasswordToFile(szPassword, filename)
Set wshShell = CreateObject( "WScript.Shell" )
tmpDirectory = wshShell.ExpandEnvironmentStrings( "%TMP%" )
set wshShell = Nothing
Const ForAppending = 8
logfile=tmpDirectory & "\" & filename & ".txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)


end function

Thursday, 8 October 2015

Cheats way to copy a pathcode, quickly

Quick way to duplicate a pathcode:

Central objects copy, takes about 40 mins.  Honestly, forget import and export and security and indexes and constraints…  Just do this:

select 'TRUNCATE TABLE PP910.' || table_name || ';' from all_Tables where owner = 'PP910' and length(table_name) < 12 and table_name not in ('DATACOUNTS', 'F983051_BAK', 'F98950BK') ;

select 'INSERT INTO PP910.' || table_name || ' SELECT * FROM PD910.' || table_name || '@jde_e1sys ;' from all_Tables where owner = 'PP910' and length(table_name) < 12 and table_name not in ('DATACOUNTS', 'F983051_BAK', 'F98950BK') ;

update pp910.f983051 set vrenhv = 'PP910' ;

Then copy the PD910\include and PD910\source dirs on the dep server to PP910

Fix object librarian

create table jde.f9861srm as select * from ol910.f9861 where sipathcd = 'PD910' ;
delete from ol910.f9861 where sipathcd = 'PP910' ;
update jde.f9861srm set sipathcd = 'PP910' ;
insert into ol910.f9861 select * from jde.f9861srm ;

Full build and deploy.

Improvement on JDE environment audit script

I’ve put a number of iterations of this on my blog, but hopefully they keep getting better.  I’m using the current one to devise a script that keeps a couple of “Systems” in sync.  On a nightly basis I want to replicate a number of data changes from one system to the other.

This uses a left outer join, so that you get all of the JDE tables that are in OL910, but also all of the copies and full package table names – which is really handy.

I use this to identify the tables that have data and then use this for my synchronisation logic.



--I use the below statement to get me a likely list of JDE owners that have JDE tables. This is using the F% criteria, note that it essentially builds the “IN” clause for you.
SELECT '''' || OWNER || ''',', count(1) FROM ALL_TABLES WHERE TABLE_NAME LIKE 'F%' group by owner;

grant all on SY910.DATACOUNTS to public ;

delete from sy910.datacounts ;

insert into SY910.datacounts select table_name,owner,0,0,simd
from all_tables left outer join ol910.f9860 on ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where owner in ('SVMDV01',


select 'update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts
where table_owner in ('SVMDV01',
order by table_name desc ;

--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.SHAE ) WHERE table_name = 'SHAE' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F99410 ) WHERE table_name = 'F99410' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F98TMPL ) WHERE table_name = 'F98TMPL' and table_owner = 'SY910';

Wednesday, 7 October 2015

9.2 release is GA

The next major release of JD Edwards is GA

Oct. 6, 2015: JD Edwards EnterpriseOne Announces Release of Applications and Tools Release 9.2!

Please see the announcement here.

The Oracle Support Document 2062671.1 (JD  Edwards EnterpriseOne 9.2 Certifications) can be found here.

The Oracle Support Document 2054020.1 (Required Components for JD  Edwards EnterpriseOne Tools 9.2) can be found here.

NOTE: Tools Release Maintenance Pack is the first Tools Release for JD Edwards EnterpriseOne Application and Tools Release 9.2.

Cutting through the marketing, go here,P141_SECTION_ID:343,2576

So you get the following snazzy screen:


The tools enhancements are categorised under 4 main headings:

User Defined Objects

User Defined Objects (UDO) are web objects created using the JD Edwards EnterpriseOne web interface. You can create UDOs for your own use, view UDOs others have created, or share your UDOs so others can view and use them. Your ability to perform any of these tasks depends on the permissions you and others have been granted by your administrator.

There are six UDOs that you create from the EnterpriseOne interface:

  • Queries - fields and QBE columns you select from a form and add conditions to in order to make the search criteria more specific.
  • Grid Formats - a selection of the columns you choose to display, the sequence in which you want them displayed, and how information is displayed in your grid.
  • Composite Application Framework - a user interface framework that enables JD Edwards EnterpriseOne applications to integrate with multiple third-party content and applications, as well as other EnterpriseOne applications.
  • One View Reports - a Business Intelligence (BI) Publisher data model, a BI Publisher report, the report definition for the One View Report, which is the metadata for the report and resides in JD Edwards EnterpriseOne.
  • One View Watchlists - a collection of items that match user-defined criteria and contain information to which users have selected to be alerted.
  • EnterpriseOne Pages -the first screen end-users see when they log into EnterpriseOne. They are HTML files and can contain any HTML-enabled functionality, such as interactive process flows, URL links and web pages, company logos, and so on.

My comments: These all exist, but I think that the management has been made more consistent.  See below:

   Object Management Workbench on the Web

Object Management Workbench (OMW) is a system used to manage the development of JD Edwards EnterpriseOne objects on the development client. Available with Tools 9.2 is the new Web OMW which is available on the web client and is used to manage user defined objects (UDO).  UDOs are objects created from the web client.  For a list of these objects, look above

Both OMW systems are defined using the Object Management Configuration application.

This is totally awesome, having a consistent management framework for promoting these objects is great.  Please include things like security in the next release… Please!


Oracle's JD Edwards EnterpriseOne Tools provides security applications, reports, and features to help you protect your company's sensitive application data. EnterpriseOne authentication security ensures that only authenticated users can sign in to EnterpriseOne. Authorization security ensures that EnterpriseOne users have access to only the applications and features that they are authorized to use.

In addition, EnterpriseOne enables you to set up security for developers who use Object Management Workbench (OMW) to add and modify objects for custom applications. Setting up developer security ensures that developers can only perform certain actions in OMW based on pre-defined responsibilities.

EnterpriseOne also includes reports that you can use for security auditing purposes, as well as auditing features for supporting the 21 CFR Part 11 auditing regulations.

With Tools Release 9.2, JD Edwards has removed the 10 character restriction for user ID and password to support more robust security authentication processes. This enhancement enables security officers to define complex password rules that align with their security and compliance requirements. User IDs have also been expanded to improve the end-user experience when signing into JD Edwards.

WOW – this is awesome.  Not so great for myLunch – but great!!!

The following content describes changes to security implemented with Tools Release 9.2.


Security Features

1. User Defined Object Security

Tools 9.1: Not applicable

Tools 9.2: Administrators can enable or disable the user defined object (UDO) features, as well as set up security for viewing, creating, and modifying individual UDOs.

EnterpriseOne provides UDO security features in the Security Workbench to enable administrators to control:

  • Which UDO features are available in EnterpriseOne.
  • Who can create UDOs for their own personal use.
  • Who can request to publish (or share) UDOs with other users.
  • Who can view/use shared UDOs created by other users.
  • Who can modify shared UDOs created by other users.

Action: Enable access to the user defined object security and administration applications, then set up security for user defined object features and user defined objects.

Documentation: JD Edwards EnterpriseOne Security Administration Guide

Managing Security for User Defined Objects

2. Secure FTP (SFTP) for Media Objects

Tools 9.1: Only non-secure FTP is available.

Tools 9.2: Secure FTP (SFTP) for media object access on the HTML Server and Business Services Server is configurable using Server Manager.

Action: Oracle recommends using SSH file transfer protocol, otherwise referred to as Secure FTP (SFTP), for accessing media objects as a more secure alternative to FTP. When EnterpriseOne is configured to use SFTP for media objects, users can securely upload, download, and delete media objects.

Documentation: JD Edwards EnterpriseOne Security Administration Guide (for HTML Server)

JD Edwards EnterpriseOne HTML Server Security

Business Services Server Security

3. Enhanced Database Security

Tools 9.1: All tables are created and shipped with PUBLIC ALL authority.

Tools 9.2: For an installation, all tables delivered by the Platform Pack installer are locked down.

Action: To enable you to maintain the security on the tables, you must set up the security definition using P986117 (signed into JDEPLAN) before running the Installation Workbench.  Any new tables that the Workbenches create (for example the TC Workbench or the Environment Workbench) will be created according to the rules in F986117.

Documentation: JD Edwards EnterpriseOne Security Administration Guide

Managing Data Source Security


Oracle Product Features

Access the Oracle Product Features tool using your Oracle Technology Network login to explore a comprehensive list of features and enhancements introduced with each release of the JD Edwards EnterpriseOne and JD Edwards World products.  View information by release or product area. Use the Compare Releases option to review product changes introduced since your last update.

Tuesday, 6 October 2015

uninstall e1local


okay, I’m having a mare with the e1local database on a fat client.  Things should not take this long.

Sure, I know it’s my fault – hitting shift delete on the c:\E910 dir after stopping the local oracle database.  I know I should not have done it…


Tried using reconfigureMSDEDatabase to get the password – NO

Generating encrypted password...
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...

ERROR:  Could not change the SYSTEM user's password.  The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details.  Will not attempt to change the SYS user's password.
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...

ERROR:  Could not change the SYSTEM user's password.  The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details.  Will not attempt to change the SYS user's password.
Generating encrypted password...
Starting service OracleE1LocalTNSListener for the database...
Starting service OracleServiceE1Local for the database...

ERROR:  Could not change the SYSTEM user's password.  The 'From' password may be incorrect, but look in c:\ReconfigureMSDE.log and jde.log for more details.  Will not attempt to change the SYS user's password.



  1. Change this file (sqlnet.ora) and set SQLNET.AUTHENTICATION_SERVICES=(NTS).
  2. Restart the OEE E1Local instance.

No – nothing

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release Production on Tue Oct 6 17:11:30 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ORA-01031: insufficient privileges



Changing the path so that e1local was in front of my local client install – NO

tnsping also told me everything was right:

C:\Windows\system32>tnsping e1local

TNS Ping Utility for 64-bit Windows: Version - Production on 06-OCT-2015 17:13:34

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = E1Local)))

OK (10 msec)


Tied of tried to be honest…

Went to delete and install the local engine – look how helpful OUI is!  it’s funny!


Tells me to run a command at the command line.


Which runs

reg.exe DELETE HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_E1Local /f
@echo off

Rem Copyright (c) 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem deinstall.bat - script that calls deinstall_E1Local.bat.
Rem This script calls the deinstall_db.bat script, passing in the
Rem correct parameters.
Rem <other useful comments, qualifications, etc.>
Rem mchaney 04/04/06 - Created Windows version.

set ORACLE_HOME=%~dp0\..

net stop OracleE1LocalTNSListener
net stop OracleMTSRecoveryService
net stop OracleServiceE1LOCAL

%ORACLE_HOME%\deinstall\deinstall_E1Local.bat -silent -paramfile %ORACLE_HOME%\deinstall\response\deinstall_E1Local.rsp


C:\Oracle\E1Local\deinstall>reg.exe DELETE HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_
E1Local /f
The operation completed successfully.
The OracleE1LocalTNSListener service is stopping.
The OracleE1LocalTNSListener service was stopped successfully.

The OracleMTSRecoveryService service is stopping.
The OracleMTSRecoveryService service was stopped successfully.

The OracleServiceE1LOCAL service is stopping...
The OracleServiceE1LOCAL service was stopped successfully.

Checking for required files and bootstrapping ...
Please wait ...
ERROR: Cannot find the file bin\ in the ORACLE_HOME c:\Oracle\E1Lo
cal. You must download the standalone version of the deinstall tool from OTN to
deconfigure/deinstall this Oracle Home.
cannot remove directory for C:\Users\smoir\AppData\Local\Temp\OraDeinstall2015-0
6-10_17-18-54: Permission denied at c:\Oracle\E1Local\deinstall\ lin
e 179
1 file(s) copied.
1 file(s) copied.
The system cannot find the path specified.
1 dir(s) moved.

Will this ever end…  shift delete is coming out again!

Remember that if you are having issues with local oracle, logs are:


Mon Oct 05 12:44:20 2015
c:\Oracle\E1Local\bin\oradim.exe -startup -sid e1local -usrpwd * -log oradim.log -nocheck 0
Mon Oct 05 12:44:28 2015
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\E910_1\PD910\DATA\JDELOCAL_PD910.DBF'

Mon Oct 05 13:14:38 2015
c:\Oracle\E1Local\bin\oradim.exe -startup -sid e1local -usrpwd * -log oradim.log -nocheck 0
Mon Oct 05 13:14:57 2015
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\E910_1\PD910\DATA\JDELOCAL_PD910.DBF'

What tools release can I take for my application release?

I always get confused on what tools release you can take based upon your current application release, so this table is great.  This information changes all of the time, so probably best you go to for the gospel.

JDEdwards OneWorld EnterpriseOne Application Releases
Application Release XE/ERP8 8.10 8.11 8.11 SP1 8.12 9.0 9.1
Tools Release
SP23/24 X X X X X X
  8.93 X X X X X
  8.94 8.94 X X X X
  8.95 8.95 8.95 X X X
  8.9 8.96 8.96 8.96 X X
  8.97 8.97 8.97 8.97 X X
  8.98 8.98 8.98 8.98 8.98 X
  8.98.1 8.98.1 8.98.1 8.98.1 8.98.1 X
  X 8.98.2 8.98.2 8.98.2 8.98.2 X
  X 8.98.3 8.98.3 8.98.3 8.98.3 X
  X 8.98.4
( or earlier)
( or earlier)
( Terminal Release)
( Terminal Release)
  X X X X
  X X X X
  X X X X
  X X X X
  X X X X