Feed aggregator

Mail from Application Express with Access Control Enabled

Jornica - Mon, 2007-10-29 17:28

One of the post installation task after installing Oracle 11 is the creation of Access Control List (ACL). Oracle provides a few scripts in order to allow flows_030000 to connect any host. What if you do not allow the database to connect any host but only one host. For instance, you want to send mail from Application Express (APEX) ?

First, set up the email settings in the administrative interface (Home>Manage Service>Instance Settings). Secondly, run the following statements as
system.

-- Create an access control list
BEGIN
dbms_network_acl_admin.create_acl(acl => 'apexmail.xml',
description => 'Mail from Apex', principal => 'FLOWS_030000',
is_grant => TRUE, privilege => 'connect', start_date => NULL,
end_date => NULL);
END;
/

-- Assign the list to your SMTP host, i.e. smtp.yyy.xx
BEGIN
dbms_network_acl_admin.assign_acl(acl => 'apexmail.xml',
host => 'smtp.yyy.xx', lower_port => 25, upper_port => 25);
END;
/

-- Uncomment to remove access control list
--BEGIN
-- dbms_network_acl_admin.drop_acl(acl => 'apexmail.xml');
--END;
--/

-- Please, do commit work.
COMMIT;

You can find more information about the usage of dbms_network_acl_admin in the Database Security Guide. For all the remaining network services you can apply the same technique.

Oracle E-Business Suite Authentication in Ruby

Raimonds Simanovskis - Mon, 2007-10-29 17:00

I was developing Ruby on Rails application that access data from existing Oracle E-Business Suite application and I wanted to add to this application possibility for users to authenticate themselves by their existing Oracle E-Business Suite user names and passwords.

Oracle is not publicly providing algorythms which are used for user passwords encryption (which are stored in FND_USER table) and therefore some googling was necessary. I was lucky to find Jira eBusiness Suite Authenticator with full source code in Java which also included all Oracle E-Business Suite password encryption / decryption functions. Actually it seems that the >1000 lines source code is decompiled from Oracle Java classes as there are no comments and variable names don’t tell anything.

But as I did not have any better source material I started Ruby rewrite of this code and happily managed to finish it in couple of days. As a result I created Oracle EBS Authentication plugin and you can find out in README file how to install it.

Usage of this plugin is quite simple – here is an example:

database_name = ActiveRecord::Base.connection.current_database
authenticator = OracleEbsAuthentication::Authenticator.new(database_name)
if authenticator.validate_user_password(login, password)
  # user authenticated
  if authenticator.user_reponsibilities.include? "System Administrator"
    # user has System Administrator role
  end
  # ...
else
  # authentication failed
end

BTW Oracle EBS password encryption approach has quite a lot of weaknesses. So if you provide SQL*Net access to your Oracle EBS database then anyone with valid Oracle EBS user name and password can decrypt APPS database user password and also can decrypt passwords of all other users. You can have a deeper look in plugin source code to understand how it can be done :)

Categories: Development

Call-Less Cursor

Vlad Sadilovskiy - Mon, 2007-10-29 14:29

Many people know that the Tkprof does not report base statistics and times for calls that have recursive calls. This can be handled by other alternative tools. There is one more unfortunate effect to watch out for when doing tuning of a DB that does fair amount of LOB operations. It seems to be more fundamental and much harder to cope with.

Someone was trying to capture performance problem for BLOB loading application. Any method they tried didn’t show good clue to the problem, and they could wonder around it for longer time unless weren’t hinted on certain LOB handling patterns that had been known for some buggy behavior.

But this was just an issue that made me post this other related observation. Here are two excerpts from Tkprof and Statspack reports from database where the case was reproduced.
 

Tkprof


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.02       0.02          4         86          0           0
Execute      9      0.66       1.14         82       2956       5618           8
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       17      0.68       1.16         86       3044       5618           9

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message from client                100014       54.07        112.26
  db file sequential read                         1        0.00          0.00
  SQL*Net message to client                  100013        0.00          0.19
  log file sync                                  10        0.00          0.02
  latch: shared pool                             11        0.02          0.02


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     4644      0.94       0.93          0          0        107           0
Execute  11059      6.06       9.85        570      45295       5516        8933
Fetch    23131      0.70       2.23        305      38484          3       28673
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    38834      7.71      13.03        875      83779       5626       37606

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    113211        0.30        324.45
  control file sequential read                  855        0.03          0.06
  direct path write                          154743        0.00          0.70
  direct path read                            99973        0.00          0.64
  log file switch completion                    111        0.97         65.21
  log file switch (checkpoint incomplete)       255        0.97        119.11
  latch: shared pool                              4        0.00          0.00
  latch: checkpoint queue latch                   1        0.00          0.00
  latch: library cache                            1        0.00          0.00
  latch: cache buffers chains                     1        0.00          0.00
  db file scattered read                          1        0.00          0.00

Statspack


  Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                        121,368         373      3   49.4
CPU time                                                       141          18.7
log file switch (checkpoint incomplete)            263         122    463   16.1
log file switch completion                         113          66    586    8.8
log file parallel write                          3,837          47     12    6.3
          -------------------------------------------------------------

Time Model System Stats  DB/Inst: TST10203/tst10203  Snaps: 2625-2631
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % of DB time
----------------------------------- -------------------- ------------
DB CPU                                             292.3         40.1
sql execute elapsed time                            45.8          6.3
PL/SQL execution elapsed time                       32.8          4.5
parse time elapsed                                   5.4           .7
hard parse elapsed time                              5.3           .7
PL/SQL compilation elapsed time                      0.1           .0
hard parse (sharing criteria) elaps                  0.1           .0
connection management call elapsed                   0.0           .0
repeated bind elapsed time                           0.0           .0
hard parse (bind mismatch) elapsed                   0.0           .0
sequence load elapsed time                           0.0           .0
DB time                                            728.5
background elapsed time                            130.7
background cpu time                                 27.5
          -------------------------------------------------------------

Both parts indicate that there was serious activity, like waits on the sequential disk reading and redo log synchronization, CPU utilization, that was not reported in category “sql execute elapsed time” and “PL/SQL execution elapsed time”. There was no statement reported in Tkprof or Statspack that generated this activity, but there was a particular cursor seen all over the 10046 trace that was responsible for some of these missing components.

I kind of was lucky to see it, because the cursor has no associated calls whatsoever, only wait records. Unless there are waits associated with such cursor, there will be no records about it in the Oracle Trace. Another “funny” thing is that this call-less cursor can have recursive calls too. This cursor is created solely to write and read LOB content.

So, here are few words of caution for those who do performance troubleshooting using raw trace files, Statspack or ASH/AWR reports. Tkprof or other tools that would try to calculate CPU, Elapsed Time or Wait statistics and build hierarchy of calls of sessions containing LOB operations will likely produce incorrect results from slightly wrong to misleading. This is because calls contain major information that allows traversing call hierarchy and calculating base statistics and timing figures. The “missing” calls might cause whole call hierarchy tree to break. For the same reason Statspack reports may show resource consumption without any visible consumer.

One more thing worth mentioning. If LOB operations are wrapped into code that uses calls to communicate with the database (i.e. PL/SQL anonymous blocks or procedures), then the resources consumed by the LOB operations inside that code will be assigned to these calls.


Where is the Scott schema in APEX?

Jornica - Sun, 2007-10-28 16:39

I decided to install Oracle 11g. And with Oracle 11g Application Express (APEX)is installed by default. Before importing an application based on the emp table, I decided to create a workspace based on the existing schema Scott. However the administrative interface did not allow me to select Scott. By not using the LOV but typing the schema name, APEX gave the following error: Schema is reserved or restricted. What is going on?

APEX uses the following query to populate the list of values:

SELECT name n,
name r
FROM sys.user$ u
WHERE type# = 1
AND name <> 'FLOWS_030000'
AND name NOT IN('HTMLDB_PUBLIC_USER', 'PUBLIC_USER', 'FLOWS_FILES',
'SCHEDULER', 'PEOPLE', 'APEX_PUBLIC_USER')
AND name NOT IN
(SELECT SCHEMA
FROM wwv_flow_restricted_schemas
WHERE NOT EXISTS
(SELECT 1
FROM wwv_flow_rschema_exceptions
WHERE schema_id =
(SELECT id
FROM wwv_flow_restricted_schemas
WHERE SCHEMA = u.name)
)
)
ORDER BY 1
/

Beside some hard coded schema names, there are two flows_030000 tables used in the query, wwv_flow_restricted_schemas and wwv_flows_rschema_exception.

SQL>SELECT * FROM flows_030000.wwv_flow_restricted_schemas t
/
ID SCHEMA CREATED_BY CREATED_ON LAST_UPDATED_BY LAST_UPDATED_ON
-- ------ ---------- ---------------- --------------- ---------------
...
38 SCOTT SYS 3-8-2007 2:12:53
...

The wwv_flows_rschema_exception contains no rows.

Both tables are used by the package htmldb_site_admin_privs, which serves as an interface packages. Now remove the restriction on Scott by executing the following statements (as Sys).

SQL>exec flows_030000.htmldb_site_admin_privs.unrestrict_schema(p_schema => 'SCOTT');
SQL>commit;
And of course, this topic is covered by the Application Express User's Guide.

I'm sailing off into the sunset

Fake Larry Ellison - Sun, 2007-10-28 16:28

Friends, I hope you're sitting down right now. I'm afraid I have some bad news. Recently there have been some complications with my lawyers and the public relations department, so I'm going to have to wrap things up here on this little blog. And in case you're wondering, yes, I know what you're thinking. I'm sorry it had to end this way, friends. But look on the bright side. Every time you make a phone call and purchase something with your credit cards, every time you make an appointment with your cosmetic surgeon and fire an employee at your corporation, chances are you'll be using an Oracle product. And every time you use an Oracle product, our paths will cross, if only briefly.

I'd like to thank you, dear readers, for all of the comments and email messages. And it would probably be a crime not to send shout-outs to the kind people at the San Jose Mercury News, MacSurfer, ZDNet, InfoWorld, and Oracle Apps Lab for all of the favorable press. And, of course, Zack Urlocker. I'll do my best to send free fake beta 12g databases out to everybody. Peace.

Frameworkia

Oracle WTF - Fri, 2007-10-26 02:33

We thought long and hard about possible titles for this new PL/SQL development standard proposed on OTN, but we couldn't improve on the one it came with.

I want share a new IDEA to create a new standard PL/SQL developing:


Function ( Standard Buffer) return number variable Number; variable1 Varchar2; begin variable := get from Standard Buffer; variable1 := get from Standard Buffer; { make your business } put in standard buffer your results end;

Give me feedback if you are interested at the new STANDARD called "FRAMEWORKIA".

A lot possibilities are ready.

Do you see the genius of it?

Er, no.

Sensing that there were people who still needed convincing, user601181 posted some sample code developed using the new Frameworkia:

CREATE OR REPLACE FUNCTION iacore
    ( eobufferia IN OUT typeeodata )
    RETURN NUMBER
IS
    CURSOR cur_getroutingcondition
        ( idc_workflow IN VARCHAR2
        , idc_operation_node IN VARCHAR2 ) IS
        SELECT *
        FROM   wf_condition
        WHERE  id_workflow = idc_workflow
        AND    id_operation_node = idc_operation_node;

    rec_getroutingcondition cur_getroutingcondition%ROWTYPE;

    CURSOR cur_dobufferiaassign
        ( idc_workflow IN VARCHAR2
        , idc_operation_node IN VARCHAR2 ) IS
        SELECT *
        FROM   wf_assignement
        WHERE  id_workflow = idc_workflow
        AND    id_operation_node = idc_operation_node;

    rec_dobufferiaassign cur_dobufferiaassign%ROWTYPE;

    next_node         NUMBER;
    next_node_ck      NUMBER;
    stop_node         NUMBER;
    operation         VARCHAR2(256);
    operation_call    VARCHAR2(256);
    type_node         VARCHAR2(32);
    workflow          VARCHAR2(32);
    line              VARCHAR2(256);
    status_wf_v       VARCHAR2(3);
    pid_chain_node    NUMBER;
    ia_tid            VARCHAR2(64);
    ia_tid_micro      VARCHAR2(64);
    ret_code_default  NUMBER;
    ret_code          NUMBER;
    retval1           NUMBER;
    statementexc      VARCHAR2(256);
    schema_function   VARCHAR2(32);
    package_function  VARCHAR2(32);
    dblink_function   VARCHAR2(32);
    first_node_flag   VARCHAR2(2) := 'NO';
    id_debug_source   NUMBER;
    mapin_keyp        VARCHAR2(1024);

    headerbufferia typebufferia;
    assignbufferia typebufferia;
    checkbufferia  typebufferia;

    rec_wfnode               wf_node%ROWTYPE;
    rec_wffunctionsourcecode wf_function_source_code%ROWTYPE;
    rec_wflogger             wf_logger%ROWTYPE;
    rec_wfbusiness           wf_business%ROWTYPE;
    rec_wffieldmapping       wf_fieldmapping%ROWTYPE;
BEGIN
    headerbufferia := eobufferia(1);

    workflow := frameworkia.getvalue(headerbufferia,'ID_WORKFLOW');

    ---- DETERMINO QUALE NODO INVOCARE
    pid_chain_node := frameworkia.getvalue(headerbufferia,'WF_NODE_ID');

    ----- SE IL NODO E' NULL ALLORA E' IL PRIMO NODO
    IF pid_chain_node IS NULL
    THEN
        -------DETERMINO HANDLER E FILENAME PER IL LOGGER
        SELECT *
        INTO   rec_wflogger
        FROM   wf_logger
        WHERE  id_workflow = workflow;
        -- rec_WfLogger.ID_WORKFLOW
        -- rec_WfLogger.ID_DEBUG_LEVEL
        -- rec_WfLogger.ID_DIRHANDLER
        -- rec_WfLogger.ID_FILENAME

        --------INSERISCO NELL'HEADER
        frameworkia.setvalue
        ( headerbufferia
        , 'ID_DEBUG_WF'
        , rec_wflogger.id_debug_level );

        frameworkia.setvalue
        ( headerbufferia
        , 'ID_DIRHANDLER'
        , rec_wflogger.id_dirhandler );

        frameworkia.setvalue
        ( headerbufferia
        , 'ID_FILENAME'
        , rec_wflogger.id_filename );

        frameworkia.setvalue
        ( headerbufferia
        , 'CHARACTER_EVIDENCE'
        , '§§§§§§§§§§§§§§§§§§§§' );

        -------DETERMINO L'ID NODE
        SELECT wf_node_id
        INTO   pid_chain_node
        FROM   wf_node
        WHERE  id_workflow = workflow
        AND    wf_first_node = 'YES';

        SELECT *
        INTO   rec_wfnode
        FROM   wf_node
        WHERE  id_workflow = workflow
        AND    wf_first_node = 'YES';

        frameworkia.setvalue
        ( headerbufferia
        , 'WF_NODE_ID'
        , rec_wfnode.wf_node_id );

        SELECT b.status
        INTO   status_wf_v
        FROM   wf_node a
             , wf_name b
        WHERE  a.id_workflow = workflow
        AND    a.wf_node_id = rec_wfnode.wf_node_id
        AND    a.id_workflow = b.id_workflow;

        IF status_wf_v = 'OFF'
        THEN
            RETURN -1;
        END IF;

        ia_tid := frameworkia.getvalue(headerbufferia,'IA_TID');
        ret_code_default := 0;
        ret_code         := 0;

        frameworkia.setvalue
        ( headerbufferia
        , 'RET_CODE_DEFAULT'
        , ret_code_default );

        frameworkia.setvalue
        ( headerbufferia
        , 'RET_CODE'
        , ret_code);

        IF ia_tid IS NULL
        THEN
            ia_tid := 'TIA' || dbms_random.STRING('U',1 * 1 + 6) ||
                      TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6');

            frameworkia.setvalue
            ( headerbufferia
            , 'IA_TID'
            , ia_tid );
        END IF;

That's just the first hundred lines, and I've formatted it. The complete iacore function was well over 600 lines long.

I for one welcome the new standard.

HRMS PFK RUP2 Global name Search

RameshKumar Shanmugam - Fri, 2007-10-26 01:33
Mainly in the multi BG environment we end up in creating duplicate employee name, the current HRMS search functionality some times the duplicates are missed out due to the localization and the Name format used in each legislation.

In the Family Pack K Rup 2, System Uses Global Name format to Search for the Duplicates Across Business Group.
The Global name format is independent of the Legislation which helps us to avoid the duplication.

To Enable this Functionality we need to enable the profile HR:Cross BG duplication Person Check.

Still the HRMS uses the same existing functionality to search the employee in single business group

Try this out!!!

Regards
Ramesh Kumar S
Categories: APPS Blogs

I just saved 7,532 houses

Fake Larry Ellison - Thu, 2007-10-25 14:39

I think all of us have been touched by the fires burning outside Los Angeles and San Diego this week. In fact, I've lost a couple of my own properties down in Malibu. BTW, they're the same properties I wanted to tear down and rebuild, so it's no biggie. But listen. Just thinking about your entire home going up in smoke is enough to motivate anybody to help people in harm's way. I know if my Japanese gardens were on fire -- if all the azaleas and bamboo and Japanese maples and bonsai marijuana plants were going up in smoke -- I'd want somebody to come and help me. So I thought, what the heck? I told my aircraft guys to get in there and convert my C-130 into a tanker ASAP, and then I flew down to San Diego and doused some flames. I helped put out a fire or two, and then I went back and dropped a couple thousand Satanic Surfers CDs over Qualcomm Stadium.

I made a difference, which is more than most of you can say. I bet you lazy turds just sat in your leather armchairs and had the news about BEA Systems read to you by beautiful girls in bikinis while getting foot massages and facials. Am I right or am I right? Shame on you freaks.

Tuning Collections in Queries [1]

Vlad Sadilovskiy - Thu, 2007-10-25 08:40

[To Introduction

I will continue series about tuning collections in queries starting from the simplest case – collection of values joined to one table. Our subject SQL statements look similar to the following two forms.

1)
select table1.*
  from table1
 where table1.f1 in
       (select column_value from table(test_statement.getList1) nlist1);

and

2)
select table1.*
  from table1, table(test_statement.getList1) nlist1
 where t1.object_id = nlist1.column_value;

We will run them in several ways by adding hints and CBO parameters. First test run is dedicated to show default CBO computations. Note cryptic name KOKBF$ in the predicate section of the explain plan. KOKBF$ is a generic alias given the table expressions. COLLECTION ITERATOR PICKLER FETCH is an operation that retrieves values from collections (if collection was instantiated in the query the operation will read: COLLECTION ITERATOR CONSTRUCTOR FETCH).

Test 1.1:

select /*+ gather_plan_statistics */        t1.object_name,t1.object_type   from t1
where t1.object_id in        (select *           from table(cast(test_statement.getList1 as table_of_number)) nlist1)

Plan:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                       |          |      1 |    255 |   1252K|00:02:07.32 |    5091K|    177K|   2959 |
|   2 |   SORT UNIQUE                       |          |      1 |    255 |   1878K|00:00:31.30 |      31 |   2959 |   2959 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| GETLIST1 |      1 |        |   2000K|00:00:04.28 |       0 |      0 |      0 |
|   4 |   TABLE ACCESS BY INDEX ROWID       | T1       |   1878K|      1 |   1252K|00:01:31.73 |    5091K|    174K|      0 |
|*  5 |    INDEX UNIQUE SCAN                | T1_I     |   1878K|      1 |   1252K|00:00:37.04 |    3839K|  28328 |      0 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."OBJECT_ID"=VALUE(KOKBF$))

About two minute runtime is not completely bad. But let’s see what we are driving into here. Nested Loops and Index Access isn’t very good choice for getting > 10% of the data of the base table. Clearly, the estimated cardinality in bold is the culprit here. The value in row with Id 2 is not the part of the output, rather it was taken from CBO trace figures. The original 10053 trace file doesn’t have definite clue where this figure comes from. For the reason described below, this could be a function of the default block size.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: KOKBF$  Alias: KOKBF$  (NOT ANALYZED)
    #Rows: 8168  #Blks:  100  AvgRowLen:  100.00

...
Join order[2]:  KOKBF$[KOKBF$]#1  T1[T1]#0
    SORT resource      Sort statistics
      Sort width:         459 Area size:      402432 Max Area size:    80530432
      Degree:               1
      Blocks to Sort:    2865 Row size:           13 Total Rows:        1800000
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1554
      Total IO sort cost: 4419      Total CPU sort cost: 1764307007
      Total Temp space used: 28894000
***************
Now joining: T1[T1]#0
***************
NL Join
  Outer table: Card: 255.25  Cost: 4653.33  Resp: 4653.33  Degree: 1  Bytes: 2

The outer table KOKBF$ was assigned default cardinality as num_of_blocks * (block_size – cache_layer) / avg_row_len. In all tests cache_layer is 24. Something tells me it is similar to the 24 bytes per INITRANS, but I still cannot get my head around the fact where the block fixed header size has gone. It might be something else though. Anyway, given the figures, our default cardinality is 100 * (8192 – 24) / 100 = 8168. With 16K block this would equal 16360 etc. When the collection is uniquely sorted for “IN” clause, its computed cardinality is reduced to 255.25 and rounded to 255. With 16K block this becomes 511.5. This is persuasively looks like default_cardinality/32. One more reason to think the value comes from default statistics assumptions shown in the below CBO trace which is generated by this simple query.

select distinct column_value from table(test_statement.getList1);

The CBO trace in this case has following number of distinct values taken from the default statistics of the collection. If this is so, it explains how CBO arrives at the cardinality of the uniquely sorted collection values in the above calculation.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: KOKBF$  Alias: KOKBF$  (NOT ANALYZED)
    #Rows: 8168  #Blks:  100  AvgRowLen:  100.00
  Column (#1): KOKBC$(VARCHAR2)  NO STATISTICS (using defaults)
    AvgLen: 1.00 NDV: 255 Nulls: 0 Density: 0.0039177

But I will shut up about it for now until I find out this for certain. Now let’s see what the second form of the testing queries produce.

Test 1.2:

select /*+ gather_plan_statistics */
 t1.object_name,t1.object_type   from t1, table(cast(test_statement.getList1 as table_of_number)) nlist1
where t1.object_id = nlist1.column_value

Plan:

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                      |          |      1 |   8168 |   1333K|01:27:04.70 |    5422K|   2692K|
|   2 |   COLLECTION ITERATOR PICKLER FETCH| GETLIST1 |      1 |   8168 |   2000K|00:00:06.30 |       0 |      0 |
|   3 |   TABLE ACCESS BY INDEX ROWID      | T1       |   2000K|      1 |   1333K|01:26:51.90 |    5422K|   2692K|
|*  4 |    INDEX UNIQUE SCAN               | T1_I     |   2000K|      1 |   1333K|00:05:39.05 |    4088K|   1382K|
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"=VALUE(KOKBF$))

In this case the default cardinality of the 8168 rows of the collection was left intact. The plan didn’t change from Nested Loops for obvious reasons. The time and number of reads skyrocketed. This happened because the collection values were randomly chosen and not sorted as they were in the previous example. Access of the base table became more scattered and subsequently more reads took place. If a key value distribution is known and clustered, it might be worth using co-located values in collections.

Now it’s time to move on to the different settings. To change CBO default cardinality estimation this query uses officially unsupported CARDINALITY hint. You should ask Oracle permission to use it in production.

Test 2.1 (adjusted cardinality):

select /*+ gather_plan_statistics */        t1.object_name,t1.object_type   from t1  where
t1.object_id in        (select /*+ cardinality(nlist1 1800000) */ *           from table(cast(test_statement.getList1 as table_of_number)) nlist1)

Plan:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
----------------------------------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                       |          |      1 |    255 |   1252K|00:01:37.31 |    5092K|    172K|   2959 |
|   2 |   SORT UNIQUE                       |          |      1 |    255 |   1878K|00:00:27.63 |     160 |   2970 |   2959 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| GETLIST1 |      1 |        |   2000K|00:00:04.32 |     129 |     11 |      0 |
|   4 |   TABLE ACCESS BY INDEX ROWID       | T1       |   1878K|      1 |   1252K|00:01:04.31 |    5091K|    169K|      0 |
|*  5 |    INDEX UNIQUE SCAN                | T1_I     |   1878K|      1 |   1252K|00:00:23.96 |    3839K|  23944 |      0 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."OBJECT_ID"=VALUE(KOKBF$))

No changes in the plan and statistics. What happened?! Did CBO even considered the change? Let’s take a look at the CBO trace.

***********************
Table Stats::
  Table: KOKBF$  Alias: KOKBF$  (NOT ANALYZED)
    #Rows: 8168  #Blks:  100  AvgRowLen:  100.00
***************************************
SINGLE TABLE ACCESS PATH
  No statistics type defined for function TEST_STATEMENT
  Table: KOKBF$  Alias: KOKBF$
    Card: Original: 8168    >> Single Tab Card adjusted from: 8168.00  to: 1800000.00
  Rounded: 1800000  Computed: 1800000.00  Non Adjusted: 8168.00

It has obviously mentioned the hint value. But the final cardinality got value as before for the same reason of collection being processed as part of “IN” sub-query. I’ll get back to this a little later.

Alright, let’s see what the second form would be like.

Test 2.2 (adjusted cardinality):

select /*+ gather_plan_statistics cardinality(nlist1 1800000) */
t1.object_name,t1.object_type   from t1, table(cast(test_statement.getList1 as table_of_number)) nlist1  where t1.object_id = nlist1.column_value

Plan:

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
---------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                         |          |      1 |   1800K|   1333K|00:01:37.68 |     145K|    189K|  45724 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| GETLIST1 |      1 |   1800K|   2000K|00:00:04.22 |       0 |      0 |      0 |
|   3 |   TABLE ACCESS FULL                | T1       |      1 |     10M|     10M|00:00:31.42 |     145K|    143K|      0 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"=VALUE(KOKBF$))

Now, the hinted cardinality is in the final execution plan. And no wonder the CBO switched the plan from Nested Loops to Hash Join. Compare the logical and physical reads of this test and tests 1.1 and 2.1. If I would have allowed bigger hash area for this query, it could run in-memory and show even better results. Last test for this post is suppose to fix the query with the sub-query. For this we would need to do an additional modification. This time modification of the CBO parameter “_always_semi_join” is needed.

Test 3.1: (adjusted cardinality, “_always_semi_join”=off):

select /*+ gather_plan_statistics */        t1.object_name,t1.object_type   from t1  where
t1.object_id in        (select /*+ cardinality(nlist1 1800000) */ *           from table(cast(test_statement.getList1 as table_of_number)) nlist1)

Plan:

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
-----------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                           |          |      1 |   1800K|   1252K|00:01:26.75 |     145K|    195K|  50757 |
|   2 |   VIEW                               | VW_NSO_1 |      1 |   1800K|   1878K|00:00:11.52 |       0 |   6825 |   6825 |
|   3 |    HASH UNIQUE                       |          |      1 |   1800K|   1878K|00:00:09.64 |       0 |   6825 |   6825 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| GETLIST1 |      1 |   1800K|   2000K|00:00:04.24 |       0 |      0 |      0 |
|   5 |   TABLE ACCESS FULL                  | T1       |      1 |     10M|     10M|00:00:31.40 |     145K|    144K|      0 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="COLUMN_VALUE")

Why, you might ask, the Semi Join parameter has any effect on this? In pre evaluation phase CBO tries to apply different query rewrite techniques in order to optimize the query. One of them is unnesting sub-query. In this case it can either do Semi Join or unnest the sub-query as a set (collection of distinct values). Since the Semi Join is disabled it used the later. The sub-query took following form with cardinality hint propagated to the outer query.

SELECT "T1"."OBJECT_NAME" "OBJECT_NAME","T1"."OBJECT_TYPE" "OBJECT_TYPE" FROM  (SELECT /*+ OPT_ESTIMATE (TABLE "KOKBF$" ROWS=1800000.000000 ) */ DISTINCT VALUE(KOKBF$) "COLUMN_VALUE" FROM TABLE(CAST("TEST"."TEST_STATEMENT"."GETLIST1"() AS "TABLE_OF_NUMBER") ) "KOKBF$") "VW_NSO_1","TEST"."T1" "T1" WHERE "T1"."OBJECT_ID"="VW_NSO_1"."COLUMN_VALUE"

Basically the original query was rewritten into following form. It is similar to the second original form with the exception of distinct clause. Cardinality hint was not ignored as it was in the original query and CBO generated desired plan.

select /*+ cardinality(nlist1 1800000) */
       t1.object_name,t1.object_type
  from t1
     , (select distinct column_value from table(cast(test_statement.getList1 as table_of_number))) nlist1
 where t1.object_id = nlist1.column_value;

Few words in conclusion. When tuning these queries, make sure CBO doesn’t rely on default statistics. In most cases CBO will subsequently generate sub-optimal plan, i.e. NLs instead of desired HJ or vice versa. If you cannot disable Semi Join, unnest “IN” sub-query. When tuning “IN” sub-query, use cardinality value close to the number of distinct elements, because that is what CBO will use in its calculations. If distribution of table data is deterministic, co-locate the values of the joined collection whenever possible to increase clustering of the access operation.


Multiple Verison of I.E on one machine

Duncan Mein - Thu, 2007-10-25 02:39
After the lastest round of updates were installed from Microsoft, I foolishly forgot to uncheck the "Upgrade to I.E 7" box and hence after a reboot, a fresh new version of Internet Explorer was waiting for me. On face value this appeared ok until I tried to access Mercury Test Director.

According to the error message, only I.E 6 was supported.

A quick search on google and I happened upon this website: http://tredosoft.com/Multiple_IE
and downloaded the installer which contained multiple version of I.E from 3.0 to 6.0 that run in standalone.

Not only has this fixed my problem of accessing applicaitons, it also allows me to test my applications against earlier versions of I.E.

Very useful indeed

Dear Shit for Brains...

Fake Larry Ellison - Tue, 2007-10-23 13:46

I know you laughed at the iPhone and everything, but now 1995 is calling and it wants its phones back. As in, your phones, Stevie. All of those devices running Windows are out, bro. The people spoke yesterday. They want technology that actually works. They want phones that can actually make phone calls without crashing. People would rather use rotary phones that your Winphones. Yes, I know you're shocked.

CNET cheerleaders go apeshit for MySQL

Fake Larry Ellison - Tue, 2007-10-23 13:15

This time they're citing a survey by the Independent Oracle Users Group (IOUG) and claiming that our users "go ga-ga for open source, including MySQL." Which I guess stands to reason if you only read the first two paragraphs of the survey and then ignore everything else. Like the part where people say that they would never use MySQL for anything other than testing because it's insecure, unsupported, and "typically not used for mission-critical applications."

Tell you what, CNET. Why don't you start using MySQL on your production webservers and stop repeating verbatim what you hear from MySQL's Vice President of Products? Then maybe you'll sound less like an infomercial and more like an actual news source. Maybe.

Oracle Database 11g for Windows now available

Christian Shay - Tue, 2007-10-23 12:36
Download Oracle Database 11g on Windows!

We'll be talking about Windows specific 11g new features at Oracle OpenWorld sessions so reserve your seat today!

SELECT as many rows as indicated by column value

Mike Moore - Tue, 2007-10-23 12:15
This select statement will return as many rows as are indicated by the value of a column. For example, if the column value is 7, then 7 copies of that row will be returned.


CREATE TABLE S

(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;

Insert into S
(RNUM, TPART, SQ)
Values
(101, 'TEST_PART1', 1);
Insert into S
(RNUM, TPART, SQ)
Values
(102, 'TEST_PART2', 2);
Insert into S
(RNUM, TPART, SQ)
Values
(103, 'TEST_PART3', 3);
Insert into S
(RNUM, TPART, SQ)
Values
(105, 'TEST_PART5', 5);
COMMIT;


SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);

Carl Icahn, pull my finger

Fake Larry Ellison - Mon, 2007-10-22 20:24

When I was heli-skiing in Jackson Hole last week, I realized that the only thing more dangerous than getting between a grizzly bear and her cubs is getting between Carl Icahn and a dollar bill. Seriously. Because here's the thing. A couple months ago we were thinking, holy cow, Carl fucking Icahn is buying up BEA Systems and the dude is ready to sell. Like, now. So I told Charles to throw an offer together and mail it in to BEA ASAP so we could pop the champaign, fire up the mechanical bull, and start rocking my boats. But then Carl said, well, uh, actually no, the offer isn't high enough, and could we please come back with a better offer? As if he really knows how these things work. And as if he's had a lot of luck with the businesses he's invested in recently. Like, um, Motorola and Time Warner. Ouch, Carl.

Anyway, I'm really happy articles like this one about Carl have started appearing around the blogosphere. This butthead needs to be called out and revealed for what he actually is: A little old man trying to play a game of monopoly with real businesses. And to be completely honest with you, that kind of board-game mentality might work in places like New York. Hell if I know. What I can tell you is nobody fucks with Larry Ellison and gets away with it. So, yeah. This isn't over, Icahn.

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.

    Pages

    Subscribe to Oracle FAQ aggregator