Feed aggregator

Distributed and in-doubt transactions

Tom Kyte - 1 hour 3 min ago
Hello Ask Tom, Team. I am struggling with distributed transactions. Oracle RAC 18c (18.6) and .NET app. I want to know a few things: The RECO background process of an Oracle Database instance automatically resolves failures involving distri...
Categories: DBA Blogs

Uploading files in a Rest webservice using PlSql UTL_HTTP

Tom Kyte - 1 hour 3 min ago
Hi, I'm calling REST web services using PLSQL UTL_HTTP package, in JSON, with no problems. However, now i need to invoke a WS but attaching a file. I can do it with Postman in ?form-data? mode. In PLSQL, I've tried the same thing in the...
Categories: DBA Blogs

OGG-02030 Failed to set logmining server parameters back to default val

Tom Kyte - 1 hour 3 min ago
I`ve try to start integrated Extract: <code>REGISTER EXTRACT EXTR_R DATABASE ADD EXTRACT EXTR_R, INTEGRATED TRANLOG, begin now ADD EXTTRAIL ./dirdat/lt, EXTRACT EXTR_R START EXTRACT EXTR_R</code> with param file: <code>Extract EXTR_R USERIDA...
Categories: DBA Blogs

What happens when there is no space in 1 diskgroup in ASM?

Tom Kyte - 1 hour 3 min ago
Hi, We have DB that is using ASM with normal redundancy. The space consumption is really high. There was an occasion when almost 1 TB space was consumed in a month. We have been adding disks when there the remaining space gets really low. Now, we ...
Categories: DBA Blogs

500 internal server error

Tom Kyte - 1 hour 3 min ago
We have an application in Oracle APEX where we do not have a large database and the number of pages used in the application would be max 250. However, there is a 500 Internal Server error that we are encountering since the last 3-4 days and we have n...
Categories: DBA Blogs

Analytic Functions Drawbacks

Tom Kyte - 1 hour 3 min ago
Hi Tom, Analytic functions are really cool, and I can see great benefits from using it. Despite its benefits, does Analytics Functions has any drawbacks, like performance issues, or any details one should be aware of when choosing to use analytics...
Categories: DBA Blogs

Oracle Ramps Up Free Online Learning and Certifications for Oracle Cloud Infrastructure and Oracle Autonomous Database

Oracle Press Releases - Mon, 2020-03-30 12:00
Oracle Ramps Up Free Online Learning and Certifications for Oracle Cloud Infrastructure and Oracle Autonomous Database

By Raghu Viswanathan, Vice President of Education Products and Delivery, Oracle University—Mar 30, 2020

Juergen LindnerI’m happy to announce that Oracle is offering free access to online learning content and certifications for a broad array of users for Oracle Cloud Infrastructure and Oracle Autonomous Database. This program kicks off on Monday, March 30, 2020, and will be available until May 15, 2020.

Starting now, any user, including developers, technical professionals, architects, students and professors, will have quick and easy access to more than 50 hours of online training and 6 certification exams.

Oracle University will provide users with free access to an extensive library of materials for Oracle Cloud Infrastructure and Oracle Autonomous Database, as well as content related to topics such as data science, machine learning, and multi-cloud environments such as integration with Microsoft Azure.

We’re making high-quality videos, access to subject matter experts, and recorded demos of hands-on labs available anywhere, anytime so IT pros can accelerate their learning and gain highly sought-after skills. Machine translation (sub-titles only) will also be available for Chinese, Japanese, Korean, Portuguese, and Spanish speaking countries for all the videos.

As our customers adapt to a rapidly evolving digital landscape, Oracle is stepping up its efforts to help build critical technical cloud skills they need to ramp up innovation. We believe that certifications help professionals develop in-demand skills, shorten turnaround times for customer projects, enhance their expertise and advance their careers, while improving their overall job performance. 

Start learning and get certified in three easy steps:

Step 1: Get a free Oracle Single-Sign-On (SSO) account. If you have an SSO, skip to Step 2

  • Click on Hello. Log In. (See far right corner of the page).
  • Select “Create an account” and fill in the required fields.
  • Verify account via email.

Step 2: Access learning paths through the links below:


Step 3: Register and take your free certification exam. If you have a PearsonVue account, log in.

  • If not, choose “Create a web account” on the right side of the page and follow instructions.

This initiative builds on a recent Oracle announcement of free training resources for cloud developers. Learn more about Oracle University and other training available.

Raghu Viswanathan is Vice President of Education Products and Delivery for Oracle University. He joined Oracle as a software engineer in 1990 and led Operations, Marketing and Sales Readiness before taking on his current role overseeing all Content, Delivery, Certification, Renewals, IT, and Development.

January 2020 Patches Not In Recommended Patch Advisor

Bobby Durrett's DBA Blog - Mon, 2020-03-30 10:58

Every quarter I download the latest patch set from Oracle’s support site by using the Recommended Patch Advisor. But the January 2020 patch sets did not show up. The RPA still recommends the October 2019 patch sets. The Recommended Patch Advisor is one of the options under the Patches and Updates tab in Oracle’s support site. I tried searching Oracle’s support site and Googling but did not find any answers. I am sure that the information is out there somewhere but the way I was searching for it did not find it. So, I thought I would post a quick note about it. The short answer is that you should use this Oracle support document to find the latest patch sets and not the Recommended Patch Advisor:

Master Note for Database Proactive Patch Program (Doc ID 756671.1)

To find this out I had to open an SR with Oracle Support. They quickly identified this document which explained the situation:

My Oracle Support: Patch and Health Recommendations No Longer Supported (Doc ID 2644769.1)

Note 2644769.1 seems to say that the Recommended Patch Advisor will continue to refer to the October 2019 patches until Oracle removes the RPA from their support site in May.

The funny thing is that I knew we had an internal document that a coworker and I had both contributed to that describes how to download the latest patch set and apply it to a database to create a tar or zip that we could use to deploy a new database. I decided to modify that document to refer to note 756671.1 and remove references to the RPA. To my surprise my coworker had already included note 756671.1 months ago in our instructions and not the RPA. I think that Oracle support must have recommended the note some time ago, but I was still using the advisor. I guess you learn something new every day.

Anyway, note to self: don’t use the Recommended Patch Advisor to find the quarterly patch updates. Use note 756671.1.


Categories: DBA Blogs

Add A SQL*Net Security Banner And Audit Notice

Pete Finnigan - Mon, 2020-03-30 09:46
I would have to say whilst I see security banners on customers Unix boxes when I am allowed to log in as part of a security audit I canot ever remember seeing a security banner when I log into a....[Read More]

Posted by Pete On 30/03/20 At 02:02 PM

Categories: Security Blogs

Ethiopian calendar support

Tom Kyte - Mon, 2020-03-30 09:46
In oracle databse 12c it says it suppirt ethiopian calander.in my database I have one employee table in that I have one column hiredate which date data type until i want to insert ethiopian calander date type data how it is possible?
Categories: DBA Blogs

ADD_MONTHS Function returning last day of the month

Tom Kyte - Mon, 2020-03-30 09:46
The question I have is regarding the ADD_MONTHS function. The live sql link for the test case is as below:- https://livesql.oracle.com/apex/livesql/s/JT1FDGOHXMRWN3LG94GEYLMPJ If we have a look at this link it has 3 components:- 1. A function ...
Categories: DBA Blogs

UTL_FILE write and User permission in Unix directory

Tom Kyte - Mon, 2020-03-30 09:46
I am using UTL_FILE.FOPEN('Unix_Directory_path','FileName.txt','w') in my Pl/Sql package and the file is getting written successfully but the User and permission are different than the file generated using SPOOL, both are using the same batch file in...
Categories: DBA Blogs

Combine create and insert with if-then or case-when in sql?

Tom Kyte - Mon, 2020-03-30 09:46
Hello people, I would like to ask for your help with a simple issue. I am srry if this is too common, but for me it is not easy at all: I have a table like this - p.sometable: <code> ID AMOUNT YEAR cusomter1 42476281.61 ...
Categories: DBA Blogs

No data found extracting information from xml

Tom Kyte - Mon, 2020-03-30 09:46
Why can't you find data in an XML when I have proof of its existence?
Categories: DBA Blogs

Index Rebuild for just 1 partition

Tom Kyte - Mon, 2020-03-30 09:46
Hi, We have partitions in our table. We want to build index for just one partition. If we do that, then does it affects the application? One time we got an error like index in unusable state. First we create index in unusable state like: <code>C...
Categories: DBA Blogs

Paginated queries

Tom Kyte - Mon, 2020-03-30 09:46
Bello, Ask Tom Team. I need to build a report that connects to a 18c Oracle database. There are big tables and I need to paginate the results. I want to show 50 rows per page in the report. I have read about Fetch, Offset features since Ora...
Categories: DBA Blogs

Oracle Continues to Invest in Canada; Opens Second Canadian Cloud Region in Montreal

Oracle Press Releases - Mon, 2020-03-30 07:00
Press Release
Oracle Continues to Invest in Canada; Opens Second Canadian Cloud Region in Montreal Unique dual region strategy helps organizations with disaster recovery and compliance requirements

Montreal—Mar 30, 2020

To support growing customer demand for enterprise cloud services in Canada, Oracle today announced a new Gen 2 Cloud region in Montreal. This follows the launch of its Toronto Cloud region last year, making Canada Oracle’s latest country offering dual cloud regions. The opening is part of Oracle’s global plans to operate 36 Generation 2 Cloud regions by the end of 2020. Now, Canadian customers and partners will have access to all Oracle Cloud services, including Oracle Autonomous Database and Oracle Autonomous Linux, as well as Oracle Cloud Applications, to unlock innovation and drive business growth.

Oracle’s unique dual region strategy enables customers to deploy resilient applications in multiple independent cloud regions for disaster recovery—without having sensitive data leave the country that could create issues with regulatory requirements around data sovereignty as well as operational issues associated with operating in multiple countries. 

Oracle continues to make significant investments in Canada to support a fast growing number of customers and partners. Today’s news follows the recent announcement that Oracle and Microsoft expanded their cloud interoperability partnership to help joint customers worldwide run their mission-critical workloads across Oracle Cloud and Microsoft Azure. The Interconnect location in Canada means enterprises can now build workloads that seamlessly interoperate between Microsoft and Oracle cloud regions in Canada.

“Oracle’s Canadian customers and partners are at the forefront of cloud innovation. Our investment in the region reflects unparalleled demand for our Gen 2 Cloud, built to run customers’ most important business applications securely,” said Don Johnson, executive vice president, product development, Oracle Cloud Infrastructure. “The new Montreal Region coupled with the Toronto Region enables Oracle to provide multi-region deployments in country to address Canadian organizations’ disaster recovery and compliance requirements.”

In addition to the Montreal cloud region, Oracle recently announced the opening of four Gen 2 Cloud Regions in Melbourne (Australia), Jeddah (Saudi Arabia), Osaka (Japan) and Amsterdam (the Netherlands). Oracle has now opened 17 Gen 2 Cloud regions in the past year and currently operates 21 regions globally—16 commercial and five government—the fastest expansion by any major cloud provider. By the end of this year, the company plans to open additional new cloud regions in the US (San Jose, CA), Brazil (Vinhedo), the UK (Newport, Wales), India (Hyderabad), South Korea (Chuncheon), Singapore, Israel, South Africa, Chile (Santiago), Saudi Arabia and two in the United Arab Emirates.

Currently available Oracle Gen 2 Cloud regions include:

  • Asia Pacific: Tokyo, Osaka, Seoul, Mumbai, Sydney, Melbourne
  • Americas: Phoenix, Ashburn, Toronto, Sao Paolo, Montreal
  • Europe: Frankfurt, London, Zurich, Amsterdam
  • Middle East: Jeddah
  • Government: two general U.S. Government regions, three U.S. Department of Defense specific Government regions

Oracle’s Gen 2 Cloud

Oracle’s modern, second-generation cloud is built and optimized specifically to help enterprises run their most demanding workloads securely. With unique architecture and capabilities, Oracle Cloud delivers unmatched security, performance, and cost savings. Oracle’s Generation 2 Cloud is the only one built to run autonomous services, including Oracle Autonomous Linux and Oracle Autonomous Database, the industry's first and only self-driving database. Oracle Cloud offers a comprehensive cloud computing portfolio, from application development and business analytics to data management, integration, security, artificial intelligence (AI), and blockchain.

Oracle recently introduced Oracle Cloud Free Tier, Always Free services for anyone to try the world’s first self-driving database and Oracle Cloud Infrastructure for an unlimited time. Now, organizations large and small, developers, students, and educators can build, learn, and explore the full functionality of Oracle Autonomous Database and Oracle Cloud Infrastructure, including Compute VMs, Block and Object Storage, and Load Balancer—all of the essentials for developers to build complete applications on Oracle Cloud. 

Canadian Customers and Partners Ready for New Oracle Montreal Cloud Region

Organizations across Canada are using Oracle to run their mission-critical workloads.

Groupe Dynamite is a global fashion retailer with two brands at the heart of its success, Garage and Dynamite. They operate more than 350 stores throughout Canada and 32 in the United States, and employ over 5,000 people. The company chose Oracle Cloud to support enterprise mission-critical workloads so they can continue as a leader among international brands.

“Groupe Dynamite is happy to see that Oracle is investing in Canada with a second cloud region in Montreal. This will enable us to centralize our critical workloads in the two Canadian cloud regions in the near future,” said Daniel Marcotte, Senior IT Director at Groupe Dynamite. “It is critical to maintain 100 percent uptime with cloud vendors and this new data center in Montreal will offer us that possibility without forcing us to store sensitive data outside of Canada.”

Combining career and professional skills training with theoretical knowledge, Seneca provides a polytechnic education to 30,000 full-time and 60,000 part-time students. With campuses in Toronto, York Region and Peterborough and education partners around the world, Seneca offers degrees, graduate certificates, diplomas and certificates in more than 300 full-time, part-time and online programs.

“Seneca College delivers world class post secondary education to Canadian and International students. Ensuring we achieve maximum availability is essential to our overall mission as a leading educational institution,” said Radha Krishnan, Chief Information Officer, Seneca College. “We are excited to have Oracle launch its Montreal Cloud region, as it will give us the ability to build a highly available multi-region application deployment in Canada that will help us best serve our students and other stakeholders.”

Oracle PartnerNetwork (OPN) is Oracle's partner program that provides partners with a differentiated advantage to develop, sell and implement Oracle solutions. OPN offers resources to train and support specialized knowledge of Oracle’s products and solutions and has evolved to recognize Oracle’s growing product portfolio, partner base and business opportunity. Key to the latest enhancements to OPN is the ability for partners to be recognized and rewarded for their investment in Oracle Cloud. Partners engaging with Oracle will be able to differentiate their Oracle Cloud expertise and success with customers through the OPN Cloud program—an innovative program that complements existing OPN program levels with tiers of recognition and progressive benefits for partners working with Oracle Cloud.

Investing in Canada

Oracle is investing in the skill set in Canada through Oracle University. Oracle University is the trusted provider of Oracle Cloud and on-premise software training and certification. All training is delivered by Oracle’s elite global team of experts and is made available in multiple learning formats for anytime, anywhere training.

In addition, Oracle is supporting the next wave of entrepreneurs via its Oracle for Startups program which is a unique acceleration program that enables mutually beneficial business-building partnerships for startups, our customers, and Oracle. The program creates a virtuous cycle of innovation by blending startup ingenuity with enterprise resources to deliver transformative solutions to customers. It starts with free cloud and expands to a rich collaboration with opportunities to engage with Oracle’s vast network of mentors, product experts, and customers.

Contact Info
Nicole Maloney
Oracle PR
About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Nicole Maloney

  • +1.650.506.0806

Web Application Firewall (WAF) In Oracle Cloud (OCI)

Online Apps DBA - Mon, 2020-03-30 05:03

Oracle Web Application Firewall (WAF) service is used to enhance and secure its Oracle Cloud Infrastructure offerings. Do you really want to know how to apply WAF on Application using OCI? If yes, then check out K21academy’s blog post at https://k21academy.com/1z099713 which covers: • Overview On WAF In OCI • How Does WAF Work • […]

The post Web Application Firewall (WAF) In Oracle Cloud (OCI) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Next/Previous Record: how APEX does it

Jeff Kemp - Sun, 2020-03-29 22:14

It’s not a common requirement in my experience, but it does come up every now and then: a customer is happy with a simple Report + Form but when they open a record, they want to be able to make their changes and go to the “next” record in one button click, instead of having to go back to the report and select the other record.

In the Sample Database Application, page 29 (Order Details) implements “Next” and “Previous” buttons which allow the user to save and open another record in one action.

These buttons are implemented using a legacy Form Pagination process. This process sets some hidden items (P29_ORDER_ID_NEXT, P29_ORDER_ID_PREV, P29_ORDER_ID_COUNT) based on a query on a specified table (DEMO_ORDERS) with a specified unique identifier (ORDER_ID) associated with a primary key item (P29_ORDER_ID). The process requires a navigation order, specified by one or two columns (ORDER_ID, in this instance) in order to know what would the “next” and “previous” records be. If there is no next or previous record is found, the buttons are hidden.

When the page is submitted, the Next and Previous buttons submit a request (GET_NEXT_ORDER_ID or GET_PREVIOUS_ORDER_ID, respectively). After the ApplyMRU process has run, one of the relevant Branches will redirect the client back to the same page (p29) and set P29_ORDER_ID to either &P29_ORDER_ID_NEXT. or &P29_ORDER_ID_PREV. which causes the page to load the relevant record.

Some things to note with this approach:

  1. The legacy Form Pagination process is limited to a maximum 2 columns for the uniqueness constraint, and 2 columns for the navigation order.
  2. The navigation order of records will not match any custom sort order or filtering the user might have used on the report; so after the user opens the “first record” in the report, the form will not necessarily navigate to the “next record” that they might expect.
  3. The Next / Previous record IDs are queried when the page is initially loaded, so if anything has changed prior to the user clicking “Next” or “Previous”, it’s possible the user will inadvertently be directed to a record that is not actually “next” or “previous” to the record as it is now.
    In the worst case, if someone had deleted a record, the page would show “record not found”.
    In a perhaps less problematic case, if someone else has just inserted a new record with a unique identifier that happens to fall between the user’s previous record and the record they are navigating to, the user will effectively “skip over” the newly inserted record and might be led to believe it doesn’t exist.
  4. For large datasets there may be a performance penalty whenever each user loads the form since the page must issue additional queries to find the IDs for the “previous smaller” and “next larger” record, as well as to get the total number of records, and the position in the overall dataset of the current record (so it can set the “count” item to something like “8 of 10”). This appears to involve the execution of three separate queries (in addition to the original query which gathered the data for the record being viewed).

These are not necessarily insurmountable or showstopping issues but should be kept in mind for forms using this approach.

With the new APEX Form feature, the above approach can still be used in much the same way – the attribute settings are a little different.

The page has a process before header of type Form – Initialization. This process has the following optional settings: Next Primary Key Item(s), Previous Primary Key Item(s), and Current Row/Total Item. To use this feature you must first create items (usually hidden) and then set these attributes to the item names.

Note that the Next / Previous Primary Key Item(s) attributes accept a comma-delimited list of items which allows them to support a compound key; I haven’t tested it but I expect this means it can support more than 2 columns.

The form will automatically populate these hidden items with the Order ID of the next and previous record, and will set the Current Row/Total Item to something like “8 of 10”. You can then use these items how you wish, e.g. as per the legacy pagination scheme, add the “Next” and “Previous” buttons, and create the navigation Branches to open the form with the relevant records.

To control the navigation order, you would set the Order By attribute on the form region.

It should be noted that the comments above about how concurrent record inserts and deletes by other users, and about report filters and sorting, also apply to the new form process.

In the past I built a system where it was important that the “Next” / “Previous” buttons should allow the user to navigate up and down the records exactly as shown in the report, respecting user-entered filters and sort order. The approach I took was to gather the IDs into a collection and pass this to the form when the user opened a record. I described the implementation and limitations of this approach in an older blog post which I expect still works today: Next/Previous buttons from Interactive Report results


Subscribe to Oracle FAQ aggregator