Month: May 2024

Oracle APEX Summit sample application

On By windows7bugsIn APEX, Oracle Database 19c, Oracle FormsLeave a comment

Recently, after setting up APEX for the nth time, I wanted to give the stack a try. I’ve been developing around Oracle technologies for more than 20 years and I felt it was the time. After some serious searches, I came across an Oracle blog that was discussing about migrating from Oracle Forms & APEX, which I was looking for.

[Forms to APEX] Creating a Migration Project (oracle.com)

By the end of the article, author had given a link to the sample application “Summit” that was directly migrated from Oracle Forms. Currently, APEX doesn’t support direct migrations from anymore. Regardless, the example project was truly informative. I suggest you to create a document using the explanations provide with the blog, which could help you with many details later.

As on 28th November 2024, against the feedback, Oracle APEX team has updated the download section with a latest export & it works without any issues once after imported. Hence, majority of the bashing has been removed from this post ;)

Although you can import the application database objects to an existing schema, it is better to be on a schema dedicated for the application so that you don’t have to scavenge through dozens of objects to identify the app specific ones.

CREATE USER SUMMIT IDENTIFIED BY SUMMIT
DEFAULT TABLESPACE EXAMPLE
TEMPORARY TABLESPACE TEMP
/
GRANT DBA TO SUMMIT
/
GRANT CONNECT TO SUMMIT
/
GRANT RESOURCE TO SUMMIT
/

Newly created “Summit” schema prior the demo application installation.

Once you created the schema, add it to your APEX workspace and import the APEX sample application that you downloaded. It is a pretty straight forward thing and when the installer asks you to install the additional components, proceed.

Do not forget to change the Parsing Schema in case if your workspace is associated with multiple schemas.

Proceed with the installation. It takes just few seconds and must install the supporting Objects (that are tables, procedures, sequences, functions etc).

Run the application and you should be welcomed with a beautiful dashboard!

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException

On By windows7bugsIn Oracle Applications(R12)Leave a comment

We’ve our own Microsoft Exchange server & recently we changed the multi-domain SSL certificate with one Wildcard certificate. We’ve got the new certificate against the same FQDN that was used with the MDC and things were working, until we had to restart our servers after a power cycle.

Exchange server stopped sending receiving and sending emails & we had to setup the Exchange Back End server with new Wildcard certificate, that we never did earlier whenever the MDC was renewed. Well, much more were in the pipeline.

After a regular maintenance restart, we noticed our Oracle Application R12 instance completely stopped sending Workflow mailer notifications that allowed our users to respond to work notifications through emails. The strangest thing was, we were able to send email from the EBS host console, using shell scripts…however reconfiguring the notification mailer always failed, complaining about wrong username or password. Checking the smtp log file shown us the error message:

%% Invalidated:  [Session-1, SSL_NULL_WITH_NULL_NULL]
%% Invalidated:  [Session-2, TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384]
Thread-10, SEND TLSv1.2 ALERT:  fatal, description = certificate_unknown
Thread-10, WRITE: TLSv1.2 Alert, length = 2
[Raw write]: length = 7
0000: 15 03 03 00 02 02 2E                               .......
Thread-10, called closeSocket()
Thread-10, handling exception: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: validity check failed

We “knew” that there was nothing wrong with the certificate as it worked everywhere else. On 4th day, we decided to check the Exchange environment.

We found both expired (We moved to SSL 10+ years back and never removed expired certificates from the stores) & new certificates and yet everything looked as they should (Exchange was working!). After some quick discussions, decided to remove the expired certificate from Exchange to give it a try. That was it. Technically, it looks like both the expired and new certificates were matching FQDN and java mailer was referring the expired certificate as it was the first one in the list.

ORA-20987: APEX – Schema is restricted – Contact your application administrator

On By windows7bugsIn APEXLeave a comment

I reinstalled Oracle database 19c & lost the APEX setup I had. I installed and configured APEX with ORDS and was online quickly and wanted to add the additional sample schemas, as this time I “was” “SERIOUS” about learning APEX ;)

After setting up the additional sample schemas, I noticed that the new schemas were not available to add to APEX workspaces! Sample schemas those were installed as part of the DBCA were the only ones available for APEX.

Soon, I realized that it was NOT a very stupid silly situation. There were hardly many articles explaining how to overcome this issue and the closest ones were not on Oracle forums as usual, instead on stackoverflow!

Anyway, I stumbled upon a question about APEX 4 and there was one answer dealing with APEX 5, that is still effective on APEX 23.2. Let’s quickly have a look at it.

Find the schemas that are restricted for APEX. Connect as SYS or SYSTEM

ALTER SESSION SET CONTAINER="ORCL"; --your PDB on which the APEX is installed your sample schemas reside
/
SELECT * FROM APEX_230200.wwv_flow_restricted_schemas;
/

The above should provide you a list of schemas that are restricted. “OE” and other sample schemas that you have installed once after setting up the database must be listed in this listing. Now proceed to derestrict the schema for APEX. We will use the sample schema “OE” for this exercise.

EXEC APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA('OE');
/

EXEC APEX_INSTANCE_ADMIN.ADD_SCHEMA('YOUR_WORKSPACE_NAME', 'OE');
/
-- Don't forget to commit!
commit;
/

That’s all. Once logged in, APEX will show the schema “OE” or all other schemas you have added following the above method under the targeted workspace.

DBT-50000 Unable to check for available memory

On By windows7bugsIn UncategorizedLeave a comment

You may run into this error while trying to setup a new database using Oracle 19c DBCA immediately after “software only” installation. The version of software that is available to download from Oracle repository is age old (19.3.0.0 for Windows) & if you are very attentive, notice that the installation process clearly warns about uncertified OS, while the software being installed on latest versions of Windows 11 / or other.

While this doesn’t have anything to do with the “Disabled Admin Shares”, identified as a bug & has a solution.

Open an elevated command prompt, switch to Oracle Home bin folder. and execute the DBCA 

dbca.bat -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

You should able to configure the new database without any additional issues this time.

ORA-12518, TNS:listener could not hand off client connection

On By windows7bugsIn Oracle database 11g, Oracle Database 19c, WindowsLeave a comment

I recently had to reinstall the 19c database and chose not to configure a new listener since I already had an 11g listener set up. Everything was fine until I attempted to set up APEX again. The APEX installation went well, but when I tried to set up ORDS, I began encountering the following errors..

Connecting to database user: system url: jdbc:oracle:thin:@//localhost:1521/orcl
Failed to connect to user: system url: jdbc:oracle:thin:@//localhost:1521/orcl
Listener refused the connection with the following error:
ORA-12518, TNS:listener could not hand off client connection
  (CONNECTION_ID=GUdr3KiAROGLcH8vxlpaMg==)
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12518, TNS:listener could not hand off client connection
  (CONNECTION_ID=GUdr3KiAROGLcH8vxlpaMg==)

The situation seemed dire. Oracle support articles primarily addressed process deficiencies and the reconfiguration of 11g listeners for 19c database services, but these methods were ineffective for me this time. After some research, I stumbled upon a post detailing Oracle Net Services versions 9.2.0.5 to 10.2.0.1, notorious for ORA-12518 errors.

Consequently, I eliminated all 19c references from the 11g listener services and established a new 19c listener service. That turned out to be the solution. It appears that the jdbc thin clients utilized by the latest ORDS versions are incompatible with 11g net services, according to my tests. If this resolves your issue, please inform us in the comments!

(This post was rewritten using #Microsoft #Copilot preview)