Tag Archives: developer

How To Get Those Customizations to Work Correctly with Oracle EBS R12.2 Editioning (EBR)

Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.
Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.

Scenario:
Creating a new view based upon the AP.AP_SUPPLIERS table to reside in a new XXCUSTOMSCHEMA.

If you attempt the simple:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers ;

It works, but your data and table definition may change whenever patching editions are in-play.

If you attempt to just reference the editioned object instead:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers# /* or the preferred apps.ap_suppliers synonym */

You will receive the ominous ORA-38818: illegal reference to editioned object error message.

What’s missing? The XXCUSTOMSCHEMA isn’t recognized as an edition-friendly schema by Oracle’s e-Business Suite (nor the database), yet.

Create your XXCUSTOMSCHEMA in the usual way:

create user XXCUSTOMSCHEMA
identified by (password)
default tablespace USERS
temporary tablepace TEMP
profile DEFAULT
account UNLOCK;

/* as APPS user */

exec FND_ORACLE_USER_PKG.LOAD_ROW(‘XXCUSTOMSCHEMA’, ‘CUSTOM’, ‘INVALID’, NULL, ‘N’, ‘B’);

/* as SYSTEM user */

alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER(‘XXCUSTOMSCHEMA’);

/* Create your SELECT grants to the editioned objects as APPS user */

grant select on ap.ap_suppliers#  to XXCUSTOMSCHEMA;

/* Create your custom view referencing the APPS synonym – which points to the editioned object */

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM apps.ap_suppliers ;

This is the same process when creating procedures and functions with the usual caveat that unless it’s a 12c database, you cannot create materialized views on the editioned objects.

For the 11gR2 EBS users, you would stick with the existing non-editioned table object reference:

CREATE MATERIALIZED VIEW APPS.XXAP_SUPPLIER_NEW_MV
(
vendor_id,
vendor_name
)
AS
SELECT aps.vendor_id, aps.vendor_name
FROM ap.ap_suppliers aps;

/* The owning schema for the materialized view must have CREATE TABLE, VIEW AND INDEX privileges on the destination tablespace */

Advertisements

IOUG Podcast 30-APR-2013 Database 12c Beta Revelations at Collaborate 13

For the week of April 30th, 2013:

An Interview with Bobby Curtis and James Lui:

  • Database 12c Revelations at Collaborate
  • A Little Insight into Oracle’s Beta Program

IOUG Podcast 30-APR-2013 Database 12c Beta Revelations at Collaborate 13

Subscribe to this Podcast (RSS) or iTunes

Transcript:

Hi, I’m James Lui,  a senior applications DBA working at Aramark Uniform Services. and I’m Bobby Curtis, a Solution Architect with BIAS Corporation, Oracle Platinum Partner.

[James] We just finished an Introduction to 12c New Features and Functionality. One of the reasons we wanted to bring this to the Users Group’s attention.is that out on the internet, we were Google-ing for the new Database 12c we wanted to find out, it’s not a general release right now; it’s not generally available.  But there are Oracle Beta test partners out there looking at it, whacking it and trying to make it work. But what we did find was a little disturbing.There’s a lot of stuff dating back to early last year (2012) that partners that are on these Betas are blogging about features that may or may not be in the product. They’re putting SQL code out there that is wrong. That came to our attention when we were talking with other Oracle Partners out here (at Collaborate). What do we do about this? So our position on this was to try to start elevating the invisibility of what is happening out there with regard to NDA Beta Partners, like Bobby and I, who are are subject to… You can’t talk about it, or you can’t disclose stuff that isn’t real.

The feature sets in the final release are subject to what Oracle wants to try to present as a solid product that works to end-user customers. Not something that the wheels are going to fall off.just because they tried it (they put some spinners on it) and it ends up falling off and it’s a bad idea.   The whole purpose for this idea is we don’t do GA on an unstable product. Let’s let experts hammer at this thing. See if it works, get an idea from a users group perspective (trying to represent as many people, as possible) what features work, what doesn’t work. and what needs to be fixed before the thing goes live.

[Bobby] That way the users understand what they’re really getting versus what the hearsay out there.. Because everything that’s been posted out there (on the blogs) violates the NDAs. and Oracle can pull things (features) out and nobody really knows it because they’re relying on older postings that were not authorized anyway.  So this session was to provide the users groups with something based upon here’s what we’ve seen, and here’s what we think is going to be in there, but it’s not guaranteed.

[James] Realistically we do know that forward information is good. Telling people that the documentation that will be provided is more massive than ever before. It’s going to give you direction as a DBA, developer or manager trying to make a decision to adopt this product. There is more to read than ever (before.) There’s more room for books to be written than ever. There’s definitely more room for webinars about this product. They’re (Oracle’s) thinking about at least 500+ new feature functionalities that are really exciting stuff. And we covered maybe…

[Bobby] Like, 25 of them — the tip of an iceberg on this product.

[James] But in terms of what that final product ends up being, when Oracle decides it’s ready for market, we had the confidence as users group representatives (ODTUG, IOUG, UKOUG, Australia OUG) when we hammered it to death, these features worked, with the caveat of saying “Fix this ” then we’re (users groups) happy.

That’s the idea behind this session. Telling people that going forward, you may need to go back to the drawing board or to school to find out how to use this product in the best way.

But the exciting part is here at Collaborate, IOUG was able to give those who attended our session a preview of the future in a very substantial technical way.

[Bobby] And so we thought it would be a good idea, so okay there’s a lot of talk about it, everybody’s asking when’s it coming out, what’s going on? So from a users group perspective, let’s give everyone not so much a peek into it, but to say here’s what’s coming from what we know of, from looking at the Betas.

But, again at the same time letting everyone know that just because we say something’s coming, Oracle could pull the plug (on that feature) at some point-in-time and say, “That’s not going to be added in this release. “ So basically, this was a way to give to the users groups, who have been saying, “We’ve been talking about it for six months. But what’s really in it?” And as we alluded to earlier, we’ve only touched the “tip of the iceberg” – we only did 25 of them (the new features). And we didn’t do a Top-10 or anything like that.  We just said, here are the 25 features we tested during Beta testing, and here’s what our thoughts are. So we think we gave the users groups a good base of understanding where it’s (Database 12c) coming from, and what can be potentially looked at, and what they could use.  And from the reaction we got from the crowd, was actually pretty good.

[James] And Bobby represents the partner, implementer, system integrator world of the consultants that are out there who are going to be challenged with actually upgrading (systems), or doing the installations. I represent the existing customer base of Oracle, we’re an Applications e-Business shop; we want to know what does that roadmap affect for us. What does 12c give us to save money basically as an organization? So between the two of us, we actually do cover a big part of that world. and we were very heartened by the (experiences) during the Beta testing (we were Phase 3, probably the last (Beta) before General Availability), we’re pretty happy with the product. Perhaps, shockingly good?

[Bobby] This is actually by far the best release they (Oracle) have come out with on a first release. Because they’ve listened to the users groups, and took a lot of the things that we wanted, put it into it (Database 12c.)

[James] And by being part of the Beta Program, the people behind the Beta Program brought the user groups in confidence, to say you people represent key members of the user community. Hit this thing until it breaks and tell us what it’s going to need for this product to go to General (Availability).. And that’s what we felt we did. We did things that were very unexpected

[Bobby] Any time you get a (Database) developer standing behind your shoulder… because that was one of the really impressive things behind the Beta testing. You had 9 testers, and Oracle put 50 of their people in the room with us. and when we broke it, they literally pulled the code open and looked at it and said, “Here’s why it’s broken.” And that in-turn gave Oracle the insight to say, “Okay what did you guys do?” versus “How did it break?” So that way they can go fix the code and can be more responsive to what users are going to do out in the marketplace.

[James]  And while it’s not a generally-accepted practice, nor a best practice idea, I as an e-Business Suite Customer ended up bringing in a live R12.1.3 version instance. Not certified. Not supposed to be certified. Certainly Oracle’s own (e-Business Suite) Development group behind Applications Technology does not certify you’re supposed to be doing (Database) 12c upgrades. But in the context of a Beta, for me this is the exciting way to do it. You take a sandbox instance, you take a copy of a live R12 instance and you try to do the upgrade. The only things that broke, was the stuff that Oracle told us would break, because they de-support those particular features.  That was the guarantee that makes it whole.

[Bobby] They listened and they fixed it. So that when GA comes out, you can take it to Production if you want.  I think that’s what a lot of people are going to be shocked about, because most organizations wait for Release 2, to be honest. But Release 1 of (Database) 12c, we think, from the user group perspective could be production-ready and be used.

[James] That’s better than most other software companies that try to be first to market with a new feature, and let the user community test it and find out where the bugs are. That was a different philosophy that we met (at Oracle). And we know the Beta 1 and Beta 2 release testings uncovered thousands and tens of thousands of problems that got fixed by the time we got our hands on it. The improvements that Oracle has actually made in that process  That’s a good step in the right direction, and we would hope every software vendor actually does that same thing.  Because IOUG being very agnostic about what the user community needs to make a business solution happen, having Oracle do that just makes their job easier.

See also:

Oracle Enterprise Manager Tips & Best Practices 2013

IOUG Podcast 15-SEP-2012 Itanium’s Back

IOUG Podcast 15-SEP-2012 Itanium’s Back / Java 7 Cloudy Bugs / Fusion HCM Taleo Talent http://ow.ly/1mtScj

IOUG Podcast 31-AUG-2012 Express and 11G

IOUG Podcast 31-AUG-2012 Express and 11GR1 Go Bye-Bye / New GoldenGate & PostgreSQL MMR http://ow.ly/1mfmDp

IOUG Podcast 24-AUG-2012 Rumors of MySQL

IOUG Podcast 24-AUG-2012 Rumors of MySQL’s Doom by Oracle / Design Piracy http://ow.ly/1m9bef

IOUG Podcast 17-AUG-2012 DBAs and Develo

IOUG Podcast 17-AUG-2012 DBAs and Developers as Leaders / OpenWorld Tech Sessions http://ow.ly/1m29G8

IOUG Podcast 10-AUG-2012 The Big Data Wo

IOUG Podcast 10-AUG-2012 The Big Data World of the Data Scientist & The DBA http://ow.ly/1lVhxb

IOUG Podcast 03-AUG-2012 OIM11gR2 / OBIE

IOUG Podcast 03-AUG-2012 OIM11gR2 / OBIEE 11.1.1.6.2 / NoSQL 2.0 Beta / OpenWorld Update http://ow.ly/1lOWy0

IOUG Podcast 28-JUL-2012 London Olympics

IOUG Podcast 28-JUL-2012 London Olympics: Remembering the Birth of the Internet http://ow.ly/1lHMCq

IOUG Podcast 21-JUL-2012 A Disasterous D

IOUG Podcast 21-JUL-2012 A Disasterous Day in the Life of a Cloud DBA / Cloud GPS http://ow.ly/1lwxrS

IOUG Podcast 14-JUL-2012 DBA Ch-ch-chang

IOUG Podcast 14-JUL-2012 DBA Ch-ch-changes / You’ve Got Big Data! http://ow.ly/1lmzHz

IOUG Podcast 06-JUL-2012 OEM Ops Center

IOUG Podcast 06-JUL-2012 OEM Ops Center 12cU1 / Google sends $4 million bill to Larry http://ow.ly/1lb9qE