Ellison's Law

"The value of information increases exponentially as fragmentation is reduced."

Data Consolidation Creates Value

Data consolidation creates the foundation to understand your business operations.

Each type of consolidation provides increasing levels of return:

• Hardware/System Services
• Software/Database (don't forget Licensing and Support fees)
• Business Processes and Functions
• Shared Services/Process Administration

Dealing with ORA-01427 Errors Returned by a Stored Procedure

Recently, I received this error in a email from a Stored Procedure:

ORA-01427: single-row subquery returns more than one row

Usually, I would have liked to receive a line number or some other clue as to where in this 2,000 lines of code that the error occurred (I did not write this procedure) but this is all I have to work with.

Thinking back, ever have a SQL statement that you develop to get data only to find when you go to create a Table with it (via CTAS or the like), only then are you confronted with an error? Well, this was the path I chose to solve this ORA-01427 error.

First, I noticed that this Stored Procedure has a large SQL statement broken into two parts that used a UNION statement. To begin, I created a simple CTAS statement using the first part of this SQL. It created the table without reporting any errors. This tells me that the problem was within the UNION statement. When I did the same step with the second part of the SQL, TOAD returned the error and reported the line(s) that were causing the error and even highlighted the line in my Editor.

With this information, I was able to put the fix in place (you will either need to modify your SQL or you have bad data) to get the Stored Procedure running again.

As any system "matures" with new data, sometimes data scenarios present themselves that were not originally anticipated by the Developer. Just as likely, there are times when you just have bad data and someone in the Business Group needs to correct.

Link to Single Sales Order in EBS 12

In various SSRS reports I have created, Order Number is a popular request. Rather than just display the raw Order Number, why not provide the User the ability to hyperlink to this item in EBS?


To do so doesn't require much - all you need to obtain is the Header_ID and the Order_Number fields and create a new data column using the following string concatenation in your report's SQL:

1:  SELECT <your columns as you would> ,   
2:  'http://<FQDN>:<port>/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE&akRegionApplicationId=660&HeaderId=' || MY.header_id || '&Order_Id=' || MY.order_number || '&addBreadCrumb=Y&oapc=2' as URL   
3:  FROM ...   
4:  WHERE ....  

<FQDN is the fully qualified domain name of your EBS server and its PORT>

Using SSRS for an example, then go to the TextBox Properties of the Order Number data item and choose the Navigation tab. Under the 'Hyperlink Action' select 'Jump to URL' and in the pull-down menu, select your URL column named in your SQL.

You may want to style the Order Number's display and make it BLUE and have it Underlined to remind the User that the item is hyperlinked as SSRS doesn't display this on default.