Saturday, 23 January 2016

APEX Dashboard Competition for English Locale

The other day Tobias announced a new competition where you can win Apple products, and what you need to do is build a dashboard in Oracle Application Express using some supplied data.


I'm about to go on a little trip so I thought I'd load up this data into my local VM so that if I had some down time, I could have a play around with it (not yet sure if I'll enter!). The only problem was, I started receiving errors during the data import, as per:




As from that screenshot, you will see the number is using a decimal point as a comma (as opposed to what I am used to, and what the session is expecting, a period). To be honest, I didn't even realise different locale's used different decimal marks - but after turning to Wikipedia, I see there are a large number of nations that do: https://en.wikipedia.org/wiki/Decimal_mark

After a bit of research, I discovered that you can change the session parameter NLS_NUMERIC_CHARACTERS to support the different locale's. With that I came up with the script:

@/home/trent/Downloads/APEX_DASHBOARD_COMPETITION_DATEN/DATEN_FINAL/DDL_EXPORT.sql
alter session set NLS_NUMERIC_CHARACTERS=',.';
@/home/trent/Downloads/APEX_DASHBOARD_COMPETITION_DATEN/DATEN_FINAL/DML_EXPORT.sql
alter session set NLS_NUMERIC_CHARACTERS='.,';

With those session modifications the data should (it did for me) load seamlessly.

The next task, was that all the data is in German. It's not a huge deal, as it's the numbers that matter. But, I feel it a lot easier to work with data I can at least interpret! So, I wanted to go ahead and come up with a way to tweak this so I can understand the information better. With a bit of google translate help, I made a series of CSV files to create a mapping. Check out this little project: https://github.com/tschf/dashtrans. In the raw folder is a series of tabular data with the word as it is in the data (original) and an English translation (not all perfect!). Once you have the project files, from the same directory, you can apply the updates as per the CSV files with:

sql dash_comp/dash_comp@//192.168.1.15/xe @to_en

Hope it's useful!

Wednesday, 13 January 2016

Upgrading APEX on XE without the patch sets

To get APEX patch sets requires a paid support account to get access to the patch sets for minor updates - so upgrading APEX on our XE database installations can be challenging for those of us without a paid support account.

So effectively, what you need to do is remove APEX, and re-install from scratch. APEX also comes with a Java program to perform backups, where you can for example export invididual workspaces, export all workspaces, export invidual applications, export applications by workspace id, or export all applications. I grabbed the idea from this apexbackup project on GitHub: https://github.com/OraOpenSource/apexbackup. Basically, you need to set your CLASSPATH to point to:

  1. The Oracle Java database driver - typically $ORACLE_HOME/lib/ojdbc5.jar or $ORACLE_HOME/jdbc/lib/ojdbc5.jar
  2. The utilities folder in the APEX installation files

After that has been set,  you can run the program. e.g: 

export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/apex/utilities/
java oracle.apex.APEXExport

With the requires arguments depending on which operation you wish to perform. If you run it without any arguments, output to the screen will be a list of all the options:

Usage APEXExport -db  -user  -password  -applicationid  -workspaceid -instance -expWorkspace -expMinimal -expFiles -skipExportDate -expPubReports -expSavedReports -expIRNotif -expTranslations -expTeamdevdata -expFeedback -deploymentSystem -expFeedbackSince -expOriginalIds -debug  
    -db:               Database connect url in JDBC format 
    -user:             Database username
    -password:         Database password
    -applicationid:    ID for application to be exported
    -workspaceid:      Workspace ID for which all applications to be exported or the workspace to be exported
    -instance:         Export all applications
    -expWorkspace:     Export workspace identified by -workspaceid or all workspaces if -workspaceid not specified
    -expMinimal:       Only export workspace definition, users, and groups
    -expFiles:         Export all workspace files identified by -workspaceid
    -skipExportDate:   Exclude export date from application export files
    -expPubReports:    Export all user saved public interactive reports
    -expSavedReports:  Export all user saved interactive reports
    -expIRNotif:       Export all interactive report notifications
    -expTranslations:  Export the translation mappings and all text from the translation repository
    -expFeedback:      Export team development feedback for all workspaces or identified by -workspaceid to development or deployment
    -expTeamdevdata:   Export team development data for all workspaces or identified by -workspaceid
    -deploymentSystem: Deployment system for exported feedback
    -expFeedbackSince: Export team development feedback since date in the format YYYYMMDD
    -expOriginalIds:   If specified, the application export will emit ids as they were when the application was imported
    
    Application Example: 
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -applicationid 31500 
    Workspace  Example: 
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 
    Instance Example:
       APEXExport -db localhost:1521:ORCL -user system -password systems_password -instance 
    Export All Workspaces Example:
       APEXExport -db localhost:1521:ORCL -user system -password systems_password -expWorkspace 
    Export Feedback to development environment:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -expFeedback 
    Export Feedback to deployment environment EA2 since 20100308:
       APEXExport -db localhost:1521:ORCL -user scott -password scotts_password -workspaceid 9999 -expFeedback -deploymentSystem EA2 -expFeedbackSince 20100308

So with that, to upgrade our APEX instances on minor releases (patches) we can script up a program to export all our workspaces, and restore them after a fresh installation - by getting the latest, full download from OTN.

I started working on a new project over the weekend to do exactly this - https://github.com/tschf/daxul. It's still in early stages, but to the point it's working for me. Basically, the arguments it accepts (current order being important) is:
  • Path to the apex installation files
  • Database host
  • Database port
  • Database sid
  • System password
  • Sys password
  • Path to where the images are stored
Once you grab the project on your server, you would simply run it like so:

sudo -E ./daxul.sh /home/trent/apex localhost 1521 xe oracle oracle /ords/apex_images

Using the same order of arguments described above.

That goes without saying, this should be run as a user with permissions to write to the destination image directory, and they should have $ORACLE_HOME set. And you will likely want to take a separate backup of all your applications in case anything breaks in the script after the APEX removal - especially in these early stages of the project (I haven't had any other testers so far).

My script is also taking a backup of the instance configuration to restore it post upgrade (though, I haven't got everything working so far due to apex_instance_admin.set_parameter seemingly not being supported for all parameters). The main thing that will be missing post upgrade is all the users previously set up in the INTERNAL workspace. I have my machine installed in VirtualBox, so I took a snapshot beforehand should anything fall over, so I can easily restore it.

Some immediate enhancements requires are to support named arguments so order isn't important, along with default values if any are omitted; as well as adding some additional ones (namely, for the values passed to the APEX install script).

Even if you don't use my specific script/project, it can be a good example case to base off to develop something for your own business.

Have I left any other crucial bits off my script - that would need backup and restoration?

Here's a little video of my running the script:


Monday, 11 January 2016

APEX Instance Admin Preferences Cheat Sheet

APEX comes with an API - APEX_INSTANCE_ADMIN - where you can both get and set instance preferences. There is a list of available properties on the documentation, however I found that not all preferences were documented there. For example, in Feature Configuration, there are some preferences surrounding packaged applications:
















Looking at the documentation, https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_instance.htm#CHDFEICJ, you may find it difficult locating these.

Then, there other properties in there that I don't think are relevant any more (..but not an expert on the subject, so may be wrong). Lets take the example of PASSWORD_HISTORY_DAYS - this states "Defines the maximum number of days a developer or administrator account password may be used before the account expires. The default value is 45 days.". We can locate the property it's talking about under Security - Authentication Control - Development Environment Settings.





If we attempt to query this property name, you will find nothing is returned.

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production



SQL> set serveroutput on

SQL> begin

  2    dbms_output.put_line('Value: ' || apex_instance_admin.get_parameter('PASSWORD_HISTORY_DAYS'));

  3  end;

  4  /

Value:



PL/SQL procedure successfully completed.



SQL>


Digging into this, it looks to me this relates to the parameter ACCOUNT_LIFETIME_DAYS.

So anyway, with all this said, here is what I've come up with a table mapping each field to a parameter name - some on the docs, others not.

Text Version:

Feature Configuration

Application DevelopmentParameter Name
Allow PL/SQL Program Unit EditingPLSQL_EDITING
Create demonstration objects in new workspacesWORKSPACE_PROVISION_DEMO_OBJECTS
Create websheet objects in new workspacesWORKSPACE_WEBSHEET_OBJECTS
Enable SQL Access in WebsheetsWEBSHEET_SQL_ACCESS
Packaged Application install Options
Allow HTTP Header Variable authenticationPKG_APP_AUTH_ALLOW_HHEAD
Allow LDAP Directory authenticationPKG_APP_AUTH_ALLOW_LDAP
Allow Oracle Applicaion Server Single Sign-On authenticationPKG_APP_AUTH_ALLOW_SSO
SQL Workshop
SQL Commands Maximum Inactivity in minutesSQL_COMMAND_MAX_INACTIVITY
SQL Scripts Maximum Script Output Size in bytesSQL_SCRIPT_MAX_OUTPUT_SIZE
SQL Scripts Maximum Workspace Output Size in bytesWORKSPACE_MAX_OUTPUT_SIZE
SQL Script Maximum Script Size in bytesMAX_SCRIPT_SIZE
Enable transactional SQL CommandsENABLE_TRANSACTIONAL_SQL
Enable RESTful ServicesRESTFUL_SERVICES_ENABLED
Monitoring
Enable Database MonitoringALLOW_DB_MONITOR
Application Activity LoggingAPPLICATION_ACTIVITY_LOGGING
Enable Application TracingTRACING_ENABLED
Workspace Administration
Enable Service RequestsSERVICE_REQUESTS_ENABLED
Team Development
Enable Team Developer's File RepositoryWORKSPACE_TEAM_DEV_FILES_YN
Maximum File Size (in MB)WORKSPACE_TEAM_DEV_FS_LIMIT


Security

SecurityParameter Name
Set Workspace CookieWORKSPACE_NAME_USER_COOKIE
Disable Adminstrator LoginDISABLE_ADMIN_LOGIN
Disable Workspace loginDISABLE_WORKSPACE_LOGIN
Allow Public File UploadALLOW_PUBLIC_FILE_UPLOAD
Restrict Access by IP AddressRESTRICT_IP_RANGE
Instance ProxyINSTANCE_PROXY
Checksum Hash FunctionCHECKSUM_HASH_FUNCTION
Rejoin SessionsREJOIN_EXISTING_SESSIONS
Unhandled ErrorsHTTP_ERROR_STATUS_ON_ERROR_PAGE_ENABLED
HTTP Protocol
Require HTTPSREQUIRE_HTTPS
Require Outbound HTTPSREQUIRE_OUT_HTTPS
HTTP Response HeadersHTTP_RESPONSE_HEADERS
RESTful Access
Allow RESTful AccessALLOW_REST
Session Timeout
Maximum Session Length in SecondsMAX_SESSION_LENGTH_SEC
Maximum Session Idle Time in SecondsMAX_SESSION_IDLE_SEC
Workspace Isolation
Allow HostnamesALLOW_HOSTNAMES
Resource Consumer GroupRM_CONSUMER_GROUP
Maximum Concurrent Workspace RequestsQOS_MAX_WORKSPACE_REQUESTS
Maximum Concurrent Session RequestsQOS_MAX_SESSION_REQUESTS
Concurrent Session Requests Kill TimeoutQOS_MAX_SESSION_KILL_TIMEOUT
Maximum Size of FIles in WorkspaceWORKSPACE_MAX_FILE_BYTES
Region and Web Service Excluded Domains
Domain Must Not ContainBAD_URLS
Authentication Control
General Settings
Delay after failed login attempts in SecondsLOGIN_THROTTLE_DELAY
Method for computing the DelayLOGIN_THROTTLE_METHODS
Inbound Proxy ServersINBOUND_PROXIES
Single Sign-On Logout URLSSO_LOGOUT_URL
Development Environment Settings
Username validation expressionUSERNAME_VALIDATION
Require User Account Expiration and LockingEXPIRE_FND_USER_ACCOUNTS
Maximum Login Failures AllowedMAX_LOGIN_FAILURES
Account Password Lifetime (days)ACCOUNT_LIFETIME_DAYS
Current Workspace Authentication SchemAPEX_BUILDER_AUTHENTICATION
Password Policy
Password Hash FunctionPASSWORD_HASH_FUNCTION
Minimum Password LengthPASSWORD_MIN_LENGTH
Minimum Password DifferencesPASSWORD_NEW_DIFFERS_BY
Must Contain At Least One Alphabetic CharacterPASSWORD_ONE_ALPHA
Must Contain At Least One Numeric CharacterPASSWORD_ONE_NUMERIC
Must COntain At Least One Punctuation CharacterPASSWORD_ONE_PUNCTUATION
Must Contain At Least One Upper Case CHaracterPASSWORD_ONE_UPPER_CASE
Must Contain At Least One Lower Case CharacterPASSWORD_ONE_LOWER_CASE
Must Not Contain UsernamePASSWORD_NOT_LIKE_USERNAME
Must Not Contain Workspace NamePASSWORD_NOT_LIKE_WS_NAME
Must Not ContainPASSWORD_NOT_LIKE_WORDS
Alphabetic CharactersPASSWORD_ALPHA_CHARACTERS
Punctuation CharactersPASSWORD_PUNCTUATION_CHARACTERS
Service Administrator Poassword PolicySTRONG_SITE_ADMIN_PASSWORD

Instance Settings

Self ServiceParameter Name
Provisioning StatusSERVICE_REQUEST_FLOW
Require Verification CodeREQUIRE_VERIFICATION_CODE
Notification Email AddressNOTIFICATION_EMAIL
Email Provisioning
Email ProvisioningDISABLE_WS_PROV
MessageDISABLE_WS_MSG
Storage
Require New SchemaREQ_NEW_SCHEMA
Auto Extent TablespacesAUTOEXTEND_TABLESPACES
Bigfile TablespacesBIGFILE_TABLESPACES_ENABLED
Encrypted TablespacesENCRYPTED_TABLESPACES_ENABLED
Delete Uploaded Files After (days)DELETE_UPLOADED_FILES_AFTER_DAYS
Email
Application Express Instance URLEMAIL_INSTANCE_URL
Application Express Images URLEMAIL_IMAGES_URL
SMTP Host AddressSMTP_HOST_ADDRESS
SMTP Host PortSMTP_HOST_PORT
SMTP Authentication usernameSMTP_USERNAME
SMTP Authentication passwordSMTP_PASSWORD
Use SSL/TLSSMTP_TLS_MODE
Default Email From AddressSMTP_FROM
Maximum Emails per WorkspaceWORKSPACE_EMAIL_MAXIMUM
Wallet
Wallet PathWALLET_PATH
Wallet PasswordWALLET_PWD
Report Printing
Print ServerPRINT_BIB_LICENSED
Printer Server ProtocolPRINT_SVR_PROTOCOL
Print Server Host AddressPRINT_SVR_HOST
Print Server PortPRINT_SVR_PORT
Print Server ScriptPRINT_SVR_SCRIPT
Print TimeoutPRINT_SVR_TIMEOUT
Help
Help URLSYSTEM_HELP_URL
Application ID Range
ID MinimumAPPLICATION_ID_MIN
ID MaximumAPPLICATION_ID_MAX

Workspace Purge Settings:

Workspace Purge SettingsParameter Name
EnabledPURGE_ENABLED
LanguagePURGE_LANG
Purge Adminsitration Email AddressPURGE_ADMIN_EMAIL
Send Summary Email toPURGE_SUMMARY_EMAIL_TO
Days until purgePURGE_DAYS_TO_PURGE
Reminder days in advancePURGE_REMINDER_DAYS_IN_ADVANCE
Days inactivePURGE_DAYS_INACTIVE
Grace period (days)PURGE_GRACE_PERIOD_DAYS
Maximum execution time (hours)PURGE_MAX_RUN_HOURS
Maximum number of workspacesPURGE_MAX_WORKSPACES
Maximum number of emailsPURGE_MAX_EMAILS

Image:

Feature Configuration:


Security:




Instance Settings:













































































Workspace purge settings: