Feed aggregator

Why a Complete Cloud Suite Matters to Your Business

Linda Fishman Hoyle - Thu, 2016-11-10 10:37

A Guest Post by Oracle’s Lisa Schwartz, Cloud Business Group (pictured left)

We tell customers all the time that Oracle has the most complete, unified cloud suite in the industry. But do you know why having a complete suite is so important?

The simple answer is because your customers expect your business to act as one business, not separate departments with discrete jobs. Employees and partners expect a unified business too, and in some cases, regulatory environments require it as well.

Customer expectations are very high

In a 2016 Harvard Business Review article entitled “Focus on Keeping Up with Your Customers, Not Your Competitors,” the author says the greatest challenge businesses have today is keeping up with customer expectations, not with competitors. Think about it: we don’t compare the customer service of one company to that of its competitors, but to the best service we’ve received anywhere.

Silos can ruin the customer experience

If you’ve ever had an unsatisfactory customer experience, it may have been due to departmental silos and disconnected business processes. Here are a few examples of how that can play out:

Your Preferences: Let’s say you receive a marketing promotion from your communications provider addressed to MS. Jane Doe, but the monthly bill lists you as MR. Jane Doe. Not a big deal, you say? Studies show that churn rates increase when customers have the impression a company doesn’t know them even after many years of doing business together.

Your Time: If you’ve ever had a family member with a serious illness, you know how frustrating it can be when the insurance company delays reimbursement. It might take you multiple calls to get through to the right person, and you have to repeat the same information every time. During the next benefits open enrollment period, you might decide to look at other health insurance options.

Your Information: Or maybe your credit score goes down because a payment processor acquires your auto loan, but your account information isn’t transferred properly and your payments aren’t processed. Next time you’re in the market for a car, you might need to broaden your search based on financing terms.

Having multiple clouds from different providers is costly, whether in the form of integration or disgruntled customers. These are just a few examples as to why a complete and unified cloud suite matters. I’m sure you can think of many more.

ADF LOV Auto Suggest Functionality

Andrejus Baranovski - Thu, 2016-11-10 10:34
ADF Faces LOV component can be easily enabled with auto suggest functionality. This is really powerful feature and should save time for user to search for required list values.

Auto suggest can be configured to be invoked only when certain number of characters is entered. I have configured Job ID list to trigger auto suggest, when two or more characters are entered. No auto suggest with single character:

Two or more characters entered - auto suggest displays filtered LOV values. What is nice about it - it automatically shows all these attributes included into LOV list:

In the background ADF BC executes SQL with bind variable from auto suggest:

LOV is defined on attribute in standard way, nothing special here:

To enable auto suggest behaviour, enough to drag and drop ADF af:autoSuggestBehavior tag into ADF Faces LOV component. Point suggestItems property to LOV binding suggestItems expression. Number of characters when to start auto suggest search is configure through minChars property:

Download sample application - ADFLovAutoSuggestApp.zip

Employee Enablement with Oracle PaaS for SaaS – Human Resources

WebCenter Team - Thu, 2016-11-10 09:05

Authored by: Mitchell Palski, Fusion Middlware Specialist

The employee experience of your organization is more transparent than ever. If Public Sector agencies are expected to compete with Commercial employers, they have to make strong first impressions on their new-hires. By improving the employee’s initial experience, your Human Resources department can have an immediate influence on employee productivity and retention.

What do employees care about?

I wrote a blog post back in April 2016 called “Recruit Millennials with Innovative Oracle Cloud Projects” highlighting the fact that the younger generations of the American workforce are attracted to innovative projects that give them the chance to get visibility for their achievements. In a 2015 NY Times article, Swarthmore Professor Barry Schwartz writes:

“But most important, we need to emphasize the ways in which an employee’s work makes other people’s lives at least a little bit better (and, of course, to make sure that it actually does make people’s lives a little bit better).”

More so than commercial industries, the social impact of a Government job has to be evident and visible in the outcomes of every day responsibility. The perennial value of our work is that it supposed to be a dedication to the citizen. The more often your employees are reminded of the imperishable effects they are having on your organization’s beneficiaries, the more motivated they will be to succeed.

There is an important accompaniment to “visibility”, which is of course recognition and reward. Tony Scwartz – Chief Executive of The Energy Project – worked with The Harvard Business Review to conduct a study of +20,000 employees across the globe to inspect which components contribute to a retentive workplace (among other things:

“Feeling treated with respect made employees feel 55 percent more engaged and 110 percent more likely to stay at the company. Employees who felt the most recognized and appreciated were 100 percent more likely to stay with their organizations.”

Think about how you manage your employees today – as “Human Capital”. Sounds more like an allocation of your investment portfolio than an actual person, doesn’t it? Well, it goes two ways. You are invested in your employees and they are invested in you. In this article, I hope to expose some technology-driven strategies to strengthen and lengthen these integral connections.

What can we do?

Natively delivered out-of-the-box, Oracle Human Capital Management (HCM) Cloud Service delivers HR functionality such as:
  • Prospective talent management
  • Workforce management
  • Workforce compensation and payroll
  • Time and Labor, absence management
  • Employee benefits and wellness
  • Social media monitoring
  • Real-time and predictive analytics
All of these capabilities are critical to your business, but software has an inborn influence on how employees are viewed. To effectively manage our staff, we use Employee ID numbers; we refer to employees as “resources”; we quantify, track and analyze performance. My intent isn’t to discount the importance of these practices, but rather to supplement them with intangibles.
  • Formalize innovation project review processes
  • Provide customized training paths
  • Establish leadership development plans
  • Start an Employee of the Month program
  • Encourage social collaboration

Oracle HCM is a wonderful foundation for managing the business of managing your people and has a wealth of data that can be extremely helpful in structuring innovative programs for employees. 

How do we do it?

Oracle’s Platform as a Service (PaaS) is a comprehensive, integrated portfolio of platform services that allow your organization to innovate new services faster and more productively at a lower cost. Using the Oracle PaaS services, technical users can create extensions of Oracle HCM.

HCM1 exposes business objects and processes to other applications through the use open standards-based technologies, including:
  • Extensible Markup Language (XML)
  • Simple Object Access Protocol (SOAP)
  • Business Process Execution Language (BPEL)
  • Web Services Description Language (WSDL)
  • XML schema definitions (XSD)

Take a look through the documentation and you will see how Oracle’s Fusion Applications are built with the purpose of being extended into your greater Enterprise. Oracle PaaS is making the development of application extensions increasingly easier, so it’s actually more important to understand the functional components of HCM than to have a highly technical staff. Platform tools allow your organization to focus on the processes, rules, and data model that you’ve constructed in your HCM system, rather than the technical underpinnings that will ultimately be relied on by your new services.

When it’s time to get technical and start building add-ons to your HCM, you can start by referencing the REST API that is available in the Fusion Application Documentation. The documentation even includes sample payloads to help speed up your development. From this point, you have the flexibility to either:
A) Leverage that REST API or
B) Transform that REST API into a SOAP service to be consumed by other applications

In cases like these, you can use the REST API to start developing right away:

  • Use Oracle Java Cloud Service (JCS) to build office-specific “microsites” that help deliver your users’ need-to-know updates and information
  • Connect to Mobile Cloud Service (MCS) to build a mobile app for traveling employees to submit Travel requests and Expense Reports
  • Leverage Process Cloud Service (PCS) for a custom application that handles HR policy management and approvals
  • Oracle Document Cloud Service (ODCS) includes Sites Cloud Service (SCS) for document collaboration workspaces
I don’t have the word count to go into all of the possibilities that Oracle PaaS can afford your organization, but I hope you walk away from this article with a kernel of knowledge. You are not limited to what comes out-of-the-box with any Oracle Fusion Application, including Oracle’s HCM Cloud. Your organization can enable seamless content delivery, social collaboration, and personalized LOB applications, without ever customizing your Oracle SaaS application.
Remember, there is a purpose to all of this. HCM tools are perfect for managing your personnel and resources but you manage people. Inculcate your people with the virtues of your public service and build an interface that highlights their alignment to those virtues. Oracle Cloud Services will allow you to easily and inexpensively build the interfaces to do exactly that.

1Access Oracle Enterprise Repository for Oracle Fusion Applications to find detailed information about integration assets, such as web services.

Shawnee State University Chooses Complete Oracle Cloud Suite to Improve Recruitment and Operations

Oracle Press Releases - Thu, 2016-11-10 07:00
Press Release
Shawnee State University Chooses Complete Oracle Cloud Suite to Improve Recruitment and Operations First state university in Ohio selects Oracle Cloud solutions to improve retention and streamline business practices

Redwood Shores, Calif.—Nov 10, 2016

Oracle announced today that Shawnee State University is implementing the full Oracle Cloud Suite including Oracle’s Enterprise Resource Planning (ERP), Enterprise Performance Management (EPM) and Human Capital Management (HCM) Cloud), in addition to Student Cloud CX. The university also anticipates moving to the Student Management Cloud as part of the university’s efforts to prepare their students for future success using the latest technology. This adoption comes with a recent Ohio Governor mandate to lower the cost of education, which prompted the university to search for the best technology to modernize and optimize internal operations.

With a student and staff body of 3,800 students and 700 employees, Shawnee State seeks to be a leader in paving the way for universities of all sizes to transition to cloud services. Their previous IT department was too small to handle the level of needs for their constituents. However, through the implementation of Oracle’s Cloud solution, the university will be utilizing a single cloud solution that helps manage recruitment processes to seek out best-fit students; this also enables them to focus on serving their constituents in a timelier manner.

While previously using an on-premise service, ensuring that recruiters reached the right students, and that those prospects ultimately chose to attend the university, was a big problem that needed to be solved. Shawnee State was seeking a service that focused on the entire student lifecycle from student success to finances – all while providing a single cloud experience from a global perspective.

“We believe that implementing Oracle’s Cloud solutions will help us meet our priority of being a student-focused university, by leveraging the latest technology all under a single unified cloud experience,” said Elinda Boyles, vice president for finance and administration at Shawnee State University. “We are fortunate that Shawnee State University President, Rick Kurtz, has forward-thinking vision and commitment to the university’s long-term success. This effort will improve operational efficiencies and better align our IT resources,” she said.

Because Oracle seeks to ensure their customers have a seamless transition to the cloud, Shawnee State was able to easily integrate the modern technology with legacy systems. Shawnee State is live with Oracle’s Student Cloud CX, and are implementing the remainder of the suite now with an expected go live of June 30, 2017.

“Oracle’s commitment to Higher Education and proven expertise in the Cloud provide Shawnee State University with a modern, secure and scalable platform for growth,” said Rondy Ng, Oracle’s Senior Vice President, Applications Development. “Our next-generation cloud platform for Higher Education incorporates embedded analytics, an intuitive user experience, and built-in collaboration functionality to deliver comprehensive support for the business and student lifecycle with a focus on putting the student first.”

Oracle delivers the industry’s broadest suite of enterprise-grade Cloud services, including Software as a Service (SaaS), Platform as a Service (PaaS), Infrastructure as a Service (IaaS), and Data as a Service (DaaS).

Contact Info
Jennifer Yamamoto
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

About Shawnee State University

Shawnee State is a student-focused public university offering a highly personalized, affordable, and accessible education dedicated to the exploration of emerging technologies and emerging ideas. Founded in 1986, and located along the Ohio River in historic Portsmouth, Ohio, the university offers a range of signature programs, including Game Design, which has been nationally ranked for six years in a row.


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

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Jennifer Yamamoto

  • +1.916.761.9555

Trigger not Dropped from Recyclebin

Tom Kyte - Thu, 2016-11-10 06:06
Hi, On one of our 11g databases we are not able to purge an object from the recyclebin: sys@DEVDB11:SQL> select count(*) from dba_recyclebin; COUNT(*) -------------- 1320 sys@DEVDB11:SQL> purge dba_recyclebin; DBA Rec...
Categories: DBA Blogs

Using with clause and double function in where clause causes non joined rows to be processed

Tom Kyte - Thu, 2016-11-10 06:06
Hi, I seem to have stumbled upon Oracle behaviour I can not explain, so I've come to the magical place called Ask Tom for guidance. I have created the following example to make it as short as possible while still reproducing my problem: 2 tabl...
Categories: DBA Blogs

Partition an Exisiting Table

Tom Kyte - Thu, 2016-11-10 06:06
I am trying to partition an existing table (Range partition) by a date column (BEGTIME). Currently the table is 7.5 GB in size. Our business moves monthly and wanted to partition into months. Also table holds data pertaining to forecasts up to Dec 21...
Categories: DBA Blogs

Fast Generation of CSV and JSON from Oracle Database

Christopher Jones - Thu, 2016-11-10 01:38

Want a fast way to spool CSV or JSON from Oracle Database? Check out the new SQL*Plus 12.2 SET MARKUP CSV option and the new Oracle Database 12.2 JSON_OBJECT operator.

You can try Oracle Database 12.2 now using Oracle Cloud Database Service.

Fast, Easy CSV with SQL*Plus and Oracle Database

First, let's see CSV ("comma separated values") output in SQL*Plus 12.2. Start with this script, t.sql:

  set feedback off

  select department_id, department_name from departments where department_id < 110;

Executed traditionally you get formatted output:

  SQL> @t.sql

  ------------- ------------------------------
	     10 Administration
	     20 Marketing
	     30 Purchasing
	     40 Human Resources
	     50 Shipping
	     60 IT
	     70 Public Relations
	     80 Sales
	     90 Executive
	    100 Finance

Running it with the new CSV mode:

  SQL> set markup csv on
  SQL> @t.sql

  40,"Human Resources"
  70,"Public Relations"


The full CSV syntax is:


You can see the delimiter can be changed from a comma, and quoting of fields can be disabled.

The SET MARKUP option can also be enabled from the command line with the -m option:

  $ sqlplus -s -m 'csv on' cj@pdb1 @t.sql

  40,"Human Resources"
  70,"Public Relations"
(Pro tip: the -s silent option may hide the password prompt on some platforms making SQL*Plus appear to hang! Enter the password and all will be well.)

CSV mode bypasses the traditional SQL*Plus pagination routines, making output faster to generate. And using the -m 'csv on' option additionally invokes a faster I/O subsystem, and also automatically sets some SET options to the values of another new option sqlplus -f. Two of the changes are increasing SET ARRAYSIZE to 100 and SET ROWPRETCH to 2.

Increasing SET ARRAYSIZE allows larger amounts of data to be returned in each low level request to the database, thus improving overall efficiency. Having SET ROWPRETCH to 2 reduces the database 'round trips' required between SQL*Plus and the database for queries that return only single rows. You should adjust the values of these, and other, settings to suit your data size and performance goals.

CSV mode can be used when connecting SQL*Plus 12.2 to Oracle Database 12.2 or earlier.

Fast, Easy JSON with SQL*Plus and Oracle Database

The Oracle Database 12.2 JSON_OBJECT function is a great way to convert relational table output into JSON.

Combined with SQL*Plus 12.2's efficient CSV output - and with quoting of columns disabled - you can spool JSON very easily.

Here's a SQL*Plus script t2.sql to return JSON output:

  set heading off
  set feedback off

  select json_object ('deptId' is d.department_id, 'name' is d.department_name) department 
  from departments d 
  where department_id < 110;


Running it generates the desired JSON output:

  $ sqlplus -s -m 'csv on quote off' cj@pdb1 @t2.sql
  {"deptId":40,"name":"Human Resources"}
  {"deptId":70,"name":"Public Relations"}

SQL*Plus 12.2 and Oracle Database 12.2 allow efficient and easy access to CSV and JSON data. If you don't yet have 12.2, or you want to create web services and do more with the data, check out Oracle ORDS or roll your own solution using the node-oracledb driver for Node.js.

Documentum story – Disable PDF automatic generation but allow jpeg

Yann Neuhaus - Thu, 2016-11-10 00:00

We had a request by a customer where he wanted the pdf automatic rendition to be disabled but keep allowing the jpeg renditions to be used as thumbnails for D2. The pdf is generated by an eternal tool so it wasn’t needed here. Here is what we did:

Login to the ADTS server and edit the following file:

For the repository named “DOCBASE1″, comment the QueueProcessorContext tag as follow and only if it include the dm_autorender_win31 value:

<!-- Start of comment to be added
<QueueProcessorContext DocbaseName="DOCBASE1">
    <CTSServer AttributeName="queueItemName" AttributeValue="dm_autorender_win31"/>
       End of comment to be added -->

Then restart the services

After the restart of the Content Transformation Services, wait for a few minutes and open the CTS log file <CTS_HOME>\logs\CTS_log.txt.

On the CTS log file:
– Ensure that there are no important errors and/or exceptions
– Search for the message “Product ADTS version <CTS_VERSION> is installed on repository: DOCBASE1″

Open the file <CTS_HOME>\logs\Polling_log.txt in order to verify that the changes have been applied successfully.

In this file, find all “DOCBASE1″ related CTSLegacyQueueProcessor log entries by searching for the following text:

DOCBASE1 CTSLegacyQueueProcessor

There should not be any matching text in the log file.


Note that you can use the jpeg renditions in the D2 4.5 Preview widget in order to display a kind of preview directly in the D2 interface.


Cet article Documentum story – Disable PDF automatic generation but allow jpeg est apparu en premier sur Blog dbi services.

Yes, Storage Arrays Can Deduplicate Oracle Database. Here Is Exactly Why It Doesn’t Matter!

Kevin Closson - Wed, 2016-11-09 23:14

I recently had some cycles on a freshly installed Dell EMC XtremIO Storage Array. I took this opportunity to prepare a blog entry about the never-ending topic of whether or not storage arrays are able to reduce physical data capacity through deduplication of blocks in Oracle Database.

Of Course There Is Duplicate Data In Oracle Datafiles

Before I continue, let me say something that may come as a surprise to you. Yes, Oracle Database has duplicate blocks in tablespaces! Yes, modern storage arrays can achieve astonishing data reduction rates through deduplication–even when the only data in the array is Oracle Database (whether ASM or file systems)!

XtremIO computes and displays global data reduction rate. This makes it a bit more difficult to show the effect of deduplication on Oracle Database because averages across diverse data makes pin-point focus impossible. However, as I was saying, I took some time on a freshly-installed XtremIO array and collected what I hope will be interesting information on the topic of deduplication.

Please take a look at Figure 1. To start the testing I created a 4TB XtremIO volume, attached it as a LUN to a test host and then created an XFS file system on it. Please be aware that the contents of an Oracle datafile is precisely the same whether stored in ASM or in a file system file. After the file system was created I used the SLOB database creation kit (SLOB/misc/create_database_kit) to create a small database with Oracle Database 12c. As Figure 1 shows, the small database consumed 11.83GB of logical space in the 4TB volume. However, since the data enjoyed a slight deduplication ratio of 1.1:1 and a healthy compression ratio of 3.3:1 for a 3.6:1 data reduction ratio, only 3.27GB physical space was consumed in the array.


Figure 1

The next step in the testing was to consume the majority of the 4TB file system with a BIGFILE tablespace. Figure 2 shows the DDL I used to create the tablespace.


Figure 2

Figure 3 shows the file system file that corresponds to the tablespace created with DDL in Figure 2.


Figure 3

After creating the 3.9TB BIGFILE tablespace I took a screenshot of the XtremIO GUI Dashboard. As Figure 4 shows, there was no deduplication! Instead, the data was compressed 4.0:1 resulting in only 977.66GB physical space being consumed in the array. So why in the world would I blog the opposite of what I said above? Why show the array did not, in fact, deduplicate the 3.9TB datafile? The answer is in the fact that I said there are duplicate data block in tablespaces. I didn’t say there are duplicate blocks in the same datafile!


Figure 4

To return the array to the state prior to the BIGFILE tablespace creation, I dropped the tablespace (including contents and datafiles thus unlinking the file) and then used the Linux fstrim(8) command to return the space to the array as shown in Figure 5.


Figure 5

Once the fstrim command completed I took another screenshot of the XtremIO GUI Dashboard as shown in Figure 6. Figure 6 shows that the array space utilization and data reduction had returned to that of what was seen before the BIGFILE tablespace creation.


Figure 6

OK, Now For The Duplicate Data

The next step in the testing was to fill up the majority of the 4TB file system with SMALLFILE tablespaces. To do so I created 121 tablespaces each consisting of a single SMALLFILE datafile of 32GB. The output shown in Figure 7 is from a data dictionary query to display the size of each of the 121 datafiles and how the sum of these datafiles consumed 3.87TB of the 4TB file system.


Figure 7

That’s Duplicate Data

Once the file system was filled with SMALLFILE datafiles I took another screenshot of the XtremIO GUI Dashboard. Figure 8 shows that the SMALLFILE datafiles enjoyed a deduplication ratio 81.8:1 combined with a compression ratio of 3.8:1 resulting in a global data reduction rate of 306.9:1. Because of the significant data reduction rate only 12.68GB of physical space was consumed in the array in spite of the 3.79TB logical space (the sum of the SMALLFILE datafiles) being allocated.


Figure 8

So here we have it! I had a database created with Oracle Database 12c that consisted of 121 32GB files for roughly 3.8TB database size yet XtremIO deduplicated the data down by a factor of 82:1!

So arrays can deduplicate Oracle Database contents! Right? Well, yes, but it matters none whatsoever. Allow me to explain.

Oracle datafiles consist of initialized blocks but vast portions of that initialized content is the same from file to file. This fact can be seen with simple md5sum(1) output. Consider Figure 9 where you can see the output of the md5sum command used to compute Oracle datafile checksums but only after skipping the first 8,692 blocks (8KB blocks). It’s the first approximate 68MB of each datafile that is unique when a datafile is freshly initialized. Beyond that threshold we can see (Figure 9) that the rest of the file content is identical.


Figure 9

Thus far this blog post has proven that initialized, but empty, Oracle Database datafiles have duplicate data. As the title of this post says, however, it does not matter.

Introduce Application Data To The Mix

Figure 10 shows the commands I used to populate each of the 121 tablespaces with a single table. The table has the sparse characteristic we are all accustomed to with SLOB. That is, I am only creating a single row in each block. Moreover, I’m populating each of these 121 tables with the same application data! This is precisely why I say deduplication of Oracle Database doesn’t matter because it only holds true until any application data is loaded into the data blocks. Figure 10 shows this set of DDL commands.


Figure 10

After populating the blocks in each of the 121 tables (each residing in a dedicated SMALLFILE tablespace) with blocks containing just a single row of application data I took another screenshot of the XtremIO GUI Dashboard. Figure 11 shows how putting any data into the data blocks reverts the deduplication. Why? Well, remember that the block header of every block has the SCN of the last change made to the block. For this reason I can put the same application data in blocks and still have 100% unique blocks–at least at the 8KB level.

Please note that the application table I used to populate the 121 tables does not consume 100% of the data blocks in each of the SMALLFILE tablespaces. There were a few blocks remaining in each tablespace and thus there remained a scant amount of deduplication as seen in Figure 11. Most XtremIO customers see some insignificant deduplication in their Oracle Database environments. Some even see significant deduplication–at least until they insert data into the database.


Figure 11

In a follow-up post I’ll say a few words about the deduplication granularity and how it affects the ability to achieve small amounts of deduplication of unused space in initialized data blocks. However, bear in mind that the net result of any deduplication of Oracle Database data files is that the only space that can be deduplicated is space that has never had application data in it. After all, a SQL DELETE command doesn’t remove data–it only marks it as free in the block.


I don’t think there are that many Oracle shops that have an urgent need for data reduction of space that’s never been used to store application data. I could be wrong. Until I find out either way, I say that yes you can see deduplication of Oracle Database datafiles but it doesn’t matter one bit.








Filed under: All Flash Array, oracle, XtremIO

Query regarding primary key , foreign key relations and inserts

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, I have a two tables Table1 (id_pk number, name_fk varchar(10)) Table2 (name_pk varchar(10), id_fk number) id_pk : Primary key of table1 name_fk : foreign key referencing name_pk of Table2 name_pk : Primary key of table2 id_fk : Fo...
Categories: DBA Blogs

Complicated query to analyse utilization period

Tom Kyte - Wed, 2016-11-09 11:46
Dear Gents I have a query which is giving the available quantity in each location on specific dates: Location Tdate RunningQty CA02D003A 31-Jul-16 1152 CA02D003A 30-Sep-16 1092 CA02D003A 11-Oct-16 500 CA02D003A 13-Oct-16 0 CA02D003A 20-Oct...
Categories: DBA Blogs

Update one column in 58 millions records table

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, Need suggestions to improve performance. Following are the methods we tried 1. Using <b>merge</b> <code>MERGE INTO /*+ PARALLEL(tbl_temp,8) */ tbl_temp tcm USING (SELECT frn.customer_id, frn.risk FROM temp_new frn ) a ON (a.id = tc...
Categories: DBA Blogs

Unable to use the out type parameter in procedure through DB Link

Tom Kyte - Wed, 2016-11-09 11:46
I have created procedure with the our parameter as type CREATE OR REPLACE TYPE char_array AS TABLE OF varchar2(32000); create or replace procedure GET_INSERTS_PRC(V_TABLE_NAME IN VARCHAR2,OUT_inserts_TYP out char_array ); I can able to run...
Categories: DBA Blogs

Stale statistics

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, I've been asked to review performance on one of our databases, and one of the issues I found was the statistics have been locked since 2011 and are considered stale. My recommendation was to back them up (as a rollback point) and upda...
Categories: DBA Blogs

Sql Loader (NULLIF and NVL(TO_NUMBER) - NULLIF not yielding the correct result. The columns is not being set as NULL but loaded with 0.0.

Tom Kyte - Wed, 2016-11-09 11:46
I have the following Sql Loader Control file : POS_COST_PRC_FUND POSITION(173:202) DECIMAL EXTERNAL NULLIF V4_NULL='?' "NVL(TO_NUMBER(LTRIM(:POS_COST_PRC_FUND,'0')),0)", V4_NULL FILLER POSITION(203:203) CHAR, The column in Oracle is defined a...
Categories: DBA Blogs

APEX 5.0 ORDS 2- Tomcat 7 - Errors on tomcat log and performance issues

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, We have implemented Apex 5.0 as our new Developing tool which is a real great tool. The configuration is on HP/UX, tomcat, ORDS and APEX on Oracle 11g r2. We have 4 environnements (dev, qualification, preprod, prod). On the 3 first ...
Categories: DBA Blogs

REST Enable Java or JavaScript in the Database

Kuassi Mensah - Wed, 2016-11-09 10:41
REST Enable Java or JavaScript in the Database
The Oracle REST Data Service (ORDS) allows you to turn Java stored procedures or JavaScript stored procedures into REST Web Services that you may publish these in the Oracle REST Data Service.
See more details @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html
Download and Configure ORDS
1) First step: download the latest ORDS  @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

2) Extract the zip file in a directory say ../ords.3.0.8

3) Configure and Install

$ ls 
docs logs params examples ords.war readme.html

Navigate into params directory and edit the ords_params.properties file.
Modify the standalone.http.port value to a desired port # (e.g., 8090) and save the file.

$vi params/ords_params.properties
#Tue Jul 26 05:23:16 UTC 2016
"params/ords_params.properties" 17L, 467C                     1,1           All

Note: Do not modify any other property

$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/u01/oracle/ords.3.0.6/ordsi
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:pdb1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:Welcome1
Confirm password:Welcome1
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Sep 07, 2016 3:53:19 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_core_2016-09-07_035319_00534.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_datamodel_2016-09-07_035342_00050.log
Completed installation for Oracle REST Data Services version Elapsed time: 00:00:23.840

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
2016-09-07 03:54:28.867:INFO::main: Logging initialized @158428ms
Sep 07, 2016 3:54:29 AM oracle.dbtools.standalone.StandaloneJetty setupDocRoot
INFO: Disabling document root because the specified folder does not exist: /u01/oracle/ords.3.0.6/ordsi/ords/standalone/doc_root
2016-09-07 03:54:29.525:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No encryption key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No mac key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: Updated configuration with generated keys
2016-09-07 03:54:29.793:INFO:/ords:main: INFO: Using configuration folder: /u01/oracle/ords.3.0.6/ordsi/ords
2016-09-07 03:54:29.793:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/oracle/ords.3.0.6/ordsi/ords, services=Application Scope]|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: |apex|pu|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: |apex|pu| is correctly configured
2016-09-07 03:54:30.298:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version :|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2016-09-07 03:54:30.305:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@429bd883{/ords,null,AVAILABLE}
2016-09-07 03:54:30.346:INFO:oejs.ServerConnector:main: Started ServerConnector@b7f23d9{HTTP/1.1}{}
2016-09-07 03:54:30.348:INFO:oejs.Server:main:
Started @159913ms   
4) Allow your schema to use ORDS 

SQL> exec ords.enable_schema;

Define and Configure Your ORDS Service
Let's use the JavaScript procedure defined in an earlier blog post @

Rem Create a procedure based on the select.js and it's javax.script wrapper (see the previous blog post)
CREATE OR REPLACE PROCEDURE selectproc(id IN varchar2)
output varchar2(10000);
SELECT invokeScriptEval(id) INTO output from dual;

-- delete load.routes module
p_name => 'load.routes');

-- External JS select query
-- URL: load/routes/nashorn/select
-- procedure: selectproc
p_module_name => 'load.routes' ,
p_base_path => '/load/routes/',
p_pattern => 'nashorn/selectbyid/:id',
p_source_type => 'plsql/block',
p_source => 'begin selectproc(:id); end;'

Let's use the JavaScript procedure defined in an earlier blog post 

Open your  web browser and navigate to  http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/100.

 You must see the JSON document of the employee with empid as 100 displayed.

That's it! You have just created your first ORDS service

The same process can be used tor Java stored procedures or other JavaScript procedures in the database.

Editing PL/SQL with Sublime Text 3 and Multiple Cursors

Tony Andrews - Wed, 2016-11-09 09:16
I'm a recent convert to the Sublime Text 3 editor, having managed for many years with TextPad.  One of the most useful features for me is multiple cursors, which allow you to make the same change to multiple lines at once. For example, it comes in very handy for quickly adding an API over a table (assuming you don't have a nice utility to generate one for you already): 1. Describe the table andTony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/11/editing-plsql-with-sublime-text-3-and.html

Oracle Cloud Helps Organizations Discover Profit Winners

Oracle Press Releases - Wed, 2016-11-09 07:00
Press Release
Oracle Cloud Helps Organizations Discover Profit Winners Organizational managers and finance executives can easily identify hidden costs and profits with new profitability and cost management solution delivered in the cloud

Redwood Shores, Calif.—Nov 9, 2016

Oracle today announced a purpose-built solution for finance executives and organizational managers to understand specifically what drives profitability, continually assess how resources are being consumed, and take action. Although under constant pressure to improve performance, many organizations lack visibility into what drives cost and profit. Cost, revenue, and operational systems are often fragmented, unconnected, and dispersed, making it difficult to understand the true profitability and costs of the business.

Oracle Profitability and Cost Management Cloud (PCMCS) enables business users to understand the root causes behind costs and revenues. Users can quickly and easily determine the profitability of customers, products, geographies, and sales channels, and take action to improve profitability and lower costs.

“What you don’t know about where your business is making and losing money can really hurt you. In today’s high-paced and increasingly competitive global business climate, it’s essential that your organization focuses resources and cash on the right priorities,” said Hari Sankar, group vice president of enterprise performance management at Oracle. “PCMCS enables organizations to easily identify their profit winners and address inefficiencies, ultimately improving business agility and profitable revenue growth, while minimizing costs.”

Built for the Cloud, Oracle PCMCS delivers new capabilities and innovation in the Cloud, while also leveraging best practices from Oracle’s on-premises offering, Hyperion Profitability and Cost Management.  The solution allows organizations to keep their general ledgers “thin,” fast, and efficient by performing detailed calculations outside the general ledger. Business users can identify profit potential and areas of cost savings with simple one-click reports and dashboards, without reliance on IT. The Cloud solution delivers pre-built reports including profit curves, scatter plots, and traceability, and includes features for detailed financial modeling and ad hoc reporting. Oracle PCMCS handles large data sets, and easily integrates with multiple Enterprise Resource Planning (ERP) systems, as well as with Oracle Enterprise Performance Management (EPM) Cloud and on-premises offerings.

No matter the industry, Oracle PCMCS helps determine where best to spend limited investments and scarce resources to get the biggest impact:

  • Financial services: Banks can gain visibility into complex cost allocations and calculate costs down to product, consumer, and branch.
  • Healthcare: Organizations can provide accurate charge coding in patient profitability.
  • Higher education: Institutions can view the full cost of a course by calculating everything needed to deliver the course, including the cost of building the course.
  • Telecommunications: Companies can apply network costs directly to a product that consumes actions on that network, such as minutes or data.
  • Transportation: Organizations can calculate all costs of moving a plane from point to point, including capitalization on plane, crew, and station costs at each airport, which can be allocated based on factors like number of passengers carried or number of flights taken.
  • Utilities and public services: Users can allocate all shared costs, such as facilities and support staff, into services required while providing transparency for government oversight.

“Businesses today can struggle with knowing exactly where their profit comes from, as traditional financial statements don’t tell the full story, and general ledgers can be inefficient and cluttered,” said Mike Willhelm, managing director at Huron. “Oracle PCMCS enables businesses to easily get insights into profits and costs and act on them quickly. In addition, it integrates well with ERP business processes.”

“The Oracle Profitability and Cost Management Cloud Service strikes the perfect balance between configurability and ease-of-use, and is a welcome addition to Oracle’s market-leading family of cloud-based Enterprise Performance Management applications. It’s the ideal solution for a wide range of allocation based business processes, including management reporting, product and customer profitability, shared service costing and chargebacks, and operational transfer pricing,” said Mike Killeen, senior vice president at Edgewater Ranzal.

Oracle PCMCS is available now for existing and new customers. Businesses can choose flexible deployment options to best fit their needs. For additional information, visit Oracle Cloud and connect with Oracle EPM Cloud on Twitter.

Contact Info
Joann Wardrip
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.


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

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Joann Wardrip

  • +1.650.607.1343


Subscribe to Oracle FAQ aggregator