IMPORTANT NOTE :: THE TIME HAS FINALLY ARRIVED. WITH THE RECENT UPDATES TO 11.5.10 (RUP7 AND POSSIBLY ONE PATCH RELEASE BEFORE THAT), THE PROPOSED METHOD HERE NO LONGER WORKS. IT WAS EXPECTED TO COME AT SOME POINT IF FOR CERTAIN WITH R12 WHERE MOD_PLSQL IS RETIRED FROM THE EBS ENVIRONMENT ENTIRELY. SO FOR A POINT OF HISTORY AND INTEREST, I WILL LEAVE THIS UP, BUT AGAIN IT WILL CEASE TO WORK PRIOR TO ARRIVING AT R12. I HAD HOPED FOR DIFFERENT, BUT THATS OKAY.

If you are looking for a way to build your own Single Sign On to Oracle Self Service Environment, here is an idea that you might like to try (Note : This solution requires 10g RUP patch instance or greater) :

Note: There is a remote chance that the authentication method used here will not function properly under an R12 environment. My guess right now is that R12 will leverage some form of LDAP. However, this is not to be considered a detractor. Reason I say that is because I think that the redirect logic will continue to be valid... only the authentication process would be non-applicable. So, I am suspecting that under R12, I will only need to comment out the authentication logic and leave the redirect logic in place... since under LDAP environments, I would already be pre-authenticated when entering the direct connect logic discussed here. Of course I will have to wait till sometime next year (2008) to find that out, which is when we will start looking at what impact R12 will have to our environment.

I am presuming that if you are reading this, you have some working knowledge of Oracle Applications and how you log into the application. Given that, most everyone knows that we can post the following values to the https://your domain/OA_HTML/fndvald.jsp :

username this is the username your wanting to login with
password this is the password your wanting to login with
rmode this will always be 2
home_url this is the url you wish to be returned to when exiting

Now you might ask yourself, where do I get my password from... you will need to take the responsibility of people maintaining their password on their own, and generate it for them via provided APIs. We store keys which when run against an algorithm match a password associated to that individuals user account. You could build your own encryption algorithm, or you could use Oracle's DBMS_OBSUFUCATION package. However, I won't digress into that any more than what I have said already.

Pages: 1 · 2

Well we finally upgraded to 11g at work. New playground for me to play in. Few things that most are aware of already :

- PLS_INTEGER was the recommended approach for coding when precision was not required. More efficient and faster. 11g brings to the table SIMPLE_INTEGER which is always NOT NULL. Since it is never NULL, then it never checks for that value (or lack of), thus making it even faster than PLS_INTEGER.

- Oracle 11g virtual table columns are columns that are actually functions (create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual), and similarly, virtual indexes that are based on functions REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table.

- Oracle 11g PL/SQL will you to specify trigger firing order.

- The 11g SQL Access Advisor gives partitioning advice, including advice on the new interval partitioning. Interval partitioning is an automated version of range partitioning, where new equally-sized partitions are automatically created when needed. Both range and interval partitions can exist for a single table, and range partitioned tables can be converted to interval partitioned tables.

- New with 10r2 proxy identification in SQL*Plus, the connect command has been enhanced to allow for a proxy, to aid applications that always connect with the same user ID:

connect sapr3[scott]/tiger

- Fine Grained Dependancy Tracking (FGDT)
This means that when you add a column to a table, or a cursor to a package spec, you don't invalidate objects that are dependant on them.

Link: http://momendba.blogspot.com/2009/07/who-says-full-table-scans-are-evil.html

Really good article that demonstrates how full table scans can be better than indexed scans... but as you read on, the real truth emerges as to why. Lesson to be learned... if your statistics are up to date, then the optimizer will do its job in almost all cases... if they are out of wack, thats when you find yourself using optimization hints. So think twice before you apply the optimization hint and promote that to production. Work with your DBAs to identify the root cause.

In my previous life at Papa Johns Corporate, we had fiscal years that did not necessarily match calendar years. To keep track of what date cycles we were in we generated a calendar table. The calendar table had a variety of data stored within it, such as :

* Period
* Start and end of period
* Day of period and days remaining in period
* etc.

Additionally, we threw in a counter field that simply provided an incremental number that could be leveraged for joining and filling in empty rows when joining unlike tables, or for any number of other occassions where you needed a list of sequential numbers.

I brought that table with me when I joined my current employer, and to this date we still use it for variety of efforts as well.

Recently I stumbled on a simple query that provided a list of dates :

SELECT TO_DATE (:x, 'DD-MON-YYYY') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= :n;

Where x is a string formatted date e.g. 03-JAN-2009 and n is number of days in range. Submitting '14-MAY-2009' and 5 would return 5 rows of data beginning with 14-May-2009 and ending with 18-May-2009.

I gave it some thought and wondered if I could convert our existing CAL_TABLE to a dynamic on-the-fly SQL query instead...

Pages: 1 · 2

Link: http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp

Author Comments : Important issue identified regarding the structure of your remote FTP servers banners. Although with a true FTP client tool it may not be required, this FTP process follows best practice and expects that the server codes precede each line being returned by the remote FTP Server as it authenticates to the remote server. Failure to include the required prefix could cause you to be unable to access the remote server or failure to authenticate. See for more info => TCP Banners and then contact the respective server administrator.

Additional information on server codes can also be found here.

It might be possible to enhance the PLSQL solution to work past the issue, however I would not encourage that and instead stay with best practice.