Feed aggregator

Prompt Engineering with Oracle DBA - PromptOps

Pakistan's First Oracle Blog - Sat, 2023-03-25 22:15

 Prompt Engineering means to teach smart AI software how to respond to people's queries. The objective of Prompt engineering involves writing a prompt that gets you your desired result. You can use prompt engineering with Oracle DBA too.

Categories: DBA Blogs

Learn AI and Data Science for Free

Pakistan's First Oracle Blog - Fri, 2023-03-24 23:07

 If you are a beginner and want to start your learning journey in AI and Data Science then this video is a good starting point.

1-Learn Python the Right Way by Peter Wentworth, Jeffrey Elkner, Allen B. Downey, and Chris Meyers. https://i.ritzastatic.com/learn-python-the-right-way/learn-python-the-right-way.pdf 2- Free SQL Courses https://www.udemy.com/topic/sql/free/ 3-Automate the Boring Stuff with Python by Al Sweigart https://automatetheboringstuff.com/ 4-Introduction to Machine Learning by Alex Smola and S.V.N. Vishwanathan https://alex.smola.org/drafts/thebook.pdf 5-Deep Learning by Ian Goodfellow, Yoshua Bengio, and Aaron Courville https://www.deeplearningbook.org 6-Natural Language Processing with Python by Steven Bird, Ewan Klein, and Edward Loper. https://www.nltk.org/book/ 7-40 days of AI https://www.linkedin.com/pulse/40-days-ai-steve-nouri/

Categories: DBA Blogs

Subtracting months frorm a current date

Tom Kyte - Fri, 2023-03-24 22:46
I am trying to subtract 6 months from the current date (SYSDATE). I know about the ADD_MONTH function, but I haven't been able to find documentation about a "SUBTRACT_FUNCTION" which can do this for me.
Categories: DBA Blogs

ORA-28864: SSL connection closed gracefully

Tom Kyte - Fri, 2023-03-24 22:46
I did exact thing as per this link https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections But I am always getting the below error. ORA-28864: SSL connection closed gracefully I am using free version of 21C database
Categories: DBA Blogs

Fluid Page Naming Conventions

Jim Marion - Thu, 2023-03-23 13:47

Best practices are an essential part of our curriculum. When creating Fluid pages, we recommend the following page name pattern:


For example, when creating a Fluid subpage to manage widgets, we would name it JSM_WIDGETS_SBF. Here is a list of page-type suffixes derived from Oracle-delivered pages:

Fluid Page
Subpage Fluid
Secondary Page Fluid
Side Page (1 or 2)
Footer Page
Layout Page

But four more page types aren't used enough to have a suffix pattern: Header Page, Search Page, Prompt Page, and Master&Detail Target Page. For those page types, we've come up with our own suffixes:

Header Page
Search Page
Prompt Page
Master&Detail Target Page

For the most part, the convention is easy to understand. First initial of page type, and then FL. But what about SRF? Where did that come from? There are four page types that start with the letter S. Adding the R after the S looked more like Search than any of the other options we considered.

With Classic already having the suffixes SEC, SUB, and POP, we have suffixes for every page type except Classic standard pages. Should we, therefore, adopt _CL for Classic pages? PeopleSoft uses exception-based design. For example, the page bar is on until you turn it off, and the standard component toolbar is on until you turn it off. Changing these properties would be exceptions. Naming conventions are no different. Our naming conventions document the exceptions. With over 12,000 Classic pages in HCM, Classic is clearly the norm, and everything else is an exception.

What do you think? Do you have different naming conventions you use for page development? If so, share your ideas in the comments!

At JSMpros, we teach PeopleSoft Fluid training and best practices regularly. We look forward to hosting you in a future class!

How can I print a logo on a report as part of "Download" functionality?

Tom Kyte - Thu, 2023-03-23 10:06
We are using APEX 22.2.0. I have an interactive report on a page. I have added a logo to the page. I want the logo to display on the report when the user selects the "Download" option from the Actions menu. How can I get the logo to download with the rest of the report?
Categories: DBA Blogs

Is there any way for compatibility between .net Dll and oracle apex applications

Tom Kyte - Thu, 2023-03-23 10:06
is there any way to add DLL files contains .net functions (c# language) and use them in oracle apex application
Categories: DBA Blogs

Change the compatability mode in APEX

Mathias Magnusson - Thu, 2023-03-23 08:00

I ran across a funny thing today in an application I built 10 yesrs ago but that I have not been involved in maintaining since then. The application is gettings som much needed UI upgrades, like getting out of the dark ages and using universal theme.

During that work there were a set of things that didn’t work, components could nbot be found and some unexpected things happened. Unexpected compared to the behavior that had been experienced while doing a mock app in the private environment in the VM.

It turns out the application still has the compatibility mode set to 4.1. Yes, no wonder it was behaving different than when one has 22.x.

This is set during upgrade if upgrading from a version to another where the new has a change in behavior. You’ll want to look into what the change behavior is and make sure your application works well with it so it is on the latest version.

You find this setting in:

Application Properties -> Definition -> Properties -> Compatibility Mode

Oracle has a running lit of the changed behavior version by version in the documentation. That is great so one does not have to find the docs for each version that has changed behavior to find it. It is in one convenient place.


It goes through the changes for each version where the behavior of the APEX engine changed, starting with 4.1 all the way up to the current version. Very convenient to be able to read up on them and see what one should expect and what to check after changing this setting.

As evidenced by this setting in this application having been forgotten it is worth validating that applications you have upgraded has this set to the value you expect. For me I’d expect the latest unless the work to verify that it works as intended has not been completed.

AWS Generative AI Introduction

Pakistan's First Oracle Blog - Wed, 2023-03-22 21:45

 Generative AI is one of the biggest recent advancements in artificial intelligence technology because of its ability to create something new. It opens the door to an entire world of possibilities for human and computer creativity, with practical applications emerging across industries.

Categories: DBA Blogs

Huge Insert!

Tom Kyte - Wed, 2023-03-22 15:46
Hi Tom I am executing a query that fetches from a cursor first then does an insert based on the number of matching records as follows CREATE OR REPLACE PROCEDURE test AS Dt date; DelID Integer; LocID Integer; CURSOR c1 IS select po_id, po_dt, to_number(name) from Order, Location Where Order.id = Location.id; BEGIN OPEN c1; LOOP FETCH c1 Into DelID, Dt, LocID; EXIT WHEN C1%NOTFOUND; Insert into Results select DelID, NVal, prod_id, NULL, qty, decode(qty, 0, 0, cost/qty), 0, NULL, 1 from subs, Inv Where subs.id = LocID And v_dt = Dt And Subs.id = Inv.inv_id; COMMIT; END LOOP; CLOSE C1; END; / The subs table contains 128 million records Inv table contain 40000 records . Cursor c1 returns 1.3 million records. This insert has taken 3 days to insert just 4 million records and there are way more than 4 million records. Im thinking of using direct insert :Insert /* + Append */. Is this going to help me? I have another question on the placement of commit, I think in hte above query it is getting committed at every one insert , is this right thing to do? Also should I disable the Index on the Results tables and rebuild it later? Is there any other way to optimize the above operation? Thanks Steve
Categories: DBA Blogs

Invalid Java Class oracle/aurora/rdbms/Compiler

Tom Kyte - Wed, 2023-03-22 15:46
Using loadjava to upload a Java source results in: <code>ORA-29547: Java system class not available: oracle/aurora/rdbms/Compiler ORA-06512: at line 1</code> This usually indicates that you either have not installed the Java Virtual machine or you did not properly apply the latest OJVM PSU patch. However, latest patch was applied and the Java Virtual machine is actually running: opatch lsinventory: <code>Patch 34786990 : applied on Mon Jan 23 10:39:29 CET 2023 Unique Patch ID: 25032666 Patch description: "OJVM RELEASE UPDATE: (34786990)"</code> <code>select comp_id, comp_name, version_full from, status dba_registry; JAVAVM JServer JAVA Virtual Machine VALID</code> <code>SQL> select dbms_java.longname('TEST') from dual; DBMS_JAVA.LONGNAME('TEST') -------------------------------------------------------------------------------- TEST</code> <code>SELECT dbms_java.get_jdk_version JDK_Version FROM dual; JDK_VERSION -------------------------------------------------------------------------------- 1.8.0_361</code> And formerly uploaded Java 'Hello World' Code works perfectly: <code>SQL> select helloworld() from dual; HELLOWORLD() -------------------------------------------------------------------------------- Hello world </code> So to summarize, the Java VM ins installed, valid and actually running. It is just the compiler which isn't working. And in fact the respective Java Class ins invalid: <code>select owner, object_name, object_type, status from dba_objects where object_name = 'oracle/aurora/rdbms/Compiler'; SYS oracle/aurora/rdbms/Compiler JAVA CLASS INVALID</code> Trying to resolve the Java Class results in a segfault: <code>SQL> alter java class "oracle/aurora/rdbms/Compiler" resolve; alter java class "oracle/aurora/rdbms/Compiler" resolve * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 1239513 Session ID: 578 Serial number: 75</code> Alert Log: <code>ORA-07445: exception encountered: core dump [kglPinMask()+6] [SIGSEGV] [ADDR:0xA0] [PC:0x4BA9C26] [Address not mapped to object] []</code> So I am wondering what happened and how to solve this issue. Is there some way to get the Compiler working again? Do I have to follow the instructions to remove and reinstall the Java VM? Or does this not solve the Compiler issue? Any ideas are appreciated! Thanks in advance and all the best, Frank
Categories: DBA Blogs

Why Automation Matters : The cloud may not be right for you, but you still have to automate!

Tim Hall - Wed, 2023-03-22 05:39

A few days ago I tweeted this link to an article about some workloads being better suited to on-prem infrastructure. Jared Still sent me this link. The executive summary in both cases is, if you have defined workloads that don’t require elastic resource allocation, and you are not making use of cloud-only platforms, you might … Continue reading "Why Automation Matters : The cloud may not be right for you, but you still have to automate!"

The post Why Automation Matters : The cloud may not be right for you, but you still have to automate! first appeared on The ORACLE-BASE Blog.Why Automation Matters : The cloud may not be right for you, but you still have to automate! was first posted on March 22, 2023 at 11:39 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Parallel hint in oracle created more than double Entries in $sql

Tom Kyte - Tue, 2023-03-21 21:26
Hi Team, I have one query which quite complex and join multiple tables. Its Insert into table a (Select /*+ parallel(S,8) */ column A,column B from Table a,Table b {Business logic}) Now the issue is, when this query is running, I could see there are 17 entries in gv$SQL and gv$Session. Idelily it should only have 8 parallel queries running in gv$SQL as per my understandig. How come there are double the entries. Please note its RAC environment with 2 Instances. If I go through the real time SQL monitor, I could see PX COORDINATOR has 17 executions. and PX SEND and PX BLOCK operation has 8 execution. Is anything wrong here, because this query is running too slow.
Categories: DBA Blogs

You ought to have oracledb in your tool chest

Mathias Magnusson - Tue, 2023-03-21 08:00

No I’m not talking about the Oracle Database. It is extremely unlikely that you found your way here if it isn’t already to some extent your specialisation. If you made it here and have no idea what this is about, drop a comment and let me know your path here if that were to be the case.

No, this is about Python. Even more it is about the OracleDB module for python. That capitalisation is mine, the official way to write it is Python-oracledb. Either way it is Oracle’s implementation of the Python Database API 2.0 specification.

I know what some of you may be thinking. Why would I need that, I’ve got my old trusted cx_Oracle driver. Yes you do and you have nothing to fear, Python-oracledb is the new name for it. You’ll feel right at home.

Let’s start with recognising what a beautiful API. To me this is an API that is equally natural to use from Python and database perspectives. I think it is not just due to Oracle’s efforts but also for the general API specification that it follows. I often find the interface between language and SQL more natural in Python than even in PL/SQL. Yes, that is probably close to an offensive statement for some. But try it before you diss it.

Speaking of Python, if you too work with developers that mainly write code in Java, you will want to be able to write simple stubbs of code to access the database and achieve the things you want them to implement to help you do your job. Most of the time it is enough with some cookie cutter code and just add what you need.

I use Python the same way but for slightly different reasons. I use it to show myself a concept of the work one has to do when accessing the database from outside the database. It also includes things that makes many things easy to test out that for me is beyond cumbersome to set up in Java. In addition Python is a language you want to have in your toool chest anyway as it is the preferred language for data these days.

Let’s rig a small test. Begin with installing the module.

python -m pip install oracledb --upgrade --user

Test getting a connection with som boiler plats code.

import oracledb

with oracledb.connect(user='<USER>', password='<PASSWORD>', dsn='CONN STRING>') as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):

As one would expect it just prints the sysdate. However this blog is not about examples for coding with oracledb, rather I think it is worthwhile to just finish up with highlighting som of the features it has to show the kind of things you can try out with just a few more liens of code.

The plain way of using it uses the thin driver which mimics how most application servers connect. If you want to you can set up for using thick mode also. It is required for som advanced usage scenarios.

Some of the features I think are of particular interest:

  • Advanced Queueing including receiving database notifications.
  • Support for EBR
  • Supporting setting context when creating a connection to allow for better instrumentation in the database.
  • Support for E2E monitoring and tracing
  • Database Resident Connection Pooling
  • ref cursors
  • Array-operations
  • JSON

And the list goes on.

One final thing I find very interesting. It has an API to set up a connection pool so you can use it to model use cases for how a connection pool would behave. It also allows you to do things like build a REST-server with it’s own connection pool to service parallell requests to your REST-server.

Take it out for a spin, I’m sure you’ll enjoy it.


Tom Kyte - Tue, 2023-03-21 03:06
Hi First time asking a question here, I have tried to follow the guidelines, apologies for any errors on my part. Any help you can give will be gratefully received, even if it's just to point me in the right direction rather than an answer First a bit of context. We are in the process of removing technical debt and as such we performing a migration of data from customised tables back to the original base tables for the application where possible. As an interim measure, we are migrating the data and replacing the custom tables with views which point to the new storage locations of the data. The view synonyms have the same names as the custom tables that they replace so that downstream systems don't fall over. Once all interfaces and other dependent code have been migrated to use the new locations, these interim views will be dropped. One of these views has to take data which is now in a clob and produce a view which chunks the clob into 240 character strings to match how the data used to be stored producing <u>level</u> <u>text</u> 1 text string first 240 2 text string next 240 3 text string another 240 . . . I used the following select to produce that output (and I need the surrogate ID from tab_a) <code> select a_surrogate_id, v.lvl, v.chnk, from tab_a, (select subj, crse, term, to_char(regexp_substr(s, '.{240}', 1, lvl)) chnk, lvl from (select subj, crse, term, s, level lvl from (select b_subj_code subj, b_crse_numb crse, b_valid_from term, substr(b_required_materials,1,4000) s from tab_b) connect by level <= length(s) / 240) ) v where a_subj_code = v.subj and a_crse_numb = v.crse and v.term = (select max(sy.b_valid_from) from tab_b sy where sy.b_subj_code = v.subj and sy.b_crse_numb = v.crse and sy.b_valid_from <= a_valid_from) </code> which works, but is really, really slow (add an infinite number of reallys) and when I do a select from the view with a where clause, I have time to run a marathon before it comes back (and I run slowly). The explain plan is the following <code>------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 16470 | 34307 | | 1 | NESTED LOOPS | | 815 | 12M| 14222 | | 2 | VIEW | | 22813 | 357M| 532 | | 3 | CONNECT BY WITHOUT FILTERING | | | | | | 4 | TABLE ACCESS FULL | tab_B | 22...
Categories: DBA Blogs

converting a table from a timezone to another timezone

Tom Kyte - Mon, 2023-03-20 08:46
I have a table with two columns START_DATE and END_DATE and they have a data type of : Timestamp with timezone. and i have also a Globale Item that get the timezone of the user, and i should use it to convert the timezone of the column in the database to the timezone of the user. <b>the data in my table are stored in this format:</b> 09-MAR-23 PM +01:00 <b> the way i do the insert is:</b> insert into table_name(START_DATE) VALUES(TO_TIMESTAMP(:P51010_START_DATE, 'YYYY-MM-DD"T"HH24:MI:SS');); My attempts to solve the proble: <b>1. first attempt:</b> <code>SELECT CAST(START_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE :GP_TMZ AS CONVERTED_START_DATE, CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE :GP_TMZ AS CONVERTED_END_DATE FROM TABLE_NAME;</code> result: ORA-20999: Failed to parse SQL query! ORA-06550: line 6, column 83: ORA-00923: FROM keyword not found where expected <b>2. second attempt:</b> <code>SELECT CAST( data_inizio AS TIMESTAMP WITH TIME ZONE ) AT TIME ZONE '''' || '' || :GP_TMZ || '''' as CONVERTED_START_DATE, CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE '''' || '' || :GP_TMZ || '''' AS CONVERTED_END_DATE FROM TABLE_NAME;</code> To add a single quote at the Begining and at the end of the page item i try this query <b>result:</b> ORA-20999: Failed to parse SQL query! ORA-06550: line 1, column 1: ORA-01882: timezone region not found <b>Shared Components: Globalization section</b> Application Timestamp Format: DD-MON-YYYY HH:MI:SSXFF PM Application Timestamp Time Zone Format <code></code> DD-MON-YYYY HH.MI.SSXFF PM TZR <b>select * from v$version;</b> BANNER: Oracle Database 19c Enterprise Edition Release - Production BANNER_FULL: Oracle Database 19c Enterprise Edition Release - Production Version BANNER_LEGACY: Oracle Database 19c Enterprise Edition Release - Production CON_ID: 0
Categories: DBA Blogs

Use The Right Tool

Michael Dinh - Mon, 2023-03-20 07:38

I think I may have blogged about how most read from left to right and down unless you are reading Chinese.

Look at how nice that is versus using text file.

Back to the grind.

Unlocking the Benefits of Cloud-based SaaS ERP: Is Your Organization Ready to Make the Move?

Senthil Rajendran - Sun, 2023-03-19 23:40

 In today's fast-paced business environment, it is crucial for organizations to stay ahead of the curve and adopt the latest technologies to remain competitive. One such technology that is gaining popularity among businesses of all sizes is cloud-based software-as-a-service (SaaS) enterprise resource planning (ERP) systems. Cloud-based SaaS ERP offers a wide range of benefits, including increased efficiency, cost savings, and scalability. However, the decision to move to a cloud-based SaaS ERP system is not one that should be taken lightly. In this blog post, we will explore when organizations should consider moving to a cloud-based SaaS ERP system.

  • Your Current System is Outdated - One of the most common reasons why organizations consider moving to a cloud-based SaaS ERP system is that their current system is outdated. Legacy systems can be slow, cumbersome, and difficult to maintain, which can lead to inefficiencies and reduced productivity. Moving to a cloud-based SaaS ERP system can help organizations streamline their processes, reduce downtime, and increase productivity.
  • You Need Scalability - Another reason why organizations may consider moving to a cloud-based SaaS ERP system is that they need scalability. Traditional on-premise ERP systems can be expensive and time-consuming to scale up or down as the needs of the organization change. Cloud-based SaaS ERP systems, on the other hand, are designed to be highly scalable and can be easily adjusted to meet the changing needs of the organization.
  • Cost Savings - One of the primary benefits of moving to a cloud-based SaaS ERP system is cost savings. Traditional on-premise ERP systems require significant upfront investments in hardware, software, and IT staff. In contrast, cloud-based SaaS ERP systems are typically more affordable, with a pay-as-you-go pricing model that allows organizations to pay only for what they need. Additionally, cloud-based SaaS ERP systems can help organizations reduce IT staff, hardware, and maintenance costs.
  • Need for Increased Mobility - Today's business environment demands increased mobility, with employees requiring access to data and applications from anywhere and at any time. Cloud-based SaaS ERP systems provide this flexibility, with web-based interfaces that can be accessed from any device with an internet connection. This means that employees can work from anywhere, whether it's at home, on the road, or in the office.
  • Need for Real-Time Data - Finally, organizations may consider moving to a cloud-based SaaS ERP system if they require real-time data. Traditional on-premise ERP systems can be slow to provide data, with reports and analytics taking hours or even days to generate. In contrast, cloud-based SaaS ERP systems provide real-time data, with dashboards and analytics that can be accessed instantly.

In conclusion, there are many reasons why organizations may consider moving to a cloud-based SaaS ERP system, including outdated systems, scalability, cost savings, increased mobility, and the need for real-time data. However, it is important to carefully evaluate your organization's specific needs and requirements before making the decision to move to a cloud-based SaaS ERP system. With the right planning and implementation, a cloud-based SaaS ERP system can provide significant benefits and help organizations stay competitive in today's fast-paced business environment.

The Cloud Era: Oracle SaaS ERP vs. On-Premise EBS 12.2 - Which Path Will Your Business Choose?

Senthil Rajendran - Sun, 2023-03-19 23:21

 In recent years, the cloud-based enterprise resource planning (ERP) system has become increasingly popular among businesses of all sizes. Oracle, one of the largest enterprise software providers in the world, offers its own cloud-based ERP solution, known as Oracle SaaS ERP. While many businesses are migrating to cloud-based ERP solutions, there are still some that continue to rely on on-premise systems, such as Oracle EBS 12.2. In this blog post, we will discuss Oracle SaaS ERP and the future of on-premise EBS 12.2.

Oracle SaaS ERP is a cloud-based ERP solution that offers businesses a modern, integrated, and scalable platform for managing their financials, procurement, project management, and more. Oracle SaaS ERP is hosted on Oracle's own cloud infrastructure, which means businesses do not need to invest in their own hardware or software. The system is designed to be easy to use and can be customized to meet the specific needs of each business.

One of the major advantages of Oracle SaaS ERP is its flexibility. The system is designed to be highly customizable, which means businesses can configure the system to meet their unique needs. The system can be easily integrated with other Oracle cloud products, such as HCM and CX, which can help businesses create a unified experience for their customers and employees.

Another advantage of Oracle SaaS ERP is its scalability. The system can easily scale up or down to meet the changing needs of the business. This means that businesses can easily add or remove users, modules, or features as their needs change. Additionally, the system is designed to be highly available, which means that businesses can rely on it to be up and running when they need it.

Despite the many advantages of Oracle SaaS ERP, there are still some businesses that continue to rely on on-premise ERP systems, such as Oracle EBS 12.2. These systems are typically hosted on the business's own hardware and require dedicated IT staff to manage and maintain them. While these systems can be highly customized to meet the specific needs of the business, they can be expensive and time-consuming to maintain.

So what is the future of on-premise EBS 12.2? While there will likely always be businesses that continue to rely on on-premise ERP systems, it is clear that the future is in the cloud. Oracle has already announced that it will be ending support for EBS 12.2 in 2030, which means that businesses that continue to rely on this system will need to start planning for a migration to a cloud-based ERP solution.

In conclusion, Oracle SaaS ERP offers businesses a modern, flexible, and scalable platform for managing their financials, procurement, and other business processes. While some businesses may continue to rely on on-premise ERP systems, such as Oracle EBS 12.2, the future is in the cloud. Businesses that are still using on-premise ERP systems should start planning for a migration to a cloud-based ERP solution sooner rather than later to ensure they are prepared for the future.


Subscribe to Oracle FAQ aggregator