Feed aggregator

Pivot on a query with multiple data columns

Tom Kyte - Thu, 2019-02-07 19:06
I have an SQL query written against view sys.dba_audit_session to retrieve initial logon and final logoff times for users across a range of days. So the basic query is <code> SELECT DISTINCT username, action_name, ...
Categories: DBA Blogs

Parsing through a Long Character with 255 characters and Stripping out Words

Tom Kyte - Thu, 2019-02-07 19:06
Hi, I have a long character field for every one of my 10,000 rows and I need to write a PL/SQL that goes through every row a parses this long field for any WORD (Mix of Alpha and Numeric) up to 6 characters and spits it out and Save it to a differ...
Categories: DBA Blogs

Generating output file using spool is very slow when compared to UTL_FILE

Tom Kyte - Thu, 2019-02-07 19:06
Hello Team, We have to extract huge data from EBS tables. We have tried two approaches Approach1 (Using SQL and Spool command) --------- We have created a script to generate the output using SPOOL command but this is taking 12 hours of time ...
Categories: DBA Blogs

Identify when a function is executed in a SQL Query or in a PL/SQL procedure

Tom Kyte - Thu, 2019-02-07 19:06
Is there any way to identify when a pl/sql function is executed in SQL Query and when is executed in a procedure or PL/SQL anonymous block? (I don't want to pass any parameter for manual identification) The main reason I need that is when a functi...
Categories: DBA Blogs

How to convert ROWS to COLUMNS

Tom Kyte - Thu, 2019-02-07 19:06
Hi, I have data as given below in table <code>create table chart( SERIES varchar2(10), LABEL date, VALUE number(5,2) ); insert into chart values('A',to_date('13-DEC-18','DD-MON-YY'),83.55); insert into chart values('B',to_date('13...
Categories: DBA Blogs

Impact on EBS of Browser Desupport of TLS 1.0 and TLS 1.1

Steven Chan - Thu, 2019-02-07 09:31

Many browsers (including Firefox, Chrome, Internet Explorer and Safari) will soon no longer support either TLS 1.0 or 1.1.  Most likely, many of you have already migrated from SSL to TLS for Oracle E-Business Suite 12.2 and 12.1: however, you should also make certain that you have configured Oracle E-Business Suite properly for TLS 1.2.

What Do You Need to Do?
  1. Understand desupport notices for TLS 1.0 and TLS 1.1 for browsers used by your organization. 
  2. Follow our documentation to meet the requirements for using TLS 1.2 with Oracle E-Business Suite Release 12.2 or 12.1:

You have the option to configure Oracle E-Business Suite to work with TLS 1.2 only, or to be backward compatible with TLS 1.2, 1.1, and 1.0.  Either of these configurations will allow Oracle E-Business Suite to function properly with browsers that have desupported TLS 1.0 and 1.1. 

Related Articles References
Categories: APPS Blogs

[Blog] High-level Steps to Protect an On-premises Application with IDCS by Using WebGate

Online Apps DBA - Thu, 2019-02-07 04:02

WebGate can now interact with Oracle Identity Cloud Service: Protect the on-premises web application by using cloud service to authenticate users to access the web application. Visit: https://k21academy.com/idcs14 to know about How to Protect an On-Premise Application with IDCS by Using Webgate. This Include: ✔ Why IDCS to protect an On-Premise Applications ✔ How does […]

The post [Blog] High-level Steps to Protect an On-premises Application with IDCS by Using WebGate appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Partner Webcast – Connect and Extend SaaS with Oracle Autonomous Integration Cloud Service

Oracle Autonomous Integration Cloud helps to simplify application integration and process automation to let you rapidly connect SaaS and on-premises application across, both Oracle and non-Oracle,...

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

Index creation slow - lots of direct path write/temp waits

Tom Kyte - Thu, 2019-02-07 00:46
I have a table on which I'm trying to index a column. This happens as part of an ETL process, however I have run this process directly to reproduce. The table in question has about 15M rows and I am trying to index a single column. create index x...
Categories: DBA Blogs

Created file using UTL_FILE. How to prevent file from being picked up before it has been fully created

Tom Kyte - Thu, 2019-02-07 00:46
We have an integration where the file is being created in oracle ebs using utl_file utility. The file is placed on the server where biztalk pings every 60 sec and picks it up. In one scenario the file was picked up before it was written out resulting...
Categories: DBA Blogs

When are partition statistics beneficial?

Tom Kyte - Thu, 2019-02-07 00:46
Tom, When are partition-level statistics used by the optimizer in 9iR2 and 10g? The only thing I've found in the docs so far (trying to avoid RTFM) is that they're used when a query fit the form SELECT ... FROM MYTABLE PARTITION (PTN_NAME). Specif...
Categories: DBA Blogs

oracle database parameters

Tom Kyte - Thu, 2019-02-07 00:46
Dear Tom, I'm trying to test the oracle 12C reporting system, in which i will dump the oracle database transaction database backup to report system. For better performance of completed reporting system, what are the parameters i need to set perfe...
Categories: DBA Blogs

GROUP BY returns incorrect number of rows

Tom Kyte - Thu, 2019-02-07 00:46
Hi A bit of an odd one - possibly a bug. <code>DROP TABLE xxibpc_test; CREATE TABLE xxibpc_test AS WITH data AS (SELECT LEVEL l_no FROM dual CONNECT BY LEVEL <= 10000) SELECT 1 instance_number ,1 + l_no session_id ,10...
Categories: DBA Blogs

Special Tables in OUAF based products

Anthony Shorten - Wed, 2019-02-06 22:47

Long time users of the Oracle Utilities Application Framework might recognize two common table types, recognized by their name suffixes, that are attached to most Maintenance Objects within the product:

  • Language Tables (Suffix _L).  The Oracle Utilities Application Framework is multi-lingual and can support multiple languages at a particular site (for example customers who have multi-lingual call centers or operate across jurisdictions where multiple languages are required). The Language table holds the tags for each language for any fields that need to display text on a screen. The Oracle Utilities Application Framework matches the right language records based upon the users language profile (and active language code).
  • Key Tables (Suffix _K). These tables hold the key values (and the now less used environment code) that are used in the main object tables. The original use for these tables was for key tracking in the original Archiving solution (which has now been replaced by ILM). Now that the original Archiving is not available, the role of these tables changed to be used in a number of areas:
    • Conversion. The conversion toolkit in Oracle Utilities Customer Care and Billing and now in the Cloud Service Foundation, uses the key table for efficient key generation and black listing of identifiers.
    • Key Generation. The Key generation utilities now use the key tables to quickly ascertain the uniqueness of a key. This is far more efficient than using the main table for this, especially with caching support in the database.
    • Information Life-cycle Management. The ILM capability uses the key tables to drive some of its processes including recognizing when something is archived and when it has been restored.

These tables are important for the operation of the Oracle Utilities Application Framework for all types of parts of the product. When you see them now you understand why they are there.

Spring Cloud GCP and authentication from your Spring Boot Application

Pas Apicella - Wed, 2019-02-06 17:18
When using Spring Cloud GCP you will need to authenticate at some point in order to use the GCP services. In this example below using a GCP Cloud SQL instance you really only need to do 3 things to access it externally from your Spring Boot application as follows.

1. Enable the Google Cloud SQL API which is detailed here


2. Ensure that your GCP SDK can login to your Google Cloud SQL. This command will take you to a web page asking which google account you want to use

  $ gcloud auth application-default login

3. Finally some application properties in your Spring Boot application detailing the Google Cloud SQL instance name and database name as shown below.


Now when you do that and your application starts up you will see a log message as follows below clearly warning you this this method of authentication can have implications at some point.

2019-02-07 09:10:26.700  WARN 2477 --- [           main] c.g.a.oauth2.DefaultCredentialsProvider  : Your application has authenticated using end user credentials from Google Cloud SDK. We recommend that most server applications use service accounts instead. If your application continues to use end user credentials from Cloud SDK, you might receive a "quota exceeded" or "API not enabled" error. For more information about service accounts, see https://cloud.google.com/docs/authentication/.

Clearly that's something we have to resolve. To do that we simply can add another Spring Boot application property pointing to a service account JSON file for us to authenticate against to remove the warning.


Note: You can also use an ENV variable as follows


You can get a JSON key generated from the GCP console "IAM and Admin -> Service Accounts" page

For more information on authentication visit this link https://cloud.google.com/docs/authentication/getting-started

Categories: Fusion Middleware

Oracle E-Business Suite and Applications Unlimited Continued Oracle Investment—No Upgrades

Chris Warticki - Wed, 2019-02-06 13:13
Continued Oracle Investment for Years to Come

In June 2018, Oracle published the “Applications and Support—an Unbeatable Combination” announcement. This message re-affirmed Oracle’s commitment to the Applications Unlimited products with continuing development and support through at least 2030. The products covered by  Applications Unlimited are Oracle E-Business Suite, PeopleSoft, JD Edwards EnterpriseOne, and Siebel.

The June announcement committed Oracle E-Business Suite Development to move to the continuous innovation release model for the next major release (identified as 12.X), as explained in the Oracle E-Business Suite publication.

  Continuous Innovation Release Announced

In the subsequent October 2018 announcement, Oracle E-Business Suite announced that we will implement continuous innovation on the current 12.2 release. 


Significance to Oracle E-Business Suite Customers

This is important to Oracle E-Business Suite customers because the announcement:

  • Restates Oracle’s commitment to Oracle E-Business Suite.
  • Clarifies that the inter-dependency of the applications and technology stack has been uncoupled, and it will be possible to perform “tools only” updates (affecting the application layer), without being forced to update the technology stack at the same time, or conversely, tech stack updates without updating the applications.
  • Means it will be easier for customers to remain current—on the continuous innovation release—and benefit from the latest developments.
  • Removes the justification for 12.1.3 users to delay upgrading, as once they have migrated to 12.2, they can remain on that release for at least through 2030.
  • Sets the record straight—responding to/countering some of the misinformation and rumors, which have been circulated, stating that Oracle E-Business Suite will not be developed or supported.


Oracle Applications Unlimited

With continuous innovation now available for Oracle E-Business Suite 12.2, Oracle E-Business Suite joins the other Applications Unlimited products that also have pledged to provide continuous innovation and Oracle Premier Support through at least 2030 for their current continuous innovation releases.



Committed to Customers | Committed to Innovation | Committed to Products


Learn More



Replacements for OrdDoc

Tom Kyte - Wed, 2019-02-06 06:26
I have been using OrdDoc to store files and attachments for my applications. It looks like Oracle has deprecated this and will no longer support it in its next release (https://mikedietrichde.com/2018/08/10/oracle-multimedia-is-deprecated-in-oracle-1...
Categories: DBA Blogs

CDC on Views

Tom Kyte - Wed, 2019-02-06 06:26
Hi Tom, I have a view created daily on a detailed table set ( n number of tables based on the load) as below CDR_Detail_20190204_view CDR_Detail_20190205_view CDR_Detail_20190206_view To generate a report , i need to pick all the daily views(...
Categories: DBA Blogs

Can I force query to use the varchar index if the condition uses a number value

Tom Kyte - Wed, 2019-02-06 06:26
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator