Feed aggregator

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...

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 06.30.00.000000 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 19.0.0.0.0 - Production BANNER_FULL: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.1.0 BANNER_LEGACY: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID: 0

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.

 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.

 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.

The root cause of the issue is source (DW) and target (OLTP) have different partition design for index organized table (IOT).

Source: PARTITION BY HASH and Target: PARTITION BY VALUES

IOT has approximately 1.9B rows.

Version 19.12.2.0.0

### References:

In What Order Are Indexes Built During Datapump Import (IMPDP) and How to Optimize the Index Creation (Doc ID 1966442.1)	

Normally index creation in a schema level import will follow this order:
1. Metadata import (user, roles & system privileges)
2. Objects like type, sequences and related grants
3. Tables, table data, table grants
4. Indexes

### This is the parameter file for export.
exclude=STATISTICS
compression=ALL
# Is it necessary to export staging?
schemas=staging,s2,s3,s4,s5
flashback_scn=61727639035
# There are 8 CPUs and possible to use up to 1.5xCPU and monitor.
# FUD in play.
parallel=8
content=DATA_ONLY
# Added for improvements to avoid 1 process doing all the work.
# Tested using 1G/2G and no huge improvements.
filesize=4G
logfile=expdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

### This is the parameter file for import.
table_exists_action=TRUNCATE
# Added for performance improvements since there is no standby database.
transform=DISABLE_ARCHIVE_LOGGING:Y
logtime=ALL
metrics=Y
# There are 8 CPUs and possible to use up to 1.5xCPU and monitor.
# FUD in play.
parallel=8
cluster=N
schemas=staging,s2,s3,s4,s5
# STATISTICS was already exclude from import.
# Exclude is not necessary, since will import everything.
exclude=STATISTICS,REF_CONSTRAINT,GRANT,INDEX,TRIGGER
content=DATA_ONLY
logfile=impdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

### Here is the export dmp.
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_09 is:
  /export/schema01.dmp
  /export/schema02.dmp
  /export/schema03.dmp
  /export/schema04.dmp
  /export/schema05.dmp
  /export/schema06.dmp
  /export/schema07.dmp
  /export/schema08.dmp
  /export/schema09.dmp
  /export/schema10.dmp
  /export/schema11.dmp
  /export/schema12.dmp
  /export/schema13.dmp
  /export/schema14.dmp
  /export/schema15.dmp
  /export/schema16.dmp
  /export/schema17.dmp
  /export/schema18.dmp
  /export/schema19.dmp

### Here is the import for IOT.
16-MAR-23 20:25:48.971: W-4 . . imported "H01" 3.946 GB 474235896 rows in 12183 seconds using external_table
16-MAR-23 14:42:56.470: W-7 . . imported "H02" 3.952 GB 475007010 rows in 7113  seconds using external_table
16-MAR-23 23:45:07.344: W-8 . . imported "H03" 3.945 GB 474120834 rows in 11956 seconds using external_table
16-MAR-23 17:02:44.346: W-6 . . imported "H04" 3.949 GB 474655428 rows in 8386  seconds using external_table

16-MAR-23 23:58:02.815: Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed at Thu Mar 16 23:58:02 2023 elapsed 0 11:18:20

The result for import was 50% faster for IOT with ~1.90B rows.

There are probably more options to test and tune; however, would be too time consuming.

Writing this blog post, I realized I have made the mistake of not requesting export/import parameter files and logs as there are redundancies for export and import parameters.

As I have done multiple engagements with Oracle GoldenGate and helped clients get the most out of their investment; I […]

The post GoldenGate Parameter Files – Format and Logic appeared first on DBASolved.

 This video shows you step by step demo to enable point in time recovery PITR for AWS DynamoDB table using AWS CLI.

 If you are a cloud engineer and looking to get into Cyber or cloud security then start by learning these tools.

Is there a utility that helps us migrate the data from mySQL database to Oracle 8.1.6 database? Also, is there a strategy one has to follow for migrating from one database to another?

Migrating a very large object in Oracle can be a daunting task, especially when dealing with large volumes of data. One approach to this problem is to use online redefinition, a feature introduced in Oracle 9i, which allows for the restructuring of tables without requiring downtime. Online redefinition involves creating a new table with the desired structure and then copying the data from the old table to the new table using an online copy process. Once the data has been copied, the old table is dropped and the new table is renamed to take its place. This approach is particularly useful when migrating data from one tablespace to another. However, there are several complexities and things to be taken care of when performing a large volume migration using online redefinition. Some of these are outlined below: Available Space: Before starting the migration, it is essential to ensure that there is enough space available in both the source and target tablespaces. If there is insufficient space, the migration process will fail, and the data could be left in an inconsistent state. Concurrent Transactions: It is essential to ensure that there are no concurrent transactions running against the object being migrated. Concurrent transactions can lead to inconsistencies in the data and can cause the migration process to fail. Object Dependencies: When migrating an object, it is essential to consider its dependencies. For example, if the object being migrated is a partitioned table, it is essential to ensure that all its partitions are migrated in the correct order. Indexes and Constraints: If the object being migrated has indexes or constraints, they will need to be migrated along with the data. This can add to the complexity of the migration process, as the indexes and constraints may need to be disabled and re-enabled during the migration process. Performance Considerations: The migration process can be resource-intensive, particularly when dealing with large volumes of data. It is essential to consider the performance impact of the migration process on the database and to tune the database appropriately to minimize the impact. Backup and Recovery: Before starting the migration process, it is essential to take a backup of the database. This will ensure that the data can be recovered in the event of any issues during the migration process. In conclusion, migrating a very large object in Oracle using online redefinition can be a complex and challenging task, particularly when dealing with large volumes of data. It is essential to consider the complexities and things to be taken care of, as outlined above, to ensure a successful migration process. By following best practices and taking the necessary precautions, the migration process can be completed with minimal downtime and without compromising data integrity.

Setting up integration between Oracle Cloud SaaS and other systems can be a complex process, but following these general steps can help simplify the process:

1. Identify integration requirements: Start by identifying the integration requirements and objectives for your organization. This includes identifying the systems that need to be integrated, the data that needs to be exchanged, and the frequency of data exchange.

2. Identify integration patterns: There are several integration patterns that can be used to integrate Oracle Cloud SaaS with other systems, including file-based, service-oriented, and event-based integrations. Determine which pattern(s) will best meet your integration requirements.

3. Design the integration: Once you have identified the integration requirements and patterns, design the integration. This includes determining the data mapping, defining the integration workflows, and designing the integration interfaces.

4. Configure the integration: Configure the integration by setting up the integration interfaces, creating the integration workflows, and configuring the integration logic. This may involve using Oracle Integration Cloud Service or other integration tools.

5. Test the integration: After configuring the integration, test it to ensure that it is functioning correctly. This includes testing the data mapping, validating the integration workflows, and testing the integration interfaces.

6. Deploy the integration: Once the integration has been tested, deploy it to the production environment. This may involve setting up the integration jobs and scheduling the data exchange.

7. Monitor the integration: Monitor the integration to ensure that it is functioning correctly and to identify any issues. This may involve setting up alerts and notifications to notify stakeholders of any issues.

8. Optimize the integration: Continuously optimize the integration to improve performance and ensure that it continues to meet the integration requirements. This may involve identifying areas for improvement and making changes to the integration configuration.

It is important to work with an experienced Oracle Cloud integration partner to ensure that the integration is properly designed, configured, and tested. An experienced partner can help you identify the integration requirements, select the appropriate integration patterns, and design and configure the integration to meet your organization's needs.

Migrating Financial Management modules from EBS 12.2 to Oracle Cloud SaaS can be a complex project, but a well-planned and executed migration can yield significant benefits. Here's a detailed project plan for migrating Financial Management modules:

1. Pre-migration assessment: a. Identify the specific modules and data to be migrated. b. Conduct an analysis of the current EBS 12.2 environment to identify customizations, integrations, and other dependencies. c. Identify the resources required for the migration, including personnel, hardware, and software.

2. Create a migration plan: a. Develop a migration strategy that addresses the timing, sequence, and dependencies of the migration. b. Create a project plan that includes specific tasks, timelines, and resource assignments. c. Identify key stakeholders and establish communication protocols.

3. Prepare the EBS 12.2 environment for migration: a. Conduct any necessary upgrades or patches to the EBS 12.2 environment. b. Prepare the data for migration, including data cleansing and mapping. c. Develop a test plan to ensure that data is correctly migrated to the Oracle Cloud SaaS environment.

4. Configure the Oracle Cloud SaaS environment: a. Configure the Oracle Cloud SaaS environment to match the EBS 12.2 environment. b. Configure security settings and access controls. c. Test the configuration to ensure that it is functioning correctly.

5. Migrate the data: a. Migrate the data to the Oracle Cloud SaaS environment using Oracle Data Transfer Service or other migration tools. b. Verify the completeness and accuracy of the migrated data. c. Perform a data reconciliation between the EBS 12.2 environment and the Oracle Cloud SaaS environment.

6. Migrate the applications: a. Migrate the Financial Management applications to the Oracle Cloud SaaS environment using Oracle's Lift and Shift tool or other migration tools. b. Test the migrated applications to ensure that they are functioning correctly. c. Perform a functional reconciliation between the EBS 12.2 environment and the Oracle Cloud SaaS environment.

7. Post-migration validation: a. Conduct performance testing to ensure that the migrated environment meets performance requirements. b. Conduct security testing to ensure that the migrated environment is secure. c. Conduct user acceptance testing to ensure that the migrated environment meets user requirements.

8. Go-live: a. Establish a cut-over plan to transition users from the EBS 12.2 environment to the Oracle Cloud SaaS environment. b. Provide training and support to users to ensure a smooth transition. c. Monitor the Oracle Cloud SaaS environment after go-live to identify and resolve any issues.

This project plan can be adapted to suit the specific requirements of your organization's Financial Management migration project. It is important to work with an experienced Oracle Cloud migration partner to ensure that the migration is properly planned and executed.

 Oracle E-Business Suite (EBS) 12.2 is a popular enterprise resource planning (ERP) system that has been widely used by businesses for many years. If you are considering migrating your EBS 12.2 system to the Oracle Cloud, you may be wondering which modules can be migrated.

In general, most of the EBS 12.2 modules can be migrated to the Oracle Cloud SaaS. Some of the most common EBS modules that can be migrated to the Oracle Cloud SaaS include:

1. Financial Management: This module includes features such as General Ledger, Accounts Payable, Accounts Receivable, Cash Management, and Fixed Assets.

2. Supply Chain Management: This module includes features such as Purchasing, Inventory, Order Management, and Advanced Supply Chain Planning.

3. Human Capital Management: This module includes features such as Core HR, Payroll, Benefits, Talent Management, and Time and Labor.

4. Project Management: This module includes features such as Project Costing, Project Billing, Project Management, and Project Resource Management.

5. Customer Relationship Management: This module includes features such as Sales, Marketing, Service, and Contracts.

6. Governance, Risk, and Compliance: This module includes features such as Advanced Controls, Financial Reporting Compliance, and Risk Management.

However, it is important to note that the availability of specific modules in the Oracle Cloud SaaS may depend on the version of EBS 12.2 that you are running and the specific features and customizations that you have implemented in your EBS environment.

Before migrating your EBS 12.2 system to the Oracle Cloud SaaS, it is recommended that you work with an experienced Oracle Cloud migration partner who can help you assess your EBS environment, identify the modules that can be migrated, and create a migration plan that minimizes disruption to your business processes.

 Oracle SaaS Cloud Migration is the process of moving an organization's applications and data from an on-premise or legacy infrastructure to the Oracle Cloud. The benefits of cloud migration include cost savings, scalability, flexibility, and increased security. In this blog post, we will discuss the process of Oracle SaaS Cloud Migration and its benefits.

Process of Oracle SaaS Cloud Migration:

The process of Oracle SaaS Cloud Migration can be broken down into the following steps:

1. Assessment: The first step in the process is to assess the current IT environment and identify the applications and data that need to be migrated. This assessment will help determine the migration strategy and the resources required for the migration.

2. Planning: The next step is to create a migration plan. This plan should include the migration strategy, the timeline, and the resources required for the migration. It should also include a risk assessment and a contingency plan in case of any issues during the migration.

3. Data Migration: The next step is to migrate the data to the Oracle Cloud. This can be done using Oracle Data Transfer Service, which provides a fast and secure way to transfer data to the cloud. It is important to ensure that the data is migrated securely and that all data is accounted for.

4. Application Migration: Once the data has been migrated, the next step is to migrate the applications to the Oracle Cloud. This can be done using Oracle's Lift and Shift tool, which automates the migration of applications to the cloud. It is important to ensure that the applications are tested thoroughly before and after the migration.

5. Testing: The final step is to test the applications and data in the Oracle Cloud to ensure that they are functioning correctly. This testing should include performance testing, security testing, and functionality testing. Once the testing is complete, the migration is considered successful.

Benefits of Oracle SaaS Cloud Migration:

There are several benefits to Oracle SaaS Cloud Migration, including:

1. Cost Savings: Moving to the cloud can save organizations money on hardware, maintenance, and infrastructure costs.

2. Scalability: The cloud provides organizations with the ability to scale their infrastructure as needed, without having to invest in additional hardware.

3. Flexibility: The cloud provides organizations with the flexibility to access their data and applications from anywhere, at any time.

4. Increased Security: The cloud provides organizations with increased security, as data is stored in secure data centers and is protected by advanced security protocols.

5. Improved Performance: The cloud provides organizations with improved performance, as data is stored in high-speed data centers with advanced network connectivity.

Conclusion:

Oracle SaaS Cloud Migration is a complex process that requires careful planning and execution. However, the benefits of cloud migration, including cost savings, scalability, flexibility, increased security, and improved performance, make it a worthwhile investment for organizations. If you are considering migrating to the Oracle Cloud, it is important to work with an experienced partner who can guide you through the process and ensure a successful migration.

 Oracle SaaS Rest API is a cloud-based platform that provides a secure and efficient way for businesses to integrate their Oracle applications with third-party systems. The platform provides developers with a comprehensive set of REST APIs that enable them to build custom applications and automate business processes using Oracle SaaS data.

Advantages of Oracle SaaS Rest API

1. Improved Efficiency: Oracle SaaS Rest API helps to automate business processes, which in turn improves efficiency. For instance, developers can use the platform to automate tasks like data entry, report generation, and other manual processes, freeing up time for more critical tasks.

2. Better Data Management: Oracle SaaS Rest API provides developers with access to real-time data, enabling them to make better decisions. This feature is particularly useful for businesses that operate in fast-paced environments where quick decision-making is critical.

3. Enhanced Security: Oracle SaaS Rest API uses a secure token-based authentication method that ensures data is protected from unauthorized access.

4. Cost Savings: By automating business processes, businesses can reduce operational costs, including staffing and manual data entry.

5. Scalability: Oracle SaaS Rest API is designed to handle large volumes of data, making it ideal for businesses of all sizes.

Limitations of Oracle SaaS Rest API

1. Complexity: Oracle SaaS Rest API can be challenging to implement and use for non-technical users.

2. Limited Integration: The platform's integration with third-party systems is limited, which can be a hindrance to businesses that rely on multiple applications.

3. Dependence on Internet Connection: Oracle SaaS Rest API is a cloud-based platform, which means it is entirely dependent on internet connectivity. In case of network disruptions, businesses may experience downtimes, affecting operations.

In conclusion, Oracle SaaS Rest API is a powerful platform that offers businesses a wide range of features to automate business processes and improve efficiency. However, its complexity and limited integration may be a hindrance to businesses that require multiple integrations. Despite this, Oracle SaaS Rest API remains a top-tier option for businesses that require a reliable and secure platform to integrate their applications.

We want to use the Data Loading functionality of APEX to upload a specific tab of an Excel sheet. We only found out it the Data Loading function only imports the first tab of the Excel sheet. We want to upload the second one of the Excel sheet. What are we missing here?

Hi Tom, I have a text column with clob datatype. (Below is how data looks in the column). I want to have only the customer input information in the column and remove all private notes. Text Example: Customer Input 04.12.2022 here is sentence 1 ||Private Notes This is sentence 2||Private Notes This is sentence 3 ||Customer Input this is sentence 4||Private Notes This is sentence 5 and needs to be removed||Customer Input this is sentence 6 Expected Output: Customer Input 04.12.2022 here is sentence 1 ||Customer Input this is sentence 4||Customer Input this is sentence 6

I tried to use UTL_HTTP with generated pdf from query report which is made using rtf file( BI pupplisher), I created page to disply the report, and use the page url to add xml script to the pdf file, but i always receive error message <code>(ORA-29273: HTTP request failed) (ora_sqlcode: -29273 ora_sqlerrm: ORA-29273: HTTP request failed ORA-06512: at "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 55 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC_PLSQL", line 126 ORA-06512: at "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 2654 ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1148 ORA-06512: at line 37 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220200", line 837 ORA-06512: at "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 2614 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC_PLSQL", line 97 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC_PLSQL", line 430 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC", line 614 ORA-06512: at "APEX_220200.WWV_FLOW_EXEC_LOCAL", line 3053 ORA-06512: at "APEX_220200.WWV_FLOW_EXEC", line 2642 ORA-06512: at "APEX_220200.WWV_FLOW_EXEC", line 2678 ORA-06512: at "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 34 ORA-06512: at "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 1230 ORA-06512: at "APEX_220200.WWV_FLOW_PLUGIN", line 3163 component.type: APEX_APPLICATION_PAGE_PROCESS component.id: 62841056118348574801)</code> could you help me to solve this problem .. thanks in advance Part of Code <code>url :='https://apex.oracle.com/pls/apex/r/UserArea/AppName/testdirectreport?session=&SESSION.'; v_req := UTL_HTTP.begin_request (url, 'POST', ' HTTP/1.1'); UTL_HTTP.set_header (v_req, 'content-type', 'application/xml'); UTL_HTTP.set_header (v_req, 'charset', 'UTF-8'); UTL_HTTP.set_header (v_req, 'Content-Length', LENGTH (var)); reqlength := DBMS_LOB.getlength (var);</code>

 Here is a general outline of the automation process to send an email of the latest ADDM recommendation on an hourly basis. Here's how you can do it:

Write a script to extract the latest ADDM recommendation from the AWR repository. You can use SQL commands to query the AWR repository and retrieve the latest ADDM report.

Configure your email client to send an email programmatically. You can use the Simple Mail Transfer Protocol (SMTP) to send an email from your email account to a designated email recipient.

Integrate the script and email client to automate the email sending process. You can use a scheduling tool like cron to run the script on an hourly basis and send the email automatically.

Here's an example code snippet in Python:

import smtplib
from email.mime.text import MIMEText

def send_email(recommendation):
    # configure the email message
    msg = MIMEText(recommendation)
    msg['Subject'] = 'Latest ADDM Recommendation'
    msg['From'] = 'sender_email_address'
    msg['To'] = 'recipient_email_address'

    # configure the SMTP server
    smtp_server = smtplib.SMTP('smtp.gmail.com', 587)
    smtp_server.starttls()
    smtp_server.login('sender_email_address', 'sender_email_password')

    # send the email
    smtp_server.sendmail('sender_email_address', 'recipient_email_address', msg.as_string())
    smtp_server.quit()

# extract the latest ADDM recommendation from the AWR repository
latest_recommendation = extract_latest_addm_recommendation()

# send the email on an hourly basis
while True:
    send_email(latest_recommendation)
    time.sleep(3600) # wait for an hour

Here is a general outline of the automation process to send an email of the latest ADDM recommendation on an hourly basis. 

Here's how you can do it:

This Python script that extracts the latest ADDM recommendation from the AWR repository and sends it via email on an hourly basis using the extract_latest_addm_recommendation and send_email functions:

import smtplib
from email.mime.text import MIMEText
import cx_Oracle
import time

# database connection details
username = 'your_database_username'
password = 'your_database_password'
dsn = 'your_database_dsn'

# email details
sender_email = 'sender_email_address'
sender_password = 'sender_email_password'
recipient_email = 'recipient_email_address'

def extract_latest_addm_recommendation():
    # connect to the database
    connection = cx_Oracle.connect(username, password, dsn)

    # execute the SQL query to retrieve the latest ADDM report
    cursor = connection.cursor()
    cursor.execute('''
        SELECT db_name, start_snap_id, end_snap_id, report_html
        FROM dba_hist_snapshot
        JOIN dba_hist_snapshot_report
        ON dba_hist_snapshot.snap_id = dba_hist_snapshot_report.snap_id
        WHERE dba_hist_snapshot_report.report_name = 'ADDM Report'
        ORDER BY end_interval_time DESC
        FETCH FIRST 1 ROW ONLY
    ''')
    result = cursor.fetchone()

    # close the cursor and database connection
    cursor.close()
    connection.close()

    # return the ADDM report HTML as a string
    if result is not None:
        return result[3].read()

def send_email(recommendation):
    # configure the email message
    msg = MIMEText(recommendation)
    msg['Subject'] = 'Latest ADDM Recommendation'
    msg['From'] = sender_email
    msg['To'] = recipient_email

    # configure the SMTP server
    smtp_server = smtplib.SMTP('smtp.gmail.com', 587)
    smtp_server.starttls()
    smtp_server.login(sender_email, sender_password)

    # send the email
    smtp_server.sendmail(sender_email, recipient_email, msg.as_string())
    smtp_server.quit()

# send the email on an hourly basis
while True:
    # extract the latest ADDM recommendation from the AWR repository
    latest_recommendation = extract_latest_addm_recommendation()

    # send the email with the latest ADDM recommendation
    if latest_recommendation is not None:
        send_email(latest_recommendation)

    # wait for an hour before sending the next email
    time.sleep(3600)

This script assumes that you have installed the cx_Oracle library for connecting to the Oracle database and configured your email account settings properly. You may need to modify the script to suit your specific database and email environment.