Feed aggregator

11g on Windows (32-bit) is out!

Renaps' Blog - Mon, 2007-10-22 19:42

Thanks to OTN Headlines. I’m downloading Oracle 11g for windows at this moment.

But only the client version, since all Oracle servers I managed runs on Linux.

With the Windows client, I will be able to use cool new features like errorlogging.


Categories: DBA Blogs

Reserve your .NET and Windows OpenWorld Sessions Today!

Christian Shay - Mon, 2007-10-22 11:45

Summary: Seats are filling up fast for Oracle OpenWorld .NET and Windows sessions, so use Schedule Builder and reserve a seat for yourself today!

Oracle OpenWorld will be once again taking over the city of San Francisco from November 11-15. It promises to be a truly amazing experience with over 1,600 sessions covering the full breadth of Oracle's offerings.

As you may recall, last year at OpenWorld we introduced "Oracle Develop", a special 2 day intensive track of content created specifically for the Oracle developer - including a .NET developer track. Well, it turned out to be so tremendously popular in San Francisco that we took the Oracle Develop show on the road to Korea, China, India, Germany, the Czech Republic, and the UK. Now we are back in San Francisco, having learned a lot about what you want in your sessions and we are offering even more of the good stuff that developers can use on their job!

This years Oracle Develop .NET track will provide comprehensive coverage of Oracle's .NET technologies including new Oracle Database 11g features, introductory material, and deep dive content. Oracle Develop is perfect for all levels of Oracle on .NET developers, from beginner to advanced. In addition to sessions we also have a .NET Hands on Lab, which lets you get your hands dirty and take Visual Studio for a joyride! You can attend Oracle Develop if you register for the full conference badge of course, but you could also sign up specifically for Oracle Develop.

Once you register, I strongly urge you to use Schedule Builder to reserve seats in the sessions you are most interested in. I recommend doing so as many of our .NET and Windows sessions look like they are getting close to capacity.

If you can't make it to Oracle Develop content this year, please visit us in the Exhibition Hall (in the database area) for your own personalized demo of our latest features from an Oracle expert.

Oracle Develop .NET Sessions at a Glance

  • Getting Started with Oracle and .NET
  • New Features in Oracle Database 11g for .NET Developers
  • Optimizing .NET Data Access Performance with Oracle Database
  • ASP.NET Web Development with Oracle Database
  • Database Development Lifecycle Management with Visual Studio: SQL, PL/SQL, and .NET Stored Procedure Development, Source Control, and Deployment
  • PL/SQL Programming for .NET Developers: Tips, Tricks, and Debugging
  • Using Complex Oracle Datatypes in .NET: LOBs, User-Defined Types, and XML
  • Building Scalable and Highly Available .NET Applications with Oracle RAC and Oracle Data Guard
  • .NET Data Caching: Using Oracle Change Notification and the Client Result Cache
  • Hands-on Lab: Building .NET Applications with Oracle

Windows Database Sessions
  • What's New for Oracle Database 11g on Windows and .NET
  • Oracle Database Integration with Active Directory and Windows Security
  • Best Practices for Performance of Oracle Database on Windows
  • Using Oracle RAC and Microsoft Windows 64-Bit as the Foundation for a Database Grid
  • Best Practices for Oracle Database and Client Deployment on Windows
  • Oracle Fusion Middleware: Best Practices and Interoperability for Windows, .NET, and Office Environments





See you there!

    Customer Advisory Board for Flow Manufacturing

    Chris Grillone - Mon, 2007-10-22 11:20
    Flow Manufacturing is seeking additional customers for a customer Advisory Board (CAB).

    CAB membership is an opportunity to directly influence design requirements for future releases and the product roadmap, and network agmonst other Flow Manufacturing customers (best practices).

    If you are interested in joining our CAB, please contact your account manager or contact me directly.

    I have returned from the wilderness

    Fake Larry Ellison - Mon, 2007-10-22 09:50

    Friends, I'm sorry about the lack of updates last week. I mean, I know this is a professional blog. And I also know a lot of stuff has gone down at Oracle over the last seven days. But let me tell you, last week was a totally crazy week for me personally. Man oh man. First there was the Oakland aerobatics show on Monday. Now, I'm not sure how many of you have flown a little prop plane 10 feet above the ground while stoned and drunk, but it really puts life and all of its challenges into perspective for you. There's nothing quite like playing chicken with telephone poles in a plane flying over four hundred miles per hour. And then on Tuesday, Steve came over to my house and we trimmed bonsai marijuana plants together and smoked a couple of joints and listened to Neil Young. Then I spent the rest of the week on my boat staring at the ocean and doing some soul searching. And you know what? I finally decided that I should have spent the week working at Oracle. Because honestly, I knew all along that it was just one of those weeks where I should have stayed home and taken care of little things, like firing John Wookey and closing the BEA Systems deal. Ah, well. As my hero Winston Churchill once said: never apologize, never explain.

    Dealing with Oracle PL/SQL Error "ORA-04068: existing state of packages has been discarded" Transparently in Java/JDBC

    Menon - Sun, 2007-10-21 17:22
    My next article is on how to deal with the infamous "ORA-04068" error that occurs when using PL/SQL packages in your application. I first wrote this article using the freely available AlleyCode software that allows you to edit HTML. I then posted it on this blog - even after many re-edits directly on the blog itself, the article as displayed on the blog does not look very elegant. This is especially true for the Java code which mysteriously keeps loosing the indentation at places. Overall, I still think it is just about readable and hope you would enjoy it:)

    Please give your comments on the blog.

    Thank you!
    Introduction
    One of the most critical arguments I have made in my book Expert Oracle JDBC Programming is that, we should strive to use stored procedures wherever we need to interact with databases - regardless of which database we use. This assumes that the database does provide the facility to write stored procedures which is true for most of the major databases such as Oracle, MySQL and SQL Server. This is true whether you use Java, .NET or any other language or framework of your choice. The arguments for and against this opinion are detailed in my book - I won't repeat them here though I may write a future article on this topic.

    In Oracle, of course, if you want to write stored procedures, you should use PL/SQL packages. In this article, I assume that you are familiar with PL/SQL in general and PL/SQL packages in particular. This article focuses on one particular "infamous" error that surprises and annoys many developers who use PL/SQL and invoke it from application layers using APIs such as JDBC. That error is "ORA-04068: existing state of packages has been discarded". This error is raised when Oracle thinks that your package state has become invalid for some reason. In this article we will discuss:

    1. what "ORA-04068" error is and why it occurs,
    2. what its impact could be, and
    3. the proposed solution (refined iteratively)
    Let us begin by defining what "ORA-04068" error is in the next section.

    Note: We use Oracle 9.2.0.3 in our examples though the same concepts should be applicable to Oracle 10g as well.

    What is "ORA-04068" Error and Why it Occurs?If we look at the definition of ORA-04068 using the oerr program provided by Oracle, we get the following information:
    $oerr ora 04068
    04068, 00000, "existing state of packages%s%s%s has been discarded"
    // *Cause: One of errors 4060 - 4067 when attempt to execute a stored procedure.
    // *Action: Try again after proper re-initialization of any
    // application's state.
    The error indicates that the existing state of the package that is being executed has been invalidated by an action taken by another session. The "state" refers to any global variables (including constants) that the package may have declared in the specification or body. The action that causes this error typically would be (but not restricted to) recompilation of the package after the connection for the session in which the error occurs was obtained. The action advised by Oracle is to re-try after re-initializing the application state appropriately to adjust for the new state of the package.

    All of this would hopefully become much clearer once we look at some examples.

    Consider the table t defined as follows:

    create table t (x number );

    Consider the specification of the package called pkg with a single procedure named p as follows:
    create or replace package pkg as
    procedure p;
    end pkg;
    /
    The package body for the package pkg shown below defines the procedure p which just inserts a constant value of 1 into the table t we defined earlier.
    create or replace package body pkg as
    procedure p
    is
    begin
    insert into t(x) values (1);
    end p;
    end pkg;
    /
    Note that there are no global variables or constants in either the package specification or body. In other words, the package is "stateless".

    We will first illustrate the concept by using two SQL*Plus sessions. In each "experiment", we would try to execute the procedure pkg.p after we have recompiled the package body in another session. Let us start with Experiment 1 where we would actually not get the ORA-04068 even though the package body has been recompiled in another session. This is because the package is "stateless" in that it does not have any global variables or constants defined in the specification or body.
    Experiment 1Assume that the table t and the specification and body of the package pkg have already been created in the package.
    In SQL*Plus session 1, we execute the package and get the following results (the package executes successfully.)

    Note: You may note that the SQL*Plus prompt in this article is sometimes different than the regular prompt ("SQL >") - for example, it is "session 1" in the code below. This can be achieved by using the command "set sqlprompt 'session 1'", for example.

    session 1> exec pkg.p
    PL/SQL procedure successfully completed.
    In SQL*Plus session 2, we recompile the package by recreating it as follows:
    session 2> create or replace package body pkg as
    2 procedure p
    3 is
    4 begin
    5 insert into t(x) values (1);
    6 end p;
    7 end pkg;
    8 /

    Package body created.

    session 2> show errors;
    No errors.
    Now if you go back to session 1 and re-execute the package procedure p, it works just fine.
    session 1> exec pkg.p

    PL/SQL procedure successfully completed.
    Let us recap what we did so far. We defined a simple package with just one procedure that inserts a constant into a table. We started a session and executed the package procedure. In another session we recompiled the package (by recreating it.) When we re-execute the package in the first session, it worked fine - in particular, the recompilation of the package in session 2 resulted in no error in the second execution of the procedure in session 1.

    Let us now repeat the whole experiment with just one change - we add a global constant to the package body (adding it to the specification would work just the same.) This means that we added "state" to the package. We illustrate the same experiment with this single change in our next section titled "Experiment 2".

    Experiment 2We log off from our earlier sessions. We start a new session and compile our new package body in session 1 as shown below - note that there is a constant declaration in the beginning of the package shown in bold. This is the state of the package. This constant is not being used anywhere but that is of no consequence to the results of this experiment.
    session1>@pkg_body
    session1>create or replace package body pkg as
    2 g_constant constant number := 1;
    3 procedure p
    4 is
    5 begin
    6 insert into t(x) values (1);
    7 end p;
    8 end pkg;
    9 /

    Package body created.

    session1>show errors;
    No errors.
    Now we execute the procedure p in session 1.
    session 1> exec pkg.p

    PL/SQL procedure successfully completed.
    We start a new session "session 2" and recompile the package by recreating it.
    session 2> @pkg_body
    session 2> create or replace package body pkg as
    2 g_constant constant number := 1;
    3 procedure p
    4 is
    5 begin
    6 insert into t(x) values (1);
    7 end p;
    8 end pkg;
    9 /

    Package body created.

    session 2> show errors;
    We execute the procedure p again in the session 1 and get the following results:
    session1>exec pkg.p
    BEGIN pkg.p; END;

    *
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04061: existing state of package body "ORA92.PKG" has been invalidated
    ORA-04065: not executed, altered or dropped package body "ORA92.PKG"
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at line 1
    What happened? When we recompiled the package body in session 2, we reset the state of the package. In other words, for any session that had connected before the package compilation, the existing state of the package (defined, in this case, by the value assigned to the constant in the package body) was wiped out from the memory. Note that we actually did not change the state (we retained the same value of the constant while recompiling) but Oracle does not track details at that level. As far as Oracle is concerned, in session 2, someone recompiled the package pkg - the package had a state which is now reset to a "new" state - so for any existing session that was already connected to Oracle before this recompilation happened, the package state becomes invalid. Hence on the next execution of any procedure or function of the package, we get ORA-04068 error the very first time.

    What happens if we re-execute the package in session 1 after we got the ORA-04068 in our first attempt? Let us see.

    session 1> exec pkg.p

    PL/SQL procedure successfully completed.
    As you can see the next execution assumes that the calling application (in this case SQL*Plus) has adjusted to the new state (since Oracle informed it once of the changed state) and is re-executing the package with the new state of the package. That is what Oracle's suggested action is (see the beginning of this section):
    Try again after proper re-initialization of any application's state.

    The next section highlights some of the impact of the ORA-04068 error.
    The Impact of "ORA-04068" ErrorTo gauge the impact of ORA-04068, all you have to do is google it. Two of the main impacts are as follows.
    1. Most enterprise applications use connection pool in which connections are cached. Now whenever a new package definition has to be deployed, it needs to be recompiled in production. The moment you do that, for all the connections in the connection pool, the state of this package would be invalidated since the package was re-compiled after the connection was obtained (as part of the connection pool initialization sometime earlier.) Note that this is true regardless of whether you changed the state or not, whether you even changed the code or not. The very first time a procedure or function in this package is invoked, it would fail with the "ORA-04058" error. So typically, you have to remember to "flush" the connection pool (meaning discard existing connections and obtain new connections to Oracle.) This typically leads to a downtime in the application deployment. For example, if you are using tomcat and a connection pool within tomcat, you may have to bring down tomcat and bring it back up - so that it re-initializes the connection pool. What if a long-running batch is using one of the connections to execute some logic completely independent of the package that needs to be recompiled? You would either have to wait till the batch completes or kill it during deployment so you can re-initialize the connection pool. As you can imagine, this can be a nightmare in terms of availability of the application.

    2. One of the even nastier impacts is that the developers are frustrated and confused by why a simple recompilation of the package (with a state) should result in Oracle failing with this error. This is especially true since other databases such as SQL Server and MySQL do not have the concept equivalent of packages and hence do not associate a state with stored procedures or functions. Hence, in these databases, you can redeploy stored procedures and expect the applications using them to work transparently. Whether this is the right choice or not on the part of other databases is debatable and is outside the scope of this article. Instead of understanding the underlying reason of ORA-04068 error and dealing with it, this single error can force the developers to abandon the use of stored procedures altogether (and thus abandon all the advantages that come with using stored procedures) and embed the SQL within their application code (such as code in Java.)
    So What is the Solution?In this section, we will discuss various solutions to deal with the "ORA-04068" error. Each solution would come with a set of constraints under which it is applicable. These solutions also show a progression of thoughts that would make the recommended solution and the trade-offs easier to understand.

    Let us begin with solution 1.
    Solution 1: Use Stateless PackagesOne of the simplest solutions is to use only stateless packages in your system. As we illustrated in our earlier section, ORA-04068 does not occur when you re-execute a stateless package even after it was recompiled in another session. This is because there is no state that can be invalidated by Oracle in the first place.

    This solution, though simple in theory, has the following significant drawbacks:
    1. It precludes you from defining any state which may result in poor code. In general there are two types of state:
      • A global variable: A global variable should be avoided in general anyway. I have yet to come across a justifiable need for declaring a global variable in a PL/SQL package or body.
      • A global constant: Almost all significant production systems need constants to be defined. If you decree that constants are not allowed in your system, then it would lead to poor practice of duplicate values defined multiple times which when required to be changed would affect more than one part of the system thus reducing maintainability.
    2. If you already have a system with packages that have state defined then this solution may result in a major rewrite. In such a case, you have to decide if the trade-off is worth it.
    Let us move on to our next solution.
    Solution 2: Move All Package State in a Different PackageThe idea behind this solution is that we move any package state in the package body or package specification to another package which would refer to as the "companion state package". This means that we reduce the number of times we have to deal with "ORA-06068" since the packages themselves don't store any state though they are dependent on the companion package for their state. In my experience, most of the time the changes occur in the package body implementation - which would not result in an ORA-04068 if we implement this solution. The ORA-04068 still occurs if we recompile the companion state package.

    Let us see this solution at work.

    We create a new package called const as follows into which we move the constant that we defined earlier in the package body of our package pkg.
    create or replace package const as
    g_constant constant number := 1;
    end const;
    /
    show errors;
    Our package specification for the package pkg does not change and is repeated below for your convenience:
    create or replace package pkg as
    procedure p;
    end pkg;
    /
    show errors;
    The package body changes so that there is no constant defined in it any more (it was moved to the package const) and also the insert statement now uses the constant defined in the package const for getting the value. Thus the package pkg depends on the package const for its state as defined by the constant g_constant:
    create or replace package body pkg as
    procedure p
    is
    begin
    insert into t(x) values (const.g_constant);
    end p;
    end pkg;
    /
    show errors;
    Assume that we have altered the package definitions for the package pkg and also installed the new package const in our system. Now we login to our session 1 and execute the procedure - it executes successfully as expected:
    session 1>exec pkg.p

    PL/SQL procedure successfully completed.
    We log in to session 2 and recompile the package specification and package body for the package pkg:
    session 2>@pkg_spec
    session 2>create or replace package pkg as
    2 procedure p;
    3 end pkg;
    4 /

    Package created.

    session 2>show errors;
    No errors.
    session 2>@pkg_body
    session 2>create or replace package body pkg as
    2 procedure p
    3 is
    4 begin
    5 insert into t(x) values (const.g_constant);
    6 end p;
    7 end pkg;
    8 /

    Package body created.

    session 2>show errors;
    No errors.
    In session 1, when we re-execute the procedure it works fine even though we had recompiled the package specification and package body. This is because the state of the package is in the package const (which has not been recompiled) and hence has not changed when we recompiled the package pkg itself.

    What happens when we recompile the package const though as follows in session 2?:
    session 2>@const
    session 2>create or replace package const as
    2 g_constant constant number := 1;
    3 end const;
    4 /

    Package created.

    session 2>show errors;
    No errors.

    If we re-execute our package pkg in session 1, we get the ORA-04068 error as expected. The error clearly states that the package state in the package const has changed and hence the dependent package pkg has been invalidated.
    session 1>exec pkg.p
    BEGIN pkg.p; END;

    *
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04061: existing state of package "ORA92.CONST" has been invalidated
    ORA-04065: not executed, altered or dropped package "ORA92.CONST"

    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at "ORA92.PKG", line 5
    ORA-06512: at line 1

    Of course, if we re-execute the package in session 1 right after, it seems to work fine as expected:

    session 1>exec pkg.p

    PL/SQL procedure successfully completed.
    Solution 2, though better than the solution 1, has the following drawbacks:
    1. It requires you to always move the state of the package outside the package itself thus making the package state globally visible to all other packages in the system. In other words, you can not create variables (or constants) private to a package (if you declare a variable or constant in the package body, it can not be accessed by any other package - in this sense it is private to the package in which it is defined - this leads to better encapsulated code.) This weakens encapsulation within the system thus reducing maintainability of the system. In fact, if we go this route, we should only have constants as part of any package state (which, is an acceptable and even welcome self-imposed restriction.)
    2. It requires you to move all the state of a package into a companion state package. This results in proliferation of companion packages in the system which makes this solution slightly inelegant. If you decide to have just one package with states of all other packages then you run into another problem wherein a single constant or variable change in the central package would invalidate all the other packages in the system - even those which are not dependent on the constant or variable. Only you can decide which of these two alternatives (central state package or companion state package per package) makes sense for you.
    3. If you already have a system with packages that have state defined then this solution may be hard to implement since it may result in a major rewrite. In this case you have to weigh the consequences of continuous ORA-04068 errors with each deployment or a one-time rewrite of the system.
    Our next set of solutions work towards an alternative that seem to be an improvement on the aforementiond two solutions but suffers from a drawback that makes the solution 1 or solution 2 as the final recommended solution for this article. However, I strongly suggest that you go through the next 2 solutions to understand the trade-offs and make your final judgement based on your knowledge of the system.
    Solution 3: Detect the ORA-0408 Error and Re-execute the Package ProcedureThis solution moves the burden of dealing with the error on the client. The idea is that Oracle, by generating the error ORA-04068, gives the client the information that the package state has been invalidated and it is up to the client to detect and react to this error. The client can choose to re-execute the procedure if that is what the client needs to do. We have already seen that this solution seems to work in SQL*Plus when the execution of the procedure right after the error works as expected. We will now see this in action in a Java program that uses JDBC and see whether this works or not.

    First let us revert back to our old code where we had state in the package pkg itself. So we re-introduce the state in our package body as before - the code is reproduced below for your convenience:

    create or replace package body pkg as
    g_constant constant number := 1;
    procedure p
    is
    begin
    insert into t(x) values (1);
    end p;
    end pkg;
    /
    show errors;

    Assume that we have recompiled the package body so that we have the new code in place. We would first simulate in a Java program using JDBC, a situation which leads to ORA-04068 error. For this we will:
    1. obtain a connection in the Java program using JDBC,
    2. execute the pkg.p procedure in the Java program using JDBC,
    3. sleep for some time (say 10 to 20 seconds) in the Java program,
    4. while our Java program is sleeping, we recompile the package body of the package pkg in a separate SQL*Plus session,
    5. re-execute the pkg.p procedure in the Java program using JDBC - this should result in the ORA-04068 error.
    The Java program called ExecutePackageProcedureTwice is shown below. It executes the pkg.p procedure, sleeps for 20 seconds to give us enough time to re-compile the package to simulate a deployement, and then re-executes the procedure:
    package dbj2ee.article2.design1;

    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import oracle.jdbc.OracleDriver;

    public class ExecutePackageProcedureTwice {
    public static void main(String[] args) throws Exception {
    Connection conn = null;
    CallableStatement cstmt = null;
    long sleepInSecs = 20;
    try {
    conn = getConnection();
    cstmt = conn.prepareCall("{call pkg.p()}");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
    } finally {
    try {
    if(cstmt != null)
    cstmt.close();
    if(conn != null)
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    private static Connection getConnection() throws Exception {
    DriverManager.registerDriver(new OracleDriver());
    return DriverManager.getConnection("jdbc:oracle:thin:@hercdev:1521:hercdev", "hercules", "hercules");
    }
    private static void executePkg(Connection conn, CallableStatement cstmt) throws Exception {
    System.out.println("Executing the package...");
    cstmt.executeUpdate();
    conn.commit();
    }
    }

    Let us now reproduce the ORA-04068 error.

    Executing the class with appropriate CLASSPATH set to point to the class root directory and to the classes12.jar (Jar containing Oracle JDBC implementation), we get the following:
    M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
    \build\classes" dbj2ee.article2.design1.ExecutePackageProcedureTwice
    Executing the package...
    Sleeping for 20 seconds...
    Once the Java program hits the point where it starts sleeping, we recompile the package in a separate SQL*Plus session:
    SQL> @pkg_body
    SQL> create or replace package body pkg as
    2 g_constant constant number := 1;
    3 procedure p
    4 is
    5 begin
    6 insert into t(x) values (1);
    7 end p;
    8 end pkg;
    9 /

    Package body created.

    SQL> show errors;
    No errors.
    Then after the Java program comes out of sleep, it fails with the ORA-04068 error as expected, when it tries to execute the package for the second time:
    M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
    \build\classes" dbj2ee.article2.design1.ExecutePackageProcedureTwice
    Executing the package...
    Sleeping for 20 seconds...
    Out of sleep...
    Executing the package...
    Exception in thread "main" java.sql.SQLException: ORA-04068: existing state of p
    ackages has been discarded
    ORA-04061: existing state of package body "ORA92.PKG" has been invalidated
    ORA-04065: not executed, altered or dropped package body "ORA92.PKG"
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at line 1
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
    at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:955)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.ja
    va:2053)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav
    a:1940)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
    nt.java:2709)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
    edStatement.java:589)
    at dbj2ee.article2.design1.ExecutePackageProcedureTwice.executePkg(Execu
    tePackageProcedureTwice.java:38)
    at dbj2ee.article2.design1.ExecutePackageProcedureTwice.main(ExecutePack
    ageProcedureTwice.java:20)

    Now, as we said, we know at the client level (in this case in the Java program) via the Exception raised wherein we can detect the error code and respond by re-executing the package. The simplest implementation of this is shown in the modified program dbj2ee.article2.design2.ExecutePackageProcedureTwice - the differences from the first version are highlighted for your convenience. As you can see we catch the SQLException and check to see if the error is ORA-04068 - if so, we re-execute the package, otherwise we re-throw the exception.
    package dbj2ee.article2.design2;

    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import oracle.jdbc.OracleDriver;

    public class ExecutePackageProcedureTwice {
    public static void main(String[] args) throws Exception {
    Connection conn = null;
    CallableStatement cstmt = null;
    long sleepInSecs = 20;
    try {
    conn = getConnection();
    cstmt = conn.prepareCall("{call pkg.p()}");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
    } catch (SQLException e) {
    if(reExecutionRequired(e)){
    System.out.println("ORA-04068 detected - re-executing the package...");
    executePkg(conn, cstmt);
    } else
    throw e;
    } finally {
    try {
    if(cstmt != null)
    cstmt.close();
    if(conn != null)
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    private static boolean reExecutionRequired(SQLException e) {
    return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }
    private static Connection getConnection() throws Exception {
    DriverManager.registerDriver(new OracleDriver());
    return DriverManager.getConnection(
    "jdbc:oracle:thin:@devhost:1521:ora92", "rmenon", "rmenon");
    }
    private static void executePkg(Connection conn, CallableStatement cstmt)
    throws Exception {
    System.out.println("Executing the package...");
    cstmt.executeUpdate();
    conn.commit();
    }
    }
    Let us see what happens when we execute this program and compile the package in a different session. As before, we start the execution of this program and get the following output:
    M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
    \build\classes" dbj2ee.article2.design2.ExecutePackageProcedureTwice
    Executing the package...
    Sleeping for 20 seconds...
    In another session, we recompile the package:
    SQL> @pkg_body
    SQL> create or replace package body pkg as
    2 g_constant constant number := 1;
    3 procedure p
    4 is
    5 begin
    6 insert into t(x) values (1);
    7 end p;
    8 end pkg;
    9 /

    Package body created.

    SQL> show errors;
    No errors.
    SQL>

    And when we come back to our Java program, it outputs the following as part of the re-execution of the package:
    M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
    \build\classes" dbj2ee.article2.design2.ExecutePackageProcedureTwice
    Executing the package...
    Sleeping for 20 seconds...
    Out of sleep...
    Executing the package...
    ORA-04068 detected - re-executing the package...
    Executing the package...

    As you can see we detected the error and re-executed the package successfully.

    Although this solution seems to work fine, the significant drawback to it should be obvious: To implement it, we will need to catch this Exception at every place where we invoke a stored procedure in our Java code. This change would be most likely prohibitive for most systems. There is another drawback to this solution that I would mention later which even makes this solution invalid for many systems.

    Our next potential solution refines the solution presented in this section to make the re-execution of the package transparent to an existing system thus making it really feasible to be implemented in .
    Solution 4: Detect the ORA-0408 Error Transparently and Re-execute the Package ProcedureThe idea behind this solution is as follows:
    1. We substitute our own wrapper class called MyConnectionWrapper instead of the Connection implementation of Oracle. The best place is to do this substitution is at the Driver level - by writing a wrapper Driver - although you can do this substitution at the connection pooling implementation level (e.g. in the data source.)
    2. Our Connection Wrapper would return a CallableStatement wrapper called MyCallableStatementWrapper instead of the CallableStatement implementation of Oracle whenever we invoke the method prepareCall() on it. In all other methods this wrapper class would delegate the action to the wrapped connection thus behaving in the same way as a normal Connection object.
    3. Our CallableStatement Wrapper would trap the Exception whenever an "execute" method is invoked on it - if it detects the ORA-04068 error, it would transparently re-execute the method again on the wrapped CallableStatement object. In all other methods, it would simply delegate to the wrapped CallableStatement object.
    First we will implement our own Driver that implements java.sql.Driver interface and wraps around the Oracle Driver class. The class MyDriverWrapper is shown below:
    package dbj2ee.article2.design3;

    import java.sql.Connection;
    import java.sql.Driver;
    import java.sql.DriverManager;
    import java.sql.DriverPropertyInfo;
    import java.sql.SQLException;
    import java.util.Properties;
    import oracle.jdbc.OracleDriver;
    public final class MyDriverWrapper implements Driver {
    private static final DriverPropertyInfo[] DRIVER_PROPERTY_INFO =
    new DriverPropertyInfo[0];

    public static final String ACCEPTABLE_URL_PREFIX = "jdbc:dbj2ee:orawrapper:";

    private static Driver driver = new OracleDriver();

    static {
    try {
    DriverManager.registerDriver(new MyDriverWrapper());
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    public Connection connect(String url, Properties info) throws SQLException {
    String myUrl = url.replaceFirst(ACCEPTABLE_URL_PREFIX, "jdbc:oracle:thin:");
    System.out.println("new url: " + myUrl);
    return new MyConnectionWrapper(driver.connect(myUrl, info));
    }
    public DriverPropertyInfo[] getPropertyInfo(String url, Properties info)
    throws SQLException {
    return DRIVER_PROPERTY_INFO;
    }
    public boolean jdbcCompliant() {
    return true;
    }
    public boolean acceptsURL(String url) throws SQLException {
    return url != null && url.startsWith(ACCEPTABLE_URL_PREFIX);
    }
    public int getMinorVersion() {
    return 0;
    }
    public int getMajorVersion() {
    return 1;
    }
    }
    Notice how the class defines its own proprietary prefix - it can be any value of your choice. It also stores an instance of the OracleDriver object which does the real work. In the connect method, the driver substitutes in the URL, its proprietary prefix with the Oracle thin driver prefix to seamlessly create a url suitable for the OracleDriver. It then gets the Oracle connection by delegating to the OracleDriver instance. It then wraps this connection with the class MyConnectionWrapper (which we would look at in a moment) and returns the MyConnectionWrapper object. This is how we substitute our own Connection object transparently. Note that you can do it in many ways - for example, you can substitute the connection at the data source level instead of doing it at the connection level.

    The class MyConnectionWrapper is shown below. Observe the following facts about the implementation of this class:

    1. It takes a connection object as an object in the constructor and stores it in a private instance variable.
    2. It wraps all CallableStatement objects with the MyCallableStatement class in all versions of the prepareCall() method implementation.
    3. The implementation of other methods simply delegate their action to the corresponding method in the wrapped connection.
    package dbj2ee.article2.design3;

    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.sql.SQLWarning;
    import java.sql.Savepoint;
    import java.sql.Statement;
    import java.util.Map;
    public class MyConnectionWrapper implements Connection {

    private Connection connection;

    public MyConnectionWrapper(Connection connection) {
    this.connection = connection;
    }
    public CallableStatement prepareCall(String sql) throws SQLException {
    return new MyCallableStatementWrapper(connection.prepareCall(sql));
    }
    public CallableStatement prepareCall(String sql, int resultSetType,
    int resultSetConcurrency) throws SQLException {
    return connection.prepareCall(sql, resultSetType, resultSetConcurrency);
    }

    public CallableStatement prepareCall(String sql, int resultSetType,
    int resultSetConcurrency, int resultSetHoldability) throws SQLException {
    return connection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }
    public void clearWarnings() throws SQLException {
    connection.clearWarnings();
    }
    // ....... all other methods are simple delegation to the connection
    // instance variable and are not being shown to conserve space.

    }
    The class implementation of CallableStatement wrapper necessitates the implementation of its parent interfaces PreparedStatement and Statement. Thus we create three wrapper objects - MyStatementWrapper wraps around Statement object; MyPreparedStatementWrapper wraps around PreparedStatement object and MyCallableStatementWrapper wraps around CallableStatement object.

    The class MyStatementWrapper is a simple wrapper around the Statement object and is shown partly below - the code is self-explanatory:
    package dbj2ee.article2.design3;

    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.SQLWarning;
    import java.sql.SQLException;
    import java.sql.ResultSet;

    public class MyStatementWrapper implements Statement {
    Statement statement;

    public MyStatementWrapper(Statement statement) {
    this.statement = statement;
    }

    public void addBatch(String sql) throws SQLException {
    statement.addBatch(sql);
    }

    // ....... all other methods are simple delegation to the connection
    // instance variable and are not being shown to conserve space.

    }
    The class MyPreparedStatementWrapper is a simple wrapper around the PreparedStatement object and is shown partly below - the code is self-explanatory:
    package dbj2ee.article2.design3;

    import java.net.URL;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.sql.ResultSet;
    import java.sql.Blob;
    import java.sql.Clob;
    import java.sql.ResultSetMetaData;
    import java.sql.Array;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.io.Reader;
    import java.sql.Date;
    import java.sql.ParameterMetaData;
    import java.util.Calendar;
    import java.sql.Ref;
    import java.sql.Time;
    import java.sql.Timestamp;

    public class MyPreparedStatementWrapper extends MyStatementWrapper
    implements PreparedStatement {

    private PreparedStatement preparedStatement;

    public MyPreparedStatementWrapper(PreparedStatement preparedStatement) {
    super(preparedStatement);
    this.preparedStatement = preparedStatement;
    }

    public ParameterMetaData getParameterMetaData() throws SQLException {
    return preparedStatement.getParameterMetaData();
    }

    // ....... all other methods are simple delegation to the connection
    //instance variable and are not being shown to conserve space.
    }
    The class MyCallableStatementWrapper is shown below. Observe the following about the implementation of this class:
    1. It extends the MyPreparedStatementWrapper class.
    2. Like other wrappers it stores a CallableStatement object as part of its instance variable.
    3. For all methods that execute a stored procedure, it overrides the implementation, re-invoking the method if it detects the ORA-04068 error transparently. Note that in reality you may have to override some other execute methods inherited from PreparedStatement also in a similar fashion.
    4. The implementation of other methods simply delegate their action to the corresponding method in the wrapped CallableStatement object.
    package dbj2ee.article2.design3;

    import java.io.InputStream;
    import java.io.Reader;
    import java.util.Map;
    import java.sql.CallableStatement;
    import java.sql.SQLException;
    import java.sql.Blob;
    import java.sql.Clob;
    import java.sql.Array;
    import java.math.BigDecimal;
    import java.net.URL;
    import java.sql.Date;
    import java.util.Calendar;
    import java.sql.Ref;
    import java.sql.Time;
    import java.sql.Timestamp;

    public class MyCallableStatementWrapper extends MyPreparedStatementWrapper
    implements CallableStatement {

    private CallableStatement callableStatement;
    public MyCallableStatementWrapper(CallableStatement statement) {
    super(statement);
    this.callableStatement = (CallableStatement)statement;
    }

    public boolean execute() throws SQLException {
    boolean result = true;
    try {
    result = callableStatement.execute();
    } catch (SQLException e) {
    System.out.println("code:" + e.getErrorCode() + ", sql state: "
    + e.getSQLState());
    if(reExecutionRequired(e)){
    System.out.println("re-executing package ");
    result = callableStatement.execute();
    } else
    throw e;
    }
    return result;
    }

    public int executeUpdate() throws SQLException {
    int result = 0;
    try {
    result = callableStatement.executeUpdate();
    } catch (SQLException e) {
    System.out.println("code:" + e.getErrorCode() + ", sql state: " +
    e.getSQLState());
    if(reExecutionRequired(e)){
    System.out.println("re-executing package ");
    result = callableStatement.executeUpdate();
    } else
    throw e;
    }
    return result;
    }

    private boolean reExecutionRequired(SQLException e) {
    return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }

    public URL getURL(int parameterIndex) throws SQLException {
    return callableStatement.getURL(parameterIndex);
    }

    // ....... all other methods are simple delegation to the connection
    // instance variable and are not being shown to conserve space.
    }
    Finally, we can take a look at our class ExecutePackageProcedureTwice that utilizes this solution. It is shown below. It is very similar to the ExecutePackageProcedureTwice in the beginning of this section - except for the following differences (shown in bold in the class listing):
    1. It prints the connection and callable statement classes to show that the classes are indeed our wrapper classes.
    2. The code for getting the connection first ensures that our driver class is loaded by using the Class.forName() trick. It then uses our proprietary prefix instead of the "oracle:jdbc:thin:" prefix so that our driver is selected by the DriverManager when obtaining the connection thus making it possible to substitute all the relevant JDBC classes with our wrapper classes.
    package dbj2ee.article2.design3;

    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;

    public class ExecutePackageProcedureTwice {
    public static void main(String[] args) throws Exception {
    Connection conn = null;
    CallableStatement cstmt = null;
    long sleepInSecs = 20;
    try {
    conn = getConnection();
    System.out.println("connection class: " + conn.getClass());
    cstmt = conn.prepareCall("{call pkg.p()}");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
    } finally {
    try {
    if(cstmt != null)
    cstmt.close();
    if(conn != null)
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    private static Connection getConnection() throws Exception {
    Class.forName("dbj2ee.article2.design3.MyDriverWrapper");
    return DriverManager.getConnection(MyDriverWrapper.ACCEPTABLE_URL_PREFIX +
    "rmenon/rmenon@devhost:1521:ora92");
    }
    private static void executePkg(Connection conn, CallableStatement cstmt)
    throws Exception {
    System.out.println("Executing the package...");
    cstmt.executeUpdate();
    conn.commit();
    }
    }
    When we execute this class, we get the following results (notice the connection class and the callable statement classes point to our wrapper classes):
    M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
    \build\classes" dbj2ee.article2.design3.ExecutePackageProcedureTwice
    new url: jdbc:oracle:thin:rmenon/rmenon@devhost:1521:ora92
    connection class: class dbj2ee.article2.design3.MyConnectionWrapper
    callable statement class: class dbj2ee.article2.design3.MyCallableStatementWrapp
    er
    Executing the package...
    Sleeping for 20 seconds...
    We then recompile the package in another session as before:
    SQL> @pkg_body
    SQL> create or replace package body pkg as
    2 g_constant constant number := 1;
    3 procedure p
    4 is
    5 begin
    6 insert into t(x) values (1);
    7 end p;
    8 end pkg;
    9 /

    Package body created.

    SQL> show errors;
    No errors.
    And when we observe back in our Java execution, we see the following after the Program successfully re-executes the package:
    M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
    \build\classes" dbj2ee.article2.design3.ExecutePackageProcedureTwice
    new url: jdbc:oracle:thin:rmenon/rmenon@devhost:1521:ora92
    connection class: class dbj2ee.article2.design3.MyConnectionWrapper
    callable statement class: class dbj2ee.article2.design3.MyCallableStatementWrapper
    Executing the package...
    Sleeping for 20 seconds...
    Out of sleep...
    Executing the package...
    code:4068, sql state: 72000
    re-executing package

    Note that if you use a connection pool then you can specify the correct driver in the connection pool to use the same technique.

    Thus we are able to device a solution that seems to work transparently for almost all cases.
    This is what I thought would be the perfect solution before I had some discussions with Tom Kyte on his web site http://asktom.oracle.com. He pointed out a caveat in this seemingly perfect solution which I now describe.

    Consider the following scenario:
    1. You have a package pkg which depends on a constant const1 in the package const. The package pkg has two methods method1 and method2 that both rely on the constant const1 - the value is set to 1.
    2. You grab a connection from the connection pool.
    3. In your Java code, you execute the method pkg.method1 - it makes some decision on the constant value which is right now 1.
    4. Now, as part of the deployment, someone compiles the package const - the constant has been changed to have a value of 2.
    5. Your transaction executes next step which is invoking the method pkg.method2.
    6. Since you have implemented the "silent re-execution technique" mentioned in this section, the method2 silently ignores the ORA-04068 and grabs the new value of the constant which is 2.
    7. The problem is that this may result in inconsistent results in the transaction. This is because you voilated the assumption that a constant declared in a package (or package state, in general) should give the same value throughout a given session - otherwise there is no guarantee that you would get a consistent result depending on the transaction semantics.
    Thus this solution is not applicable in all cases where the re-execution of the package procedure does not give the correct results. This can happen, if for example, your package procedure's current execution depends on the previous package state. This was the scenario that Tom elaborated on and pointed out that it may be more common than I had thought earlier.
    Conclusion and Recommended Solution(s)
    We looked at multuple solutions in this article for the ORA-04068 error and also gave various trade-offs for each solution. Following is what I recommend depending on individual scenario:
    1. In all cases I recommend not using any global variables in the package specification or body as far as possible.
    2. The easiest solution is to use stateless packages (our solution 1) and if you can go with this then that is what I recommend. You should strive to make your packages stateless.
    3. The second best solution (which would be applicable in majority of cases), is to add companion packages for each package where the package state is isolated. This ensures that you encounter ORA-04068 only when you actually change the companion state packages - which should be relatively rare - especially if the state consists of only constants. If you don't like to have companion packages, then you can have a central package for all constants in the system - this would result in more ORA-04048 than usual - but note that you typically need to flush your connection pool even if you change the state of one package so this is not as bad as it sounds.
    4. I do not recommend solution 4 (or solution 3) since I find it hard to guarantee that they would work in any complicated system. However rare the number of times they would result in a failure, this solution would act like a ticking bomb ready to explode in those rare situations.
    AcknowledgementsI would like to acknowledge my colleagues Ivan Zlatev and Andrey Radchenko, discussions with whom led to the solution 4 outlined in this article. I would also acknowledge Tom Kyte, discussions with whom led to my not recommending solution 4 after all.

    Global Temporary Tables

    Robert Vollman - Sun, 2007-10-21 12:56
    I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom."It's time to add Global Temporary Tables to your toolbelt.""What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned Robert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com15

    Disclosure & Bloggers

    Peter Khos - Sat, 2007-10-20 13:37
    With the recent "can of worms" that Jake blogged about on his Blog, I thought that Mary Ann Davidson kind of hit it on the nail when she blogged about "Disclosure".I've blogged about this previously but thought that it is worth mentioning again.Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

    ORA-01461: can bind a LONG value only for insert into a LONG column

    Vlad Sadilovskiy - Fri, 2007-10-19 15:47

    Just one another issue with JDBC I think is worth mentioning. You perhaps wonder why so obvious error message could be troublesome to comprehend. Unfortunately, it speaks of things that a developer never meant to happen. It doesn’t have to be a LONG value. This error could appear when ASCII strings are bound for VARCHAR2 columns. In this case it happens when JDBC v. 10.1/2 is used with a Oracle Server 9i database configured for multi-byte character set. Before going any further, please note that according to support matrix Oracle JDBC 10g and 9i are cross supported with Oracle Server 9i and 10g.

    For the testing I used two databases Oracle 10g and Oracle 9i with database character set UTF8 and JDBC 10.2.0.3.

    Some of the Asian language characters can consume up to 3 bytes in UTF8 encoding. But can you expect that a string composed of 1334 ASCII characters would not bind for VARCHAR2(4000)? However, it is possible. Let’s dig up little more details.

    Here is how the string of 4000 “a” characters would appear to the Oracle 9iwhen bound from JDBC 10g with default properties. This bind is not spooled into the 10046 trace file. Instead it can be captured by enabling stacktrace dump on 1461 event.


     bind 19: dty=1 mxl=4001(12000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=4000 offset=0
       bfp=ffffffff7cd7f060 bln=4000 avl=00flg=05

    Oracle Server 10g. Note, that when you test this scenario, there are no errors – Oracle Server 10g  is perfectly fine with the 12000 bytes in the private buffer.


     Bind#19
      oacdty=01 mxl=4001(12000) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000010 frm=01 csi=871 siz=4000 off=0
      kxsbbbfp=ffffffff7b55a0c0  bln=4000  avl=4000  flg=05
      value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa....

    According to Metalink Note: 3756847.8 this problem was introduced in some 10.1 versions of JDBC, but was fixed in 10.2. However, it is not enabled by default. Following JDBC connection property enables the fix.


    java.util.Properties props = new java.util.Properties();
    props.put("oracle.jdbc.RetainV9LongBindBehavior","true");
    ods.setConnectionProperties(props);
    ods.setURL(url);

    And here is how the bind looks like after the fix is enabled.
     

    Oracle Server 9i:


    bind 19: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=4000 offset=0
       bfp=ffffffff7cd56088 bln=4000 avl=4000 flg=05
       value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

    Oracle Server 10g:


     Bind#19
      oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
      oacflg=03 fl2=1000010 frm=01 csi=871 siz=4000 off=0
      kxsbbbfp=ffffffff7b56f060  bln=4000 avl=4000flg=05
      value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

    There is no need in enabling this fix for 10g/10g setup. 10g version of the trace is shown just for comparison purposes.

    In conclusion I want to clarify that this article describes the unexpected behavior with legitimate length values. However, there are other cases when apparently correct values fail to insert with same message. I.e. already mentioned strings that, when using UTF8 for database character set, can expand from 4000 Asian language characters to up to 12000 byte values. In these cases, Notes: 241358.1, 370438.1 recommend different workarounds. Note: 445072.1  has decent example of getting the length of encoded string and suggests converting VARCHAR2 columns into CLOB if encoded string length exceeds maximum length of VARCHAR2 type.


    Run Flow Manufacturing without other Oracle Applications

    Chris Grillone - Fri, 2007-10-19 11:49
    Running Oracle Flow Mfg standalone has been done in the past. The company used the applications from a corporate office to design balanced flow lines and inform each plant how to set up their lines. This was a constant changing process asthey had lots of new product and volume changes.

    To do this, they implemented an instance of Oracle Manufacturing and Flow. They interfaced their Inventory Master and BOM file from Baan to Oracle. They imported Forecasts directly to be used for line balancing andkanban sizing. If they need to execute within Oracle they would then need to export the inventory transactions back to the legacy system.

    Additional data files can be interfaced as needed.

    Disclosure

    Mary Ann Davidson - Wed, 2007-10-17 12:01

    Many corporations have corporate ethics policies. I take a refresher ethics class online once a year at Oracle and despite the fact I think I am pretty ethical, I always get at least one question wrong.  (I think that means I am learning at least one new thing when I take the class.)

     

    One of the areas most corporate policies cover is the area of conflicts of interest. For example, at Oracle, if you are asked to serve on an advisory board or board of directors of a company, you need to get multiple approvals. Approval is generally only given under certain circumstances that include consideration of whether there is a potential conflict of interest. For example, serving on an advisory board of a company in direct competition with Oracle would most likely not be approved.

     

    Even in the ordinary course of business, potential conflicts of interest can arise and you are expected to disclose these. I don't know if it's my Midwestern upbringing or going to a university with a very strong honor code, but I am really big on disclosure. Probably ad nauseum disclosure: if I think something is even approaching a gray area of ethics, I email our corporate compliance officer to ask if there is an issue. And the reason is that at some point it is not merely the company's ethics policy that governs my disclosure, it's my personal integrity. I would hate to have someone think I said or did something where I appeared to be "independent" but in reality had an "angle" that was tainted by my being a stakeholder in some way.

     

    Disclosure forces you to be honest with yourself as well as other people. If you have an axe to grind about something, you need to disclose who sharpens your axe if it is material to the discussion. And it often is.

     

    There are many written or unwritten ethics codes that cover issues of disclosure in the business world. People who write about securities or recommend them are generally either prohibited from owning stock in companies they write about, or they have to disclose it. Imagine reading "Investment Kahuna-ette's" column in (insert name of well-respected business publication here), going out to buy the stock Ms. Kahuna-ette touted, and come to find out, she went long on the stock before the article came out (meaning, she bought the stock and hoped the price would rise). You'd feel as if Ms. Investment Kahuna-ette pumped the stock just so she'd make money, right? And if Ms. Investment Kahuna-ette did not disclose that in her column (not in subparagraph III, second sentence on some fine print document nobody could possibly be expected to find let alone read), you'd feel as if she cheated. Because she did cheat. People get fired for that.

     

    One of the real downsides to the democratization of opinions that Web 2.0 represents is that where bloggers are competing with or crowding out "professionals," they are not necessarily adopting the code of ethics that some of the professionals have or at least pretend to have. This includes issues around disclosure.

     

    I read an article over the weekend about how influential bloggers are to the restaurant business. On the face of it, there is nothing wrong with word of mouth spreading a restaurant's reputation; how many of us have had friends or relatives in town and asked our "foodie" friends for a restaurant recommendation? However, some of these bloggers are so successful that they quit their day jobs and blog for a living: their revenue is through advertising. So now, they are "professionals" and ought to be governed by a code of ethics. But few are.

     

    Here's what I mean: professional restaurant reviewers as a matter of course (and ethics) pay for their own meals at the restaurants so they can't be accused of being "on the take." However, the "new breed" of online restaurant reviewers are apparently, as a matter of course, wooed by restaurants through "receptions" (read, "free food and drink"), after which their reviews becomes positive, what a surprise. Or, a blogger's parents who had a horrible restaurant experience were sent meal coupons (after their online blogger child ripped the restaurant). The blogger subsequently gave a rave review to the restaurant that supplied the meal coupons to his parents.

     

    Now, maybe the food was really fabulous (I can't imagine any self-respecting "foodie" calling artfully presented dog food anything but "dog food," even if it was free, high end and organic dog food). However, in none of the above cases did the Influential Blogger disclose that he or she had gotten something of value for free from the restaurant. It doesn't mean they weren't entitled to an opinion, it means that they should have disclosed the freebie(s) because it likely "taints" their opinion. Or at least gives the appearance of tainting it, which is just as bad.

    Other disclosure issues arise from people's business models and 
    business relationships. For example, a lot of firms that are industry
    analysts, since they analyze and recommend products, also work with
    product vendors to guide their product directions. Many of them are
    very good in their sectors and can add value to vendors trying to
    ensure they solve the right customer problems. It becomes a problem
    if there is de facto or implicit "tit for tat" (meaning, if the vendor does
    not purchase consulting "advice," the analyst firm's "product reviews"
    of the vendor suffer). It's also a problem, in my opinion, if the analyst
    firm does not disclose (as they issue reports) which firms they "consult"
    for and which not.* One does have to ask the question, how objective
    can you be if the people whose products you review are also paying
    you to give them advice? At least disclose that there is a relationship
    and let the reader decide how important that relationship is.
    Appearances matter.

    So, what does disclosure have to do with security? A lot, as it happens, and not just the age-old "full vs. responsible disclosure" issue. Many security professionals, me included, have opinions and beliefs and we blog about them. For a lot of us, our associations don't need an explicit disclosure because they are already obvious. I don't add a disclaimer when I say something positive about Oracle in my blog because hey, I work for Oracle, my blog is hosted by Oracle, my email address has "oracle.com" in it; nobody could reasonably expect that I have a hidden agenda if I say something positive about Oracle. 

    It would be different, however, if I blogged on another web site where I had a totally different email id (let's say, a hotmail account), and I claimed to be the world's biggest Oracle security fan and did not disclose that I was a security executive with the company. Any sane person's response if I did that and it came out that LuvOracleSecurity@hotmail.com (which is a made up email address as far as I know) is lil' ol' me, would be, "Hey, who are you kidding here?" And they'd be right. It's not ethical. Not even close to being ethical. ("Slimy" is the word that comes to mind.)

     

    As with other sectors of life, in the security community, many people have relationships that they do not disclose that either explicitly or implicitly influence their opinions, business judgment and/or public statements. They ought to - but often do not  - disclose them.

     

    For example, many security researchers also work for vendors from time to time to help them find vulnerabilities in the software. If you are a vendor, you figure if someone is a good researcher (has found a number of product vulnerabilities and worked with you well as "an independent") and you feel you can trust him/her, it can be helpful to have the researcher in to help you improve your product. We actually hired one of these individuals to run our ethical hacking team - a smart guy, good at finding vulnerabilities, and an ethical person.

     

    Many vendors hire third parties to help them improve their products (disclosure: we have hired and do hire third parties to perform product assessments in addition to using our own internal ethical hacking team). Also, typically, you have some contractual restrictions on what the researchers can do with the information they find under contract. Most of these items are covered by a confidential disclosure agreement (sometimes called a non-disclosure agreement) and the thinking behind it is, "Hey, I am paying you to tell me about what you find so I can fix it, and I want time to fix it. So, Mr. Researcher, I don't want you doing a paper about this until some period after you report the bug and I fix it, to make sure customers are protected, and I don't want you ever releasing exploit code because I think it puts people at risk." Fair enough.


     

    So, where does disclosure come into it? Just this: since many researchers who do "work for hire" for vendors are prevented from talking about what they are working on for Vendor X, they can - and often do - start talking about Vendor Y. Researchers do not generally have big PR agencies working for them and creating a media splash is "free marketing" that works pretty well. And because controversy sells, they may not be saying nice things about Vendor Y. None of this is necessarily a problem if Vendor Y is actually in the wrong. If you are guilty of tormenting small mammals, and a third party says so publicly, you have no cause to complain that you were wronged. Be nice to the critters and your PR problem goes away. But to the extent that the researcher is prohibited from talking at all about Vendor X, or does not disclose that he does work for hire for them, his opinion is potentially tainted to the extent he speaks about Vendor X or others in their market sectors. Just like the restaurant reviewers, if Vendor X paid me or gave me something for free, and I now say glowing, wonderful things about their product, I ought to disclose that I am or have been on their payroll or that I am getting freebies.

     

    Even if we debate responsible disclosure (about the vulnerabilities themselves, which is another charged area) there should be no debate about the ethics of disclosing business relationships if you are going to set yourself up as "an independent expert." If you are on someone's payroll, you are not independent anymore, though you may still be an expert.

     

    Quite honestly, even if you cannot speak or are restricted in how you speak about Vendor X, you need to disclose that or you have no moral leg to stand on in discussing disclosure - of any kind - with anybody. You might still be right in what you say, but at least the reader can correctly surmise that you might not be telling all you know about Vendor X - because you can't.  If I am evaluating an expert's opinion, knowing what he cannot say or is not saying is at least as important as knowing what he can and does say.

     

    I have a final thought on what is at the core of the disclosure issue for me, and that is the age-old but never surpassed virtues of honor and integrity. I mentioned earlier that I had gone to a university with a strong honor code: the University of Virginia. The single biggest reason I went there wasn't the beautiful architecture, though it is stupendous. Many buildings were designed by Thomas Jefferson: in 1976, Jefferson's Lawn and Rotunda were named the most outstanding architectural achievement in 200 years of American history by the American Institute of Architects (AIA). It wasn't the beauty of The University though there is that: the only time I have experienced love at first sight was seeing UVA in fall when I went there as a high school senior to check it out. I applied to UVA, and only UVA, and got in. It wasn't the fact that the engineering program was designed to turn out well-rounded graduates (I had to read More's Utopia and Plato's Republic as an engineering school requirement!), though that appeals to my literary side.

     

    Nope, I went to UVA because they had an honor code that means something. It's one of the oldest honor codes in the country, and there is still a single sanction for honor violations: dismissal. Because there are no degrees of honor. If you think that there are degrees of honor, and cheating, lying and stealing are all excusable depending on day of the week, your mood, or your "value system," then you are welcome to attend another university: UVA does not want you there, and they make that clear in their recruiting materials. And as a graduate, I don't want people attending there who do not believe in and subscribe to the honor code. There is a beautiful gateway at UVA at one of the entrances, on which is incised: "Enter by this gateway and seek the way of honor, the light of truth and the will to work for men." Says it all.

     

    The University recently sent a number of alumni/ae a link to some new ads they are going to run during televised football games. The emphasis in these ads was "diversity." And I was upset, but not because I have anything against diversity, if by that one means "commitment to the highest standards of academic excellence by all members of the university community, regardless of background." But what the school stands for, really and truly stands for, that makes it different from all others is the honor code, and that is what the ads should have stressed. Furthermore, in matters of honor, there should be no diversity. Whoever you are, wherever you come from, you live by the University of Virginia honor code, with its single sanction, or you go someplace else. A single code for all, and a single sanction for violations: dismissal.

     

    There are precious few bastions in this country that have not fallen by the wayside to "everyone does it," "lying, cheating and stealing are just 'different values' that need to be tolerated," and "you can't expect people to live up to some arcane old ideal." (Except that I can and I do expect it.) One of these bastions is the University of Virginia. The other bastions include the service academies: the Naval Academy, the Military Academy, the Air Force Academy, the Coast Guard Academy. And for many of these schools, part of the honor code includes creating a community of honor: "A cadet will not lie, cheat or steal, nor tolerate those who do."

     

    West Point has a single, straightforward motto that every cadet remembers because it is engraved on the West Point coat of arms. It is "Duty, Honor, Country." It was also among the last phrases to be quoted by GEN Douglas MacArthur at his stirring farewell address: "In my dreams I hear again the crash of guns, the rattle of musketry, the strange, mournful mutter of the battlefield. But in the evening of my memory I come back to West Point. Always there echoes and re-echoes: Duty, Honor, Country."

     

    Need I add that "Duty, Honor, Country" is a lot worthier ideal than "Me, myself, and I," which seems to be the ruling ethos of so many?

     

    For me, the issues around disclosure are not really as complicated as people seem to think they are. It goes back to honor. Honorable men and women disclose the nature of relationships when the existence of that relationship gives the appearance of - or substance to - a tainting of their opinions or a conflict of interest. If there is, as yet, no professional code of ethics in the security community, it is time we had one, and we can start with acting honorably as individuals: if your opinion looks to be or is influenced by a business relationship, disclose the relationship. You may still be right in what you say when you have an axe to grind, but the reader will know who sharpens your axe.

     

    * Note: even if the vendor does not want the relationship disclosed for a variety of reasons, an analyst firm typically can say that they consult to players in the same space.  It has the same disclosure effect for their allegedly impartial reviews, but saves the initial vendor's confidentiality requirement.  This type of arrangement is common in the securities industry.

     

    For more information:

     

    Book of the week: Mr. Pip by Lloyd Jones. I do not generally like much modern fiction, especially as so much is of the post-modernist drivel variety.  However, a single great book can change your life, which happens to be the conceit of this story. After a revolution breaks out on Bougainville, the last white man on the island becomes a teacher, and he teaches the children by reading them Dicken's Great Expectations. A magical, special book that enriches your soul.

     

    About the University of Virginia Honor Code:

     

    http://www.virginia.edu/uvatours/shorthistory/code.html

     

    A virtual tour of Jefferson's Academical Village at:

     

    http://www.virginia.edu/academicalvillage/#

     

    Pictures of UVA:

     

    http://www.pbase.com/steveyaphotos/the_university_of_virginia

     

    A link on honor codes:

     

    http://en.wikipedia.org/wiki/Honor_code

     

    General Douglas MacArthur's Farewell Speech at West Point:

     

    http://www.nationalcenter.org/MacArthurFarewell.html

     

    The Coat of Arms of West Point:

     

    http://www.usma.edu/PublicAffairs/Press_Kit_files/CoatArms.htm

     

    A great biography of Douglas MacArthur is still American Caesar by William Manchester, which you can find at:

     

    http://www.amazon.com/American-Caesar-Douglas-MacArthur-1880-1964/dp/0440304245

     

    (I found out a few years ago that my dad had actually met Douglas MacArthur a couple of times while serving in Japan after WWII. How cool is that?)

     

    Identifying mutex holder

    Fairlie Rego - Tue, 2007-10-16 20:55
    In my previous post on flushing a cursor we see that a process is waiting on a mutex. In this post we will try to identify the holder from a systemstate dump

    • Find the process executing the purge API. In this case it was process 22
    • Open the systemstate dump and go to PROCESS 22
    • Under this (if the systemstate is at level 266) you will see the short stack of the process

    ksdxfstk()+36<-ksdxcb()+2452<-sspuser()+176<-sigacthandler()+44<-__systemcall()+52<-semtimedop()+28<-sskgpwwait()+224<-ksliwat
    ()+988<-kslwaitns_timed()+48<-kskthbwt()+232<-kslwait()+116<-kksLockWait()+396<-kgxWait()+444<-kgxExclusive()+132<-kxsPurgeCur
    sor()+476<-psdpur()+1444<-pevm_icd_call_common()+728<-pfrinstr_ICAL()+132<-pfrrun_no_tool()+72<-pfrrun()+832<-plsql_run()+696<
    -peicnt()+260<-kkxexe()+824<-opiexe()+12736<-kpoal8()+1912<-opiodr()+1548<-ttcpip()+1284<-opitsk()+1432<-opiino()+1128<-opiodr
    ()+1548<-opidrv()+896<-sou2o()+80<-opimai_real()+124<-main()+152<-_start()+380


    As can be seen from the above stack it includes the function kxsPurgeCursor() which contains this piece of functionality (to flush the cursor)

    • Under the session state object of this process you will see that the session is waiting on

    waiting for 'cursor: pin X' blocking sess=0x0 seq=9208 wait_time=0 seconds since wait started=0 idn=dbabc3c, value=1, where|sleeps=e00000629

    wait_time=0 indicates the session is waiting at the time this process was dumped.

    • If you scroll down further in the same process state object and search for idn=dbabc3c

    KGX Atomic Operation Log 3d6e12a08
    Mutex 3da75c7d0(0, 1) idn dbabc3c oper GET_EXCL


    Hence this process is waiting to acquire the mutex in exclusive mode.

    • To find the process holding the mutex search on the string "dbabc3c oper"

    You will find something like the below

    KGX Atomic Operation Log 3d6e671e0
    Mutex 3da75c7d0(0, 1) idn dbabc3c oper SHRD


    Search the process holding this.
    In vi editor you can use ?PROCESS which will lead you to

    PROCESS 15:
    -------------------------------------------


    Hence Process 15 is holding the mutex and this is the process doing the merge join Cartesian in Session 1.

    We're a little disappointed about Doris Lessing

    Fake Larry Ellison - Tue, 2007-10-16 01:53

    My wife Melanie really thought she was going to win the Nobel Prize in Literature this year. Don't ask me why. I mean, all of her books have been out of print for years now. And even when they were still in print, the only people who read them were desperate stay-at-home-moms and gay men and horny teenage girls who kept vibrators under their mattresses. But she still thought she was going to win. I warned her. I told her she was cruising for a bruising. Before we got married I said, Melanie, only 11 women have won the Nobel Prize in Literature in 106 years. And she said that she knew that and she wasn't going to get her hopes up or anything. But did she listen to me? No, she did not. She started crying her eyes out last Friday when I was supposed to be closing the deal with BEA Systems. It sort of frigged everything up. Anyway, that's what I was this weekend -- a shoulder to cry on. You're jealous, I know.

    Blog Action Day

    Peter Khos - Mon, 2007-10-15 22:18
    Today is Blog Action Day where each blogger is to blog about the environment related to their topic/theme.I am in IT so what can I blog about which would be relevant. Well, I recently attended Gartner Symposium ITxpo where "Green IT" is a key theme for the Symposium and Gartner expects this to be an ongoing theme for the IT industry for the next 3 to 5 years.Michael Dell as one of the keynote Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

    Multi-Foundation on UNIX

    Mark Vakoc - Mon, 2007-10-15 19:43
    Today I wanted to setup multi-foundation on the same enterprise server on my Linux based server. I had an existing server that I wanted to "clone" to create two additional installs for testing various tools releases. The primary was installed using platform pack and was already upgraded to 8.97 using SM.

    I wanted to take advantage of the multiple instance support in EnterpriseOne that allows multiple instances to run under the same OS user. Typical multi-foundation setups operate each enterprise server install as a different OS user. This works fine, but with the advent of server manager and it's management agents it would require installing and operating a separate managed home for each OS user. This setup works fine, but operating under the same OS user requires only a single agent install which seems like a good idea.

    The first think I did was to stop the existing enterprise server using SM. In a shell I performed a recursive copy of the base directory (/u02/jdedwards/e812 in this case) to /u02/jdedwards/port6015 and /u02/jdedwards/port6016). Back in server manager I created two new instances by registering these new installations with unique instance names.

    Using SM I went through and modified all the configuration items that were install specific. I went through each configuration category and looked for those settings that referenced the old install path and updated them accordingly. Off the top of my head I had to change the following settings
    • Install Path
    • Build Area
    • XTS Repository
    • JDE and JDEDEBUG log configurations
    • jdelog.properties log file configurations
    • Compiler and associated locations
    There are also some settings that are not install location specific that I knew add to be modified. These include
    • The JDENET listen and connect ports (set to 6015 and 6016)
    • The IPC Start Key (set to unique, non-overlapping values)
    Since these new servers were a copy of an existing install I had to modify some of the startup scripts to use the new install paths. These scripts were located in the SharedScripts folder found underneath the base install. This included changing the paths in
    • enterpriseone.sh
    If you are on a release earlier than 8.11SP1 (and thus prior to the platform pack installer) the settings are instead contained in the .oneworld located in the user's home location.  Since this single file defines paths that would be different for each enterprise server instance it is better to use multiple OS users in this case.

    Finally, since I'd be running these servers under the same OS user I had to modify the RunOneWorld.sh and EndOneWorld.sh scripts to set the variable "MULTIPLE_INSTANCE" to 1. This instructs the scripts to be aware that multiple foundations are running under the same OS user. I made this change for all servers, including the original server used as the source.

    Back in SM I tried to start all my new servers. The new servers all failed to start. Looking at the SM logs and a little digging later I realized a problem in the logic in the RunOneWorld.sh script that determines if processes are already running. The script makes a series of calls to the ps application and filters based on OS user and the JDENET port. Since my new installs included the JDENET port in the path (6015 and 6016) it was incorrectly thinking the startup script itself was an existing E1 process and failed start the server. I changed the install locations so they didn't include the 6015 and 6016 values in the path and all worked.

    Sidenotes
    This is probably not a typical setup but once configured it works very well. There are a few notes I'd like to mention.

    First, when performing my initial installation plan I had anticipated these additional ports and defined them initially. This is probably not normal, so one would have to modify their original plan to add the new ports. This creates many of the DB records required for the server such as the F965* tables and the package discovery tables (812 and later).

    Secondly one must be aware of the changes made to RunOneworld.sh and EndOneWorld.sh to set the MULTIPLE_INSTANCE=1 line. Since these scripts are delivered with the tools release they will be overwritten with each new tools release I upgrade the servers to. I'll have to go back and modify those.

    Although this isn't a true step by step guide to configuring multi-foundation I hope it helps the experienced CNCs with some guidance to configuring this sort of configuration.

    Flow Demo at Oracle Open World

    Chris Grillone - Mon, 2007-10-15 18:25
    The famous Pen demo will be at OOW on Wednesday from 2:30-5:30 PM in Space 2 - Mascone West. Draft marketing collateral:This hands-on demonstration highlights Flow Manufacturing and Configurator in a Lean Manufacturing Enterprise. The demonstration begins with a customer, an audience member, ordering a configured-to-order pen using iStore. The sales order is then sent to Order Management and released to manufacturing where a Flow schedule is created. The Flow schedule is executed with two line operations using the HTML Flow Execution Workstation, demonstrating both simple attached documents and video for assembly instructions. Bar code scanning and RFID from Warehouse Management System are used in this complete manufacturing process demonstration. Although this demonstration is provided at an executive level, all manufacturing data is built to support the Flow schedule and will be available to allow the audience members to dive deeper into specific Flow Manufacturing features, such as line design and balance, mixed model map workbench and Kanban execution. Come marvel at our use of a forklift in the demo!

    WF_DEFERRED Queue is having few million messages in "Ready" State

    Madhu Thatamsetty - Mon, 2007-10-15 03:55
    I would like to publish this blog post in the form of a set of questions and answers that will help you resolve a situation if "Ready" state messages in WF_DEFERRED queue are in few millions and Workflow Deferred Agent listener is down from years which caused the pile up of records in WF_DEFERRED Queue.Q1. How do I find out in first place WF_DEFERRED Queue is having millions of records inMadhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com9

    Payroll Reversal Pack K Rollup 2

    RameshKumar Shanmugam - Sun, 2007-10-14 15:44
    HRMS Family Pack RUP 2 was released on Jun 2007.
    Due to current project schedule i didn't had much time to test the new functionality.
    This week i had the chance to install the PF K RUP 2 to my test instance, the lot of functionality which i was expecting is here now...

    The first thing which i would like to highlight is Payroll Reversal functionality.
    If you want to reverse assignments that have been processed as part of a payroll run or QuickPay run, you can now reverse them as a group by assignment set instead of reversing by individual assignment only. Any assignment sets that you use for reversals can also use the normal assignment set features such as:
    • Include or exclude specific assignments
    • Make formula-based amendments

    The original reversal by single assignment is still available, and you can continue to access it from the Assignment window.

    Payroll Reversal Details http://ramesh-oraclehrms.blogspot.com/2007/09/payroll-reversal.html

    Defining Assignment Set http://ramesh-oraclehrms.blogspot.com/2007/08/assignment-set.html

    Try it out!!!

    Categories: APPS Blogs

    Flushing a single cursor

    Fairlie Rego - Fri, 2007-10-12 19:33
    In a very intensive OLTP environment plan stability is of utmost importance as a single sub optimal query can bring the system down to its knees.

    Using sql outlines and sql profiles is always not possible if you have an application with more
    than a million lines of code and any one of the thousands of sql statements could have a plan flip.

    In 11g there is a new procedure in the DBMS_SHARED_POOL package which helps you flush out a single cursor.
    There is a typo in the syntax portion of the documentation in
    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_shpool.htm#sthref8578
    which I will try to get corrected in the next week.

    PURGE Procedure
    This procedure purges the named object or specified heap(s) of the object.

    Syntax

    DBMS_SHARED_POOL.KEEP (
    name VARCHAR2,
    flag CHAR DEFAULT 'P',
    heaps NUMBER DEFAULT 1)
    Parameters

    Table 118-4 KEEP Procedure Parameters

    Parameter Description
    name Name of the object to keep.
    The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.

    Currently, TABLE and VIEW objects may not be kept.

    flag (Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
    Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.

    Set to 'T' or 't' to specify that the input is the name of a type.

    Set to 'R' or 'r' to specify that the input is the name of a trigger.

    Set to 'Q' or 'q' to specify that the input is the name of a sequence.

    In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

    heaps Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
    1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged

    This feature was introduced via the fix in bug 5614566 and I actually know a customer who has this applied on top of 10.2.0.3.

    E.g

    SQL> exec dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge heap 0 and heap 6

    PL/SQL procedure successfully completed.

    This would actually not work against a cursor which is currently executing.(pinned)

    Session 1:
    =========
    Do a massive Merge Join Cartesian
    select * from dba_objects a, dba_objects b, dba_objects c;

    Session 2:
    =========
    Identify the sql address and hash value and try to purge the cursor..

    exec dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This hangs

    and this session is waiting on "cursor: pin X" requesting an exclusive mutex pin for the cursor object whilst it has already been pinned by session 1

    Session 3
    ==========
    select event,p1,p2 from v$session where username='SYS' and type='USER';
    EVENT P1 P2
    ----------------------------------------- ---------- ----------
    cursor: pin X 3691928467 1


    The p1 value here is the Hash value of the cursor we are trying to flush.

    From the short stack of the process which is executing the purge API a function called kxsPurgeCursor is called which would try to take a mutex (since _kks_use_mutex_pin is TRUE by default)
    The purge completes only after you cancel the sql in session 1 and exit from the same
    or kill the session executing the sql.

    11g on Linux x86_64 is out!

    Renaps' Blog - Fri, 2007-10-12 19:15

    This morning, I was surprised to learn that the Linux x86_64 announcement release was not in this Oracle New Downloads RSS Feed. I got aware of the news reading Niall Litchfield Blog. I guess news travels faster via the Oracle blog community! , also, I thought Oracle would have normally released 11g for other o/s prior to releasing the Linux x86_64 version. In any case, I will download this release now.

    It is now available here: Oracle 11g on Linux x86_64.

    I have been waiting for this release for a while since two of my databases are stuck to 32 bits as they need to send and receive data using “hsodbc” (only available for 32bit) for an ERP software.

    Heterogeneous systems are quite common, and I am pretty sure that many of the enhancement requests for Generic Connectivity were focused on making them available for 64-bit platforms.
    The
    Metalink Note:361676.1, Is Generic Connectivity Available On The LINUX Or Windows 64-bit Platforms? , explains that Generic Connectivity will be available on Linux Red Hat 64-bit and SUSE 64-bit with 11gR1.

    Another reason that makes me appreciate this new release is that I ran through several issues while trying to run Oracle with larger SGA’s on 32 bits platforms. I used RAMFS and HugePages to enable the use of 8 to 10GB of SGAs. The following links helped me in this process:

    Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

    Metalink Note: 317141.1, How to Configure RHEL 4 32-bit for Very Large Memory with ramfs and HugePages

    I am hoping that “hsodbc” will work fine on 11g x86_64; I’ll keep you posted on my findings…


    Categories: DBA Blogs

    Pages

    Subscribe to Oracle FAQ aggregator