Feed aggregator

External Tables with multiple datafiles

Tom Kyte - Wed, 2017-02-15 23:26
Tom, I suspect this will be a simple matter to answer, but I haven't found this discussed elsewhere. Given an external table with multiple datafile locations: <code> create table multi_loc_ext ( ... ) organization external ( ...
Categories: DBA Blogs

Creating dynamic stored procedure problem

Tom Kyte - Wed, 2017-02-15 23:26
Here is my first dynamic stored procedure and I have some errors when trying to compile it. Please help. I am new in ORACLE. Thanks a lot Line 12 PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exceptio...
Categories: DBA Blogs

create SQL to fetch all records of table and chunk them to write in flat files.

Tom Kyte - Wed, 2017-02-15 23:26
Hi, I want to create SQL to fetch all records of table and chunk them to write in flat files. Suppose take an example,if table contents 2000 records then write sql to fetch all of records and write 1000 records in one flat file, other 1000 recor...
Categories: DBA Blogs

Measuring stored procedure with AUTOTRACE ON? (followup question)

Tom Kyte - Wed, 2017-02-15 23:26
Hi Team, I posted a question regarding dynamic update SP earlier last week: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533310000346559369 And as a result of that, I have 2 versions of Stored Procedure for each SP i'm m...
Categories: DBA Blogs

PeopleSoft Cloud Manager - Now Available

PeopleSoft Technology Blog - Wed, 2017-02-15 16:17
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

We’ve been talking about running PeopleSoft Applications on the Oracle Compute Cloud ever since the release of PeopleTools 8.55.  In release 8.55, we changed the way PeopleSoft is packaged and installed, using automated scripts for deployment and configuration. We call that the PeopleSoft Cloud Architecture.  Today, we make it even easier by automating processes like moving on premise environments to the cloud, provisioning of environments, defining topologies, cloning environments and maintaining application instances.  You could do these manually but that takes a lot of time and resources.  Now they are all automated by PeopleSoft Cloud Manager.

What are the basics?

This is not PeopleSoft running as a SaaS application.  There, got that out of the way.  This is you, moving your PeopleSoft Application from on premise to the Oracle Compute Cloud Service (also referred to as Oracle’s Infrastructure as a Service (IaaS)).  You move the database, application tier, web tier, app sources, tools source, everything that makes up a PeopleSoft application and place it in the cloud.  You can do this for any of your instances, demo, dev, test, training, regression, sandbox, conference room pilot, and even production. Take it one step further, and you can put your database on the Database Cloud Service and let Oracle manage patching the database for you.  This is your application with your configurations, your customizations, your security profiles, and your data, and it’s running on the Oracle Cloud.  

PeopleSoft Cloud Manager

PeopleSoft Cloud Manager helps you run PeopleSoft Applications on the Oracle Compute Cloud.  You’ll find it on the Oracle Marketplace and it’s free of charge.  Just load it on to your Oracle Compute Cloud and start running. First thing you’ll want to do is get an environment.  That’s what Lift and Shift is for.  'Lift and Shift' packages up your on premise environment and copies it to the Cloud.  Once there, define a topology to identify all the resources your application will use, and automatically deploy it.  From on premise to the cloud, fully automated, in a very short time. You don’t have to move all your PeopleSoft environments to the cloud.  Think of this as an extension of your data center.

Once you’ve got PeopleSoft Applications running on the Cloud, PeopleSoft Cloud Manager gives you a great collection of utilities and administrative features to manage them.  You can start and stop them (of course!).  If you don’t want one anymore, you can delete it.  If you want another copy of an environment, you can clone it.  And of course, none of this is worthwhile unless you can maintain them.  Simply subscribe to application and tools changes, and anytime PeopleSoft publishes a changed (PRP, PUM image, PeopleTools patch, PeopleTools release), it is automatically copied to your Cloud File Repository and ready to be applied.  No more poking around My Oracle Support waiting for changes to be posted!

If you’ve been thinking about moving some of your PeopleSoft operations to the cloud, you’ll want more information about Cloud Manager.  Of course, all of that can be found on the PeopleSoft Information Portal.  For more information about using the Oracle Compute Cloud and Database Cloud Services, talk to your Oracle Technology Sales Representative.

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman",serif;}

Interaction Hub Image 3 Now Available

PeopleSoft Technology Blog - Wed, 2017-02-15 12:29

The latest image (Image 3) of the Interaction Hub is now available on My Oracle Support--PeopleSoft Update Manager Home Page.  To get the image, go to the PeopleSoft Update Images tab, then choose the Interaction Hub Update Images page.

This image contains a couple of beneficial features:

  • Uptake of Elasticsearch    Elasticsearch is the new search engine used with PeopleSoft.  It provides many benefits over previous implementations of search technology.  When Search is used with the Interaction Hub, you can have federated searching across all PeopleSoft applications including the Hub.  This means that when you run a search, you can retrieve results from all content sources.  Learn more about Elasticsearch with PeopleSoft from our Key Concepts page on Search.
  • Fluid Related Information for Content Management    This is functionally similar to Classic content management in Related Content.  There are three main aspects to this feature:  1) It enables users view all sorts of content from conventional articles, to videos or documents.  2) It provides a setup page for administrators to assign or update content, including context-specific content.  3) Enables administrators with extra privileges to create or update content management assignments, specify a role that can serve as content administrator, mark content as context sensitive, and turn on or off online content update capability. 

    Such managed content can be included in the Related Information from or in a page as embedded Related Information.  See PeopleBooks guidance on Related Information.
Information on features coming in future images will soon be posted on the Interaction Hub page on peoplesoftinfo.com.

Custom Component in Sites Cloud Service with Static Files Referenced

WebCenter Team - Wed, 2017-02-15 09:18

Authored by Carlos Picazo, EMEA Digital Specialist Presales team member, on his personal blog "Carlos' Technology Corner".

Custom component in Sites Cloud Service with static files referenced

Today’s post objective is to learn how to develop a Custom Component in Sites Cloud Service that makes use of static files (in our case, a default image that can be override by the contributor) and how to reference the static files to work both in edit mode and when the site is published.

I’m going to use the same bootstrap theme than in previous posts (Modern Business) and will focus on create a custom component to render this specific HTML snippet (obviously managing the image, text and social network links):

Basically it’s a team member card, but I want to keep the image as the default image and provide the ability to the contributor to select another image hosted in Documents Cloud. Then we have three text that I will convert to make them editable by the contributor and we will play with the different CKEditor toolbars we want to offer to edit each text field. Finally, we will have 3 inputs as settings to configure social network links.

Continue reading for the three inputs and next steps.

pl/sql program to increment/decrement sequences

Tom Kyte - Wed, 2017-02-15 05:06
my question is i am a shopkeeper and i am provide a token number for every customers in database if two and three customers are removed in the database after token number is automatically arrange in sequence.so how to solve this problem in pl/sql
Categories: DBA Blogs

Automatic Memory Management

Tom Kyte - Wed, 2017-02-15 05:06
What is your stance on using AMM on a production system? Several Metalink notes and books recommend its use but that is not always what Oracle support recommends. If there are known issues and AMM is not recommended then I wonder why not have the...
Categories: DBA Blogs

data updaion using bulk collect

Tom Kyte - Wed, 2017-02-15 05:06
Hi Tom , I am bit new to the PLSQL world and trying to write a block that will read clob data and update(only first 4000 byte as we are still using 11g) to a varchar column . The block is ready but when I execute with some good number(close to 70 K) ...
Categories: DBA Blogs

SQLLDR fails on data specified as filler in control file

Tom Kyte - Wed, 2017-02-15 05:06
I have a sqlldr control file that looks like this name char(255) enclosed by '"', contactemail char(255) enclosed by '"', rawtext1 filler enclosed by '"', rawtext2 filler enclosed by '"', auditdate ...
Categories: DBA Blogs

ORA-02089: COMMIT is not allowed in a subordinate session

Tom Kyte - Wed, 2017-02-15 05:06
Hello Tom, I got an issue above. I have some stored procedure which enables/disables some Oracle queue, procedure is being called from java, java method is wrapped by '@Transactional' annotation. It opens the main transaction, where my db procedu...
Categories: DBA Blogs

Servers for Pratice

Tom Kyte - Wed, 2017-02-15 05:06
Hi, Just visited the site livesql.oracle.com, look like it is very good environment for developers to improve their skills in Coding. I am learning RAC & Apps DBA concepts.Is there any environment to implement Apps DBA & RAC concepts like liv...
Categories: DBA Blogs

Facing error while fetching partition_position from all_tab_partitions

Tom Kyte - Wed, 2017-02-15 05:06
Hi Tom Am facing trouble with a simple query. Basically, my ultimate goal here is to purge the data older than 2 years for few tables(which I have mentioned in purge_table_list table). I have renamed all the partitions to the format of last 7 ch...
Categories: DBA Blogs

Dropping all objects under a schema

Tom Kyte - Wed, 2017-02-15 05:06
Hi Connor, The below query will drop all the table which are present in the current user A's schema (normal scenario login using user 'A'). <code> select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in...
Categories: DBA Blogs

Subtract 2 timestamps to find the difference in hours

Tom Kyte - Wed, 2017-02-15 05:06
Hi, How can I find out the time difference in hours between 2 timestamp with timezone I tried below one <code> select extract(HOUR FROM diff) from ( select '2/14/2017 3:39:15.097484 PM +00:00' - '2/14/2017 1:39:15.097484 PM +00:00' as dif...
Categories: DBA Blogs

ORA-00936: missing expression Solution

Complete IT Professional - Wed, 2017-02-15 05:00
Did you get an ORA-00936: missing expression error? Learn what it means and how to resolve it in this article. ORA-00936 Cause The error you’ve gotten is this: ORA_00936: missing expression Oracle’s official “cause and action” that appears along with the error is: Cause: A required part of a clause or expression has been omitted. […]
Categories: Development

Webcast: "Planning Your Upgrade to Oracle E-Business Suite 12.2"

Steven Chan - Wed, 2017-02-15 02:04

EBS 12.2 upgrade webcastOracle University has a wealth of free webcasts for Oracle E-Business Suite.  If you're looking for help with planning your upgrade to EBS 12.2, see:

Anne Carlson, Senior Director Applications Technology, discusses key upgrade planning considerations, combining lessons learned from customers with practical advice from Oracle’s Development, Consulting, and Support organizations. Understand how to build the business case, identify needed time and resources, prepare business and IT staff for changes, plan for required system changes, create an effective test strategy, and more. This material was presented at Oracle OpenWorld 2016.

Categories: APPS Blogs

Links for 2017-02-14 [del.icio.us]

Categories: DBA Blogs

Financial Reports - which tool to use? Part 1

Rittman Mead Consulting - Wed, 2017-02-15 01:00
Financial Reports - which tool to use? Part 1

One of the treats of working in the Business Intelligence world is that we are asked to analyze different aspects of a business. In fact, we are asked to analyze many different types of businesses, too. Most of us using BI tools have come from some previous background. Be it Marketing, Finance, Supply Chain or any other, we most likely had work experience before we got here. Maybe one of our jobs even led to Business Intelligence. The fact is, we are not experts in all areas. It would take several lives to make such a claim, because each area can be very complex and take years to master. The truth, for most of us, is that we have our favorite areas. They are often related to what we are most familiar with.

Financial Reports - which tool to use? Part 1

Over time, I came to really appreciate how simple numbers can be, and developed this - hard to understand - favoritism towards financial reports. While some business areas can be artistic and even vague, numbers are never vague. I have a great appreciation for that. Working with numbers is always precise. In the end, they have to match. No matter how great your report looks, if the numbers don’t add up the report is always wrong. Plus, financial layouts are generally very defined going in, so there is little room for error.

Financials in OBIEE

So, the endeavor begins when you are a BI consultant and everything is supposed to add up properly and look very nice. OBIEE is an extremely powerful tool, and this gives users the impression that it can solve all problems. While it can solve most problems, it falls short on some key features needed for easy financial reporting. That is not to say that Financials can’t be handled in OBIEE - but it is definitely to say that it is not easy.

So, if financial reports are not easy to create in OBIEE, than we are left with two very simple options:

  1. Struggle through it and make it happen

  2. Choose another tool

I have made the mistake of choosing option 1 some times, but quickly realized that option 2 couldn’t be as bad. Countless times, I have been asked to create financial reports in OBIEE. Of course, they needed to tie up and match a specific format: they needed to have blank lines inserted between one section and another, and the alignment of the categories was very important. They often required very detailed variance calculations, so that a company could see where they stood as far as change overtime. Variance percentages are key on these types of reports, and if you have dealt with them in OBIEE, you know that different types of variances and their grand totals can often pose challenges for report writers.

So, in order to accomplish the formatting needed, you end up adding extra code here and there, in essence trying to make OBIEE do something that it’s not supposed to do. Soon, you are experiencing performance issues and a new array of considerations are in place. You start removing your “special code”, then you loose your formatting. The numbers on your financial statement are still correct, but your report looks something like this:

Financial Reports - which tool to use? Part 1

While, in reality, you were trying to get here:

Financial Reports - which tool to use? Part 1

** The Balance Sheet above was created using HFR for illustration of formatting only.

Looking at a different OBIEE financial report (below), you will see that a lot of formatting can be done in these reports, but they will always look like OBIEE reports, if you know what I mean.

Financial Reports - which tool to use? Part 1

In this example, the first column is out of order - as far as Income Statements go. This was left alone on purpose to display one of the issues with creating these statements in OBIEE. The tool does not easily allow you to choose which items will go in each row. So, in the criteria tab, in Answers, you choose the order of the columns, but if you need the rows in order, you will need to either:

  1. Use a hidden column created just for sorting purposes

  2. Leverage selection steps, or

  3. Create a measure column for each row that you will need, use a pivot table, and add the Measure Labels as rows on your pivot

I will illustrate the third option, as it is my preferred way of ordering rows. Suppose that you have a very simple criteria tab such as this:

Financial Reports - which tool to use? Part 1

Naturally, your results would default like this on a table:

Financial Reports - which tool to use? Part 1

If you use a Pivot table instead, you can drag your Measure Labels onto the Rows:

Financial Reports - which tool to use? Part 1

And now, you will be able to see your measures as rows. You can easily reorder them as needed by just moving the order of the columns in the Measures section of your Layout editor.

Financial Reports - which tool to use? Part 1

This seems like a simple solution if you know precisely what all your rows should be, and even better, if you don’t have a huge amount of measures on the report. In real life, this type of row ordering is high maintenance:

  1. You must label each measure to match the account category name for each row

  2. You must filter each measure by its account category (or account number)

  3. If the account category name changes in your DB, you must manually rename your columns to match the new naming convention

  4. If you add or delete account categories, you must manually add and delete columns from your report

OBIEE 12c offers a great improvement in this area: the ability to “save columns” is described very well by Jason Baer on this blog: https://www.rittmanmead.com/blog/2016/01/my-favorite-obiee-12c-feature-that-almost-no-one-is-talking-about/

With the new release of the product you can save as many financial columns as you would like in the web catalog, which allows you to reuse them. As a consequence, you will streamline report maintenance by updating the columns’ format and formula directly from the catalog (instead of inside every report). In fact, if you are spending too much time maintaining your existing reports out of OBIEE 11g, you will automatically benefit from an upgrade to 12c just based on this single feature. Check here for more info: https://www.rittmanmead.com/obiee-12c-upgrade/

This is a great improvement, but you will still need to deal with an overall lack of flexibility for dynamically adding and deleting columns, setting orders, adding blank space, indenting and calculating variances along with proper grand totals.

After spending more time than you should in order to create a simple report, you really start considering other tools. If you are already working in the Oracle stack, the obvious choices will be BI Publisher and Hyperion Financial Reporting (HFR).

Financials in Essbase/HFR

Hyperion Financial Reporting (HFR) brings a powerful solution to financial statements, because it allows you to create pixel perfect reports that are pre-aggregated in an Essbase cube. Just with that, two big problems were just solved: formatting and performance.

In the example below, you see that HFR allows you to place metrics on both sides of the Account Category (butterfly layout - difficult to accomplish in OBIEE):

Financial Reports - which tool to use? Part 1

In addition to formatting and performance, there are some definite pros to consider when choosing HFR:

  1. The calculations in HFR dynamically reference cells, as in excel. So, if a cell changes, the cells that are referencing the original cell will automatically be updated

  2. HFR has the ability to create financial books and batches, and also has a powerful bursting feature

HFR is a great solution for Income Statements, Balance Sheets and other reports that come from Essbase cubes. In a simplistic way, an Essbase cube is a combination of tables that have been joined and pre-aggregated. Since most tables coming out of a financial module in a system can often be joined, you should be able to create Essbase cubes to use as a source for your HFR reports. You will rarely have a requirement that cannot be handled by HFR and Essbase, but some situations may be problematic, for example, if your report requires a measure to be entered at run-time, if results from multiple cubes need to be added, or if your layout is very complex. This is why :

In an HFR report, you start by inserting a grid onto your report and then you associate that grid with a specific Essbase cube. If you need data from two cubes on the report, you can insert another grid and associate that with the second cube. You can also create a report that leverages calculations between existing grids (for the purpose of doing math with two or more separate cubes):

Financial Reports - which tool to use? Part 1

Many thanks to my collegue, Mark Cann https://www.rittmanmead.com/blog/author/mark-cann/, for working through this solution with me

The challenge here is that you may end up with multiple layout grids on your HFR report, which will complicate the report creation and maintenance going forward. It is important to know that if your requirements call for strange off-setting of cells and multiple different looking blocks, then HFR may not be the best tool for the job. If you choose HFR for this purpose, you will spend too much time trying to make things right.

Financial Reports - which tool to use? Part 1

*This is a simple Essbase implementation with 2 cubes (or databases): a Balance Sheet and an Income Statement cube.

The fact is, some financial reports are very tricky and do not come solely from a Financial module. For example, if your company is evaluated monthly for a line of credit, your bank may require to look at several components of your business in order to determine the amount that you can borrow. They will base their decision not only on your monthly revenue, but also your liabilities, such as accounts payables, and some of your assets, such as inventory. What they ask for really depends on their internal lending requirements, and also on the type of business that you have. These are, therefore, highly customized reports that never come out-of-the box anywhere. For this reason, most companies spend a lot of man hours creating these reports as a huge excel report, after the employees have managed to pull information from many different modules together.

These excel “monsters” do the job. They are accepted by the banks, and will get you that loan. On the downside, they need to be redone every month and will drag resource hours out of profitable projects. The flat excel files are also prone to mistakes, as the values are manually keyed in each time. If you make a mistake favorable to the company, your bank will look at it as a very negative issue. If you make an unfavorable mistake, you will not be able to borrow as much as you qualify for. This is a no win situation, so the reports must be accurate every time.

Financial Reports - which tool to use? Part 1 To check for accuracy, there is nothing like testing overtime. But, since you must rework the report each month, you don’t have that opportunity.

The solution is to create a template that will pull from all of these different modules, calculate the numbers, add the results automatically to a pixel perfect formatted report. Over the development cycle, these mappings and calculations will be thoroughly tested, and then they will only be reused going forward.

While you may spend some time pulling this logic together, you will only have to click a few buttons after you are done, for months or years to come. In fact, I have clients that have been running reports such as this one for years. They have been saving a couple of weeks in report creation every month.

Let’s look at an example of what I am talking about:

Financial Reports - which tool to use? Part 1

On this report, each number (disguised as $1234) has been mapped to a calculation that will be pulled dynamically, according to the date entered on the prompt. The inventory amounts are adjusted according to banking requirements, and a rate is allocated depending on the row. This amount is later added/subtracted from receivables and existing contracts. Most of these numbers were created as separate OBIEE analyses. Some amounts could even be tied into web services to get the daily futures prices to estimate the value of contracts when the report runs. All lines are considered in the final equation before the total borrowing amount can be calculated. Per this bank’s requirement, this form needed to be printed and signed, then submitted monthly.

Lending/financing reports may be the most tricky, and the most time consuming for companies to generate every month. The reports may be required by the bank, or by a company that is leasing or financing valuable equipment to your company. These reports need to show your prospective lender everything about your business. They will often need to be done in a format that is specified by your lender. These formats are not negotiable, in fact, some lenders still use old forms that used to be read by a machine.

Here is another small snippet of a financing report that I had to create recently. Now, which tool would you use for 10 different pages of something like this, which required some of the amounts to be entered in the prompt? *Note: the report had to look “exactly” like this:

Financial Reports - which tool to use? Part 1

Well, as I mentioned in the beginning of this article, OBIEE would not be your partner in this type of endeavor. I can guarantee that this relationship would fail: strange formatting with black boxes, line numbers, need for Headers (footers too, not shown here), indenting, etc.

You may consider Essbase/HFR combo, for formatting and performance, but you will soon realize that:

  1. Performance does not tend to be an issue with these reports, as they are generally submitted to lenders on a monthly basis, and therefore can be scheduled to run automatically in the middle of the night.

  2. As mentioned earlier, HFR requires a layout grid to be inserted before the report can be designed. Here, you would end up with multiple grids to handle the calculation of different cells from multiple cubes - which can be cumbersome to create and maintain.

  3. The measures in an HFR report should come from the pre-aggregated cube. In this example, some of the measures were entered as part of the prompt and are calculated at run time. At this point, you must scratch the Essbase/HFR option for this one!

So, now you are still stuck with your monster excel spreadsheet, then retyping the numbers onto the required form.
Financial Reports - which tool to use? Part 1

Before you marry this solution, let me present you with the tool that can do everything: BI Publisher.

Stay tuned for the second part of this blog, when I will share why I believe that BIP can solve the most challenging reporting requirements out there!

Categories: BI & Warehousing


Subscribe to Oracle FAQ aggregator