DBA Blogs

Time difference between two date/times

Tom Kyte - Fri, 2017-04-28 18:06
Hey Guys Hoping you will be able to help with an issue I am having. I currently have information stored in a table as StartDate - stored as date (eg 18-APR-17) and StartTime - stored as a number(4,2) so 10.30am would be stored as 10.3 or 3.45pm...
Categories: DBA Blogs

lob tablespace -- moving a lob

Tom Kyte - Fri, 2017-04-28 18:06
How do I move lob indexes and lob segments from one tablespace to another.
Categories: DBA Blogs

How to find out, what stored procedure does a commit

Tom Kyte - Fri, 2017-04-28 18:06
This may sound silly, but I'm unable to figure out what damn stored procedure (or something else?) performs a commit after I manually execute another stored procedure. There is a complicated business logic implemented using stored procedures and, ...
Categories: DBA Blogs

Eexecute an Oracle stored procedure via a database link

Tom Kyte - Thu, 2017-04-27 23:46
Hi, I have procedure via database link, and getting this error ORA-02064: distributed operation not supported This is because need to apply pragma autonomous_transaction. But is there any other way? some of the procedure we need to reuse, a...
Categories: DBA Blogs


Tom Kyte - Thu, 2017-04-27 23:46
Hey Tom I am really hoping you can help me with this, I have can not find anything related to this anywhere. We have a daily export .dmp from a Linux PROD DB. We want to import that in a windows new DB . I have tried creating a DB w/ DBCA a...
Categories: DBA Blogs

Advanced index compression HIGH in 12.2

Tom Kyte - Thu, 2017-04-27 23:46
Team, Reading through this documentation link on Advanced index compression HIGH. <u>http://docs.oracle.com/database/122/REFRN/DB_INDEX_COMPRESSION_INHERITANCE.htm#REFRN10336</u> <quote> TABLESPACE Index inheritance is based on tablespac...
Categories: DBA Blogs

Display DBMS output from another DB

Tom Kyte - Thu, 2017-04-27 23:46
Hi, Im running a procedure in DB A which calls a procedure internally which is in DB B. When I turn on set server output on it only displays the DBMS output of DB A and not in DB B. I want all the DBMS outputs regardless of DB.... THANKS
Categories: DBA Blogs

Auto mail for every 6 months

Tom Kyte - Thu, 2017-04-27 23:46
Hi All, I need a query for the below scenario. I am working for a retail application so there is an option for conducting an audit for suppliers. Scenario - While conducting an audit i found a child labor issue so for this case i have to sen...
Categories: DBA Blogs

editing dmp file

Tom Kyte - Thu, 2017-04-27 23:46
Dear Tom iwant to modify the tablespaces in a dump file before importing it to another database doesnt include the tablespaces in the export file how can i do this...? can i generate a sql file to do so...? thanks for your help Mohamed
Categories: DBA Blogs

Oracle E-Business Suite and Java Web Start. Finally!

Pythian Group - Thu, 2017-04-27 20:36

Great things always happen overnight. That’s probably the case for European Oracle Apps DBAs in the same situation, like myself.

This morning I read Steven Chan’s latest blog post about Java Web Start certification with Oracle E-Business Suite and its support release to the public. We heard about it during some informal discussions at Collaborate 17 conference.  Everything is ready, the documentation is prepared, and they are just waiting for final bug related to Workflow Activity Monitor to be addressed…

…and about twenty days later,  it was released.

This is a superb evolutionary event! And, of course, I made time to fit in some good introductory, hands-on experience.


Initial thoughts while going through Doc ID 2188898.1


1. Java Runtime version for client.

  • JRE 8 Update 131 b31 or JRE 8 Update 121 b33 are required (as a minimum).
    This isn’t clearly visible – these are special update releases (look at b31 and b33), available only through My Oracle Support download via Patch 25767257 and 25450542. A public release containing the support is only scheduled for next quarter and is set to be released with Update 141.
  • JRE 6 and JRE 7 are out of scope! If you don’t have JRE 8 support there in your system, now is the right time to think about it.

2. Support for R12.2. Also there is a support for R12.1.3. No 11i.

Patching requirement is minimal (three small patches for AD/TXK/FND), but you need to be on latest AD/TXK.C.8 and ATG_PF.C.6, and new PSU overlay for 10.1.2 Oracle Home which nicely supersedes the previous overlay released.

There are eight functional module patches posted so far. There are still known issues mentioned for other modules. These are less popular modules, and are to be implemented and added to the list at some point, I believe.
I would include this list in your patch maintenance, especially Oracle Workflow related for Activity Monitor.

s_forms_launch_method new context variable is implemented which sets the required profile options at Site level. However, you can still configure exceptions for users via User Level profile option values. Both methods are available. You can still leave browser plugin method as default, and enable JWS only for particular users, let’s say, for testing purposes.

3. “Only one instance per Java applet can be running on the client PC.”

Yes, it’s only possible to have one environment opened at the same time. This is a JWS limitation.  Even IE’s noframemerging or “hey, I can use my second browser” is not an option here.

4. Chrome full usage support is finally available there for Forms.

Basically, browser support is not dependent on browser plugin availability. There is also Microsoft Edge support to mention and we can finally start using 64-bit Java, as there is no longer browser plugin dependency.

5. Known issues section.

From management and housekeeping perspective, docs are mentioning that the process is self cleanable, like frmservlet.jnlp downloaded files should be automatically cleared from disk after launch. There is a known issues section mentioning some nuances, open issues and workarounds for specific browsers and functionality areas in EBS. As always, it will be kept updated and will be periodically reviewed by many of us Apps DBAs for news.


Now to talk about my hands-on…

I used my R12.2.6 Vision lab instance built on AWS.  The patching exercise took something like 30 minutes in total (10.1.2 patch, ebs patches, JAR regeneration). But I was doing this in hotpatch mode and not through online patch cycle, (yes, not the right way, but my goal was to avoid ADOP time consuming tasks.
I also installed the required JRE 8 update 131 b31 64-bit version on my laptop.

I made a huge mistake starting this testing on my MacOS, which introduced a number of issues that almost led me to a huge “why Oracle?” facepalm result. And it’s not just Gatekeeper security feature known issue mentioned for Safari on MacOS. But I’ll talk about this later.

Brought up my Windows 7 VM.
All IE 11, Firefox’s latest public update, and Chrome just worked like a charm. I didn’t experience any issues like I faced previously on my Mac. “Save File” to Downloads folder and then a double-click, or “Open with” to open the applet immediately. And your Forms session is up. Look at the screenshot – CHROME!

Applet window is transparently opening.

The example below is from Workflow Activity Monitor using Firefox. No extra browser tab / window opened in my case, although there was a mention of known issue with Firefox.

Applet files downloaded are automatically cleared once the applet is loaded, you will not find them on the disk. Chrome, as an example, updates the status of each item under Downloads tab.

So again.. Windows platform is covered well. What about the rest?

Linux is not a certified platform for Oracle E-Business Suite end user desktops. Although I was successfully using it in my experience, and it should work, the Oracle team still is not testing and certifying it. You may use and play around it at your own risk, and it shouldn’t be the right production direction. I personally haven’t tested it yet for JWS, but my guess is that it will have the same problem as MacOS is having. Maybe a to-do for this blog post update at some point.

MacOS… Steven Chan’s blog post, as mentioned above, states that “Safari on MacOS” is not certified because of MacOS Gatekeeper security feature that is “making the Java Web Start user experience very challenging”. That’s fine. We know a workaround to go to System Preferences app and click on Open Anyway button, though it’s required every time we launch the Forms session.

But… we don’t even reach this point. In the example below, I will outline a sequence of “nuances” I faced. It will be based on Safari screenshots, as only Safari is officially certified on MacOS. Absolutely the same issues I faced in Firefox on MacOS, and in Chrome on same MacOS.

First – we are trying to open Forms, but just getting this familiar screen.

Why? Because the URL still goes to browser plugin mode – “/forms/frmservlet?config=browserMode&appletmode=…”

We can go and set the ICX_FORMS_LAUNCHER profile option to “/forms/frmservlet?config=jws” on Site level, as MOS note outlines.

This works, but it will be required every time we run AutoConfig, as the profile option is always being reset to just “/forms/frmservlet” on Site level, and Forms opening process is supposed to follow FND_ENABLE_JAVA_WEB_START value direction.

After the manual fix this is what’s happening. Safari is downloading the applet.

We launch it and face a known Gatekeeper issue (only now).

Applet is loaded, but not Forms.

I would expect Safari to cover this itself, but in my case I have Firefox configured as default Web browser in the system.
That caused Firefox tab to be opened (called by initial applet) and a second applet download to be requested.

Once the second applet is up, I finally get the Forms session running. Sort of, it’s a similar flow that is happening with a browser plugin, but it is just killing the user experience.
Initially second applet was blocked by same Gatekeeper, and I had to use the known workaround. But at a later testing MacOS is always blocking only the first applet while launching Forms, second – not anymore. Maybe, I suspect the issue is with a miss of jndi extension for the file.

I got the Forms running. But what a process it was…
And nothing is cleared. My Downloads folder is full of these fndservlet.jndi files downloaded.

To summarize

This is a great moment and long expected feature that got finally released by the Oracle EBS team. But is it ready?

I would say, if your company is following strict control standards what software is used, including the browsers, you are, most probably, using same IE on Windows – this feature will work well. Forms will work even with good old browser plugin mode, your current JRE 6 or JRE 7, and you shouldn’t care and rush with this implementation.
Same comments about Firefox ESR – it will work as before. But not for a long time period until next ESR release branch merge.

If your company is following BYOD approach, multiple platforms in use like MacOS, you seek for Chrome browser support – at this stage only Windows platform is covered well. Others – are not officially certified, including “Safari on MacOS”. You can still use it, but you need to accept the “head ache” process as described by the example above. And that’s not about Safari only, as other browsers, like Firefox and Chrome, behaves in the same way on MacOS.

And not all Apps DBAs will agree to manually fix ICX_FORMS_LAUNCHER every time AutoConfig is executed. Though there might be User level exceptions for a defined end user base list.

Categories: DBA Blogs

Partner Webcast – DevOps and Cloud Native Microservices on Oracle Cloud

DevOps is a culture, movement or practice that emphasizes the collaboration and communication of both software developers and other information-technology (IT) professionals while automating the...

We share our skills to maximize your revenue!
Categories: DBA Blogs

transpose columns to rows

Tom Kyte - Thu, 2017-04-27 05:26
Could you please help me about the below scenario : Two tables, One to Many Relation. I tried by pivot , but pivot can only be used with aggregate functions. master table : members mem_id mname ------ ----- 1 abc 2 xyz 3 qrs Det...
Categories: DBA Blogs

Index script

Tom Kyte - Thu, 2017-04-27 05:26
I have two databases with indexes for same column different. I want a script that compares these two databases and generates the rename index script for the target database. Can you provide me a sample? Need to join dba_indexes and dba_ind_columns fr...
Categories: DBA Blogs

Incremental stats on large partitioned table

Tom Kyte - Thu, 2017-04-27 05:26
we have our a reporting database which is being cloned from our production database daily. The source database is a mission critical database where we can't make any changes(can't set table preferences for stats gathering ). We have a range partition...
Categories: DBA Blogs

Need assistance on calling shell script from oracle triggers.

Tom Kyte - Thu, 2017-04-27 05:26
Hi Tom, REQUIREMENT: Need to send database report before and after database bounce. Test Case: 1) Created a scheduler job. <code> BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'RUN_CHECK_REPORT', schedule_name => 'HEALTHCHE...
Categories: DBA Blogs

TABLE Function for custom type

Tom Kyte - Thu, 2017-04-27 05:26
Hello , I have this collection: a nested table built from a custom record that holds three varchars fields, and two numeric ones. The collection is filled with data inside a procedure. I need to sort that collection and I also need to choose...
Categories: DBA Blogs

Date in a nearest leap year

Tom Kyte - Thu, 2017-04-27 05:26
I've got the problem to solve. To find nearest(next or previous) date of the event. It happens every leap year at the particular date. Let's say 1st of May. How to generate such dates and get the nearest of them? Thank you. Our request for more ...
Categories: DBA Blogs

Get Primary Keys of Affected Rows in Update Statements

Tom Kyte - Thu, 2017-04-27 05:26
Question is simple Is there any way where I can get primary keys of a table which have been updated by update statements ? By doing that I want to leverage a select for an update Example There is a table EMPLOYEE with EmployeeID,EmployeeNam...
Categories: DBA Blogs

Big Data SQL Cloud Service now Available!

Oracle Big Data SQL Cloud Service has been released! Big Data SQL is Oracle’s breakthrough approach to simplifying access to big data sources. Oracle Big Data SQL provides the ability to query...

We share our skills to maximize your revenue!
Categories: DBA Blogs

truncate table reuse storage

Tom Kyte - Wed, 2017-04-26 11:26
Hi Tom, I am getting to do same action why? TRUNCATE TABLE xyz where to_date('12/30/1899', 'MM/DD/YYYY') + dtimestamp < to_date('01/01/2017','MM/DD/YYYY') REUSE STORAGE; still getting messages like Error report: SQL Error: ORA-03291: I...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs