Feed aggregator

String to Rows: Hierarchical Methods (New SQL Snippets Tutorial)

Joe Fuda - Sun, 2007-07-29 13:30
A new, hierarchical technique for converting strings to rows has been added to the SQL Techniques Tutorials - String to Rows tutorial in SQL Snippets.

Integer Series Generators: CONNECT BY LEVEL Method (Updated SQL Snippets Tutorial)

Joe Fuda - Sun, 2007-07-29 13:30
This tutorial has been updated to include a discussion of how hierarchical queries that do not have a CONNECT BY condition with PRIOR behave. A sample query that uses "PRIOR DBMS_RANDOM.VALUE IS NOT NULL" to make the CONNECT BY LEVEL technique conform to documented requirements has also been added.

Accessing Custom Forms after Upgrading To R12

Fadi Hasweh - Sun, 2007-07-29 03:23
I was checking customization upgrade subject since we faced it and I found this interesting note (451934.1)

The note is talking about how to make custom forms that where created and working fine on 11i to work the on R12.
And according to the note
"All custom forms that were build and working fine on releases 11i are designed and compiled using the Form Builder 6i, while the developer version for R12 is 10G.So you need to open the custom forms in Form Builder release 10G and compile them and save then upload them again."

I did not try the solution yet but thought of sharing it since many were asking bout this subject lately.
Also i found the (Custom forms does not work after upgrade to 12i) forum helpful

Data consistency and accuracy

Peter Khos - Fri, 2007-07-27 17:49
Earlier this week, a private contractor working for the City of Burnaby (a suburb of Vancouver) dug their backhoe into a high pressure oil pipeline (a video of incident) sending about 1,400 barrels of crude oil high into air covering the neighbourhood and eventually leaked out into the Burrard Inlet. It took half of an hour before the pipeline was switched off and two hours before the oil boom Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

11.0.3 to Upgrade

Madan Mohan - Fri, 2007-07-27 04:09
Production Cut-over Checklist
10th Novembert, 2006

Environment Name:

Prepared By:
Madan Mohan K

1.0 Pre-Upgrade Checks

· OS user for RDBMS
· Group for or11iuat
· OS user for APPLMGR
· Group for ap11iuat
· OS utilities to be in PATH
· Java version
· Perl version
· Zip version
· Unzip version
· Oracle Inventory file
· Oracle Inventory permissions
· Add to .profile for all Unix users
· Permission for $APPLCSF
· X-server installed
· Stage area is installed

ar cc make ld zip unzip perl java javac
ulimit -d 1048576
Xvfb installed
2.0 Pre Upg tasks on 11.0.3 instance

2.0.1. Apply the Patch 1268797
cd $FND_TOP/patch/110/sql
sqlplus / @afstatrn.sql True
2.0.2. Apply the TUMS patch 3422686
cd $AD_TOP/patch/110/sql
sqlplus apps/app @adtums.sql /u10/app/convr11/tmp
2.0.3 Set the File attachments

2.0.4 . Submit “Purge Concurrent
Requests” with a retention
period of 7 days

2.0.5. Check for rows in
Select count(*) from apps. ALR_ACTION_HISTORY;
If No rows then Apply the Patch 451137
2.0.6. Disabled all the Custom Triggers and

2.0.7 Disabled Database Audit trial in init.ora
Audit_trial= false or comment out the line.
2.0.8 Validate and Compile Apps Schema
Use adadmin Utility
2.0.9 Backup the INVALIDS in a Table
Create table BEFORE_PREUPG_INVALIDS as select * from dba_objects where status=’INVALID’;
2.0.10. Make a note of passwords for following
Oracle user - APPS
Oracle user - SYSTEM

3.0 Pre Upgrade Tasks on 11i Instance

3.0.1. Run Rapid install on DB tier
./rapidwiz –servername
3.0.2. Run Rapid install on MT tier
./rapidwiz –servername
3.0.3 Apply the Forms Patch set 18

3.0.4 Install software on DB tier (4163445)
3.0.5 Install Latest Opatch 2617419 on DB Tier
Unzip *2617419*.zip in $OARCLE_HOME
3.0.6 Applied Database patch 4192148 for Import

3.0.7 Update the .profile in MT and DB Tiers .
On MT :
- /<>/applmgr/r11i<>appl/APPSORA.env
- $ORACLE_HOME/ R11i<> _ .env

3.0.8 InstallPrep.sh script as per Note 189256.1

3.0.9 Configured tnsnames.ora on MT to connect to 8.1.7 database of 11.0.3 instance

3.0.10. Login to MT tier and perform following pre
upgrade tasks
Verify custom index privileges - Done
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta afindxpr.sql applsys
sqlplus apps/cl_11i_gbook_sta afpregdi.sql

Make sure orders are in a supported status - Done
cd $ONT_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ontexc07.sql

Review Item Validation Org settings – Done
cd $ONT_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ontexc05.sql

Close open pick slips/picking batches or open deliveries/departures – Done
cd $WSH_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @wshbdord.sql

Validate inventory organization data – Done
cd $WSH_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @wshpre00.sql

Review cycles that may not be upgraded – Done
cd $ONT_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ontexc08.sql

Clear open interface tables – Done
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta @
Run above sql for each of the following scripts:
Requisitions Open Interface (pocntreq.sql)
Purchasing Documents Open Interface (pocntpoh.sql)
Receiving Open Interface (pocntrcv.sql)

Import and purge Invoice Import Interface expense reports and invoices – Done
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta @apuinimp.sql

Diagnose problems in the data – Done
cd $AR_TOP/patch/115/sql
sqlplus apps/cl_11i_gbook_sta @ar115chk.sql

Identify potential ORACLE schema conflicts - Done
cd $APPL_TOP/admin/preupg
sqlplus applsys/cl_11i_gbook_sta @adpuver.sql
3.0.10 Apply Family Consolidated Upgrade Patch for Financials - 3993353 with preinstall=y

3.0.11 Shutdown Mid Tier services
and Database for 11.0.3
Instance & Bkup the 11.0.3 DB

4.0. Database Upgrade using Export /
Shutdown the 11.0.3 MID Tier Services

Login to 11.0.3 Database and Note the global_name
on Source Instance.
Select global_name from global_name;

Extracted following files which are used for export / import from patch 4872830
adclondb.sql – Ran this script to generate adcrdb.sql. adcrdb.sql contains create database and create tablespace which needs to be executed on 11i
sqlplus SYSTEM/SYSTEM_r11clone @adclondb.sql 9
Ran auque1.sql, which generates a script called auque2.sql. This is required since Advanced Queue settings are not propagated in Export / Import process. auque2.sql needs to be executed on Target Instance.
auexpimp.dat – Parameter file for Export and Import
Export 11.0.3 database using following par file
exp parfile=exp_parameter.dat


5.0. Import 11.0.3 database to 11i

Login to 11i Database Tier

Copy the initR11CLONE.ora from 11.0.3 instance to initR11i.ora on 11i and modify the directoey locations for controlfiles, all dump destinations, and also specify APPS_UNDO for undo tablespace

Updated Global_name for this Database to the value on 11.0.3
alter database rename global_name to 'R11CLONE.WORLD';

Create the apps user as

create user apps identified by cl_11i_gbook_sta default tablespace applsysd temporary tablespace

Setup the Enterprise Edition for Oracle 9i
These scripts create objects required by the RDBMS and other technology stack components on the database server
Login to 11i Database Tier
Created folder $ORACLE_HOME/appsutil/admin
Copied addb920.sql, adsy920.sql, adjv920.sql, admsc920.sql, and adgrants.sql from $APPL_TOP/admin to $ORACLE_HOME/appsutil/admin

sqlplus /nolog
connect SYSTEM/system_r11iuat
@addb920.sql -- Sets up database SYS schema

sqlplus /nolog
connect SYSTEM/system_r11iuat
@adsy920.sql -- Sets up database SYSTEM schema

sqlplus /nolog
connect SYSTEM/system_r11iuat
@adjv920.sql - Installs Java Virtual Machine

sqlplus /nolog
connect SYSTEM/system_r11iuat
@admsc920.sql FALSE CTXD TEMP $ORACLE_HOME/ctx/lib/libctxx9.so

Check for output of dba_registry is as given below

col comp_name for a40
col version for a15
select comp_name, version, status FROM dba_registry;

Imported 11.0.3 database into 11i using following parameter file

Script imp_time_taken
imp parfile=imp_parameter.dat


Run preparatory scripts

sqlplus /nolog
connect SYSTEM/system_r11clone
@adgrants.sql APPLSYS -- Grants necessary privileges on selected SYS objects.

Applied database patch to create / update OWS packages
Login to 11i Database Tier 3835781
mkdir $ORACLE_HOME/appsutil/admin/OWS
cd $ORACLE_HOME/appsutil/admin/OWS
cp /R11isit/staging/patches/*3835781* .

Installed XML Parser for PL/SQL
Login to 11i Database Tier
mkdir $ORACLE_HOME/appsutil/admin/xmlparser
cd $ORACLE_HOME/appsutil/admin/xmlparser
cp $COMMON_TOP/util/plxmlparser_v1_0_2.zip .
unzip plxmlparser_v1_0_2.zip - This file creates several subdirectories in the current location
cp $COMMON_TOP/util/XSU12_ver1_2_1.zip .
unzip XSU12_ver1_2_1.zip - This file creates subdirectory - OracleXSU12
cd /R11iuat/db/9.2.0/appsutil/admin/xmlparser/lib/java
loadjava -user apps/cl_11i_gbook_sta -r -v xmlparserv2.jar
loadjava -user apps/cl_11i_gbook_sta -r -v xmlplsql.jar
cd ../sql
cat load.sql sqlplus apps/cl_11i_gbook_sta
cd /R11iuat/db/9.2.0/appsutil/admin/xmlparser/OracleXSU12/lib
sh oraclexmlsqlload.ksh
mkdir $ORACLE_HOME/appsutil/admin/java
cd $ORACLE_HOME/appsutil/admin/java
cp /R11iuat/oradata/R11iEXP/applmgr/r11iexpcomn/java/xmlparserv2.zip .
loadjava -user apps/cl_11i_gbook_sta -r -v xmlparserv2.zip

Re-compile Invalids
sqlplus “/ as sysdba”
create table invalids_after_db_backup as select * from dba_objects where status = ‘INVALID’;

Set up tablespaces
Increased SYSTEM tablespace size to 9 GB before you run the scripts given below
cd $AD_TOP/patch/115/sql
sqlplus APPS/cl_11i_gbook_sta @adgncons.sql apps cl_11i_gbook_sta APPLSYS
Gathered database information
Login to 11i Mid tier
cd $APPL_TOP/admin/preupg
sqlplus apps/cl_11i_gbook_sta @adupinfo.sql

Ran preparatory script on Administration node
Login to 11i Mid tier
cd $APPL_TOP/admin
sqlplus system/system_r11clone @adsysapp2.sql system_r11clone

6.0 Auto Upgrade Tasks
Login to 11i Application Tier
o Run Autoupgrade using adaimgr

6.0.1 Post-Autoupgrade tasks
Login to 11i Database Tier
Re-compile Invalids
sqlplus “/ as sysdba”
create table invalids_after_autoupgrade as select * from dba_objects where status = ‘INVALID’;

6.0.2 Performed cold Backup of

7.0 Autopatch Tasks
Login to 11i Application Tier
Applied AD.I.4 (4712852)
Applied maintenance pack
cd $AU_TOP/patch/115/driver
adpatch options=nocopyportion,nogenerateportion

Issues faced while running Autopatch are noted in Upgrade log attached to this document below
Ran OATM to move 11.0.3 data to

8.0 Post Autopatch tasks
List of Patches to be applied are included in below file.

DBA Setup activities performed after all patches are applied is listed in the following document

9.0 Clean Up Unwanted Files/Directories

10.0. Health Check & Release the Instance for

Webinar: Release 12 Java Infrastructure

Solution Beacon - Thu, 2007-07-26 14:44
This is another in our Release 12 webinar series, and will be presented live, with the recorded replay available for registered attendees in the near future. This one hour webinar will be presented on 8/8 at 10:30am CDT, and registration is available here.TitleRelease 12 Java InfrastructureAbstractLearn about the new Java infrastructure underlying the Release 12 environment. The new technology

Webinar: Are you Ready for Fusion?

Solution Beacon - Thu, 2007-07-26 14:43
This is another in our Release 12 webinar series, and will be presented live, with the recorded replay available for registered attendees in the near future. This one hour webinar will be presented on August 15th at 10:30am CDT, and registration is available here.TitleAre you Ready for Fusion?AbstractA Practical Guide to What You Should Know For those of you concerned about Fusion's place in your

Webinar: Release 12 Subledger Accounting Engine

Solution Beacon - Thu, 2007-07-26 14:42
This is another in our Release 12 webinar series, and will be presented live, with the recorded replay available for registered attendees in the near future. This one hour webinar will be presented on August 15th at 1:30pm CDT, and registration is available here.TitleRelease 12 Subledger Accounting EngineAbstractWhat it is, What it does, and How to use it. Be in the know by attending this

Choice of desktop OS

Peter Khos - Wed, 2007-07-25 20:10
Back in April of this year, I bought a new Dell AMD desktop which comes with Vista installed. I gave that a twirl for a month and thought maybe I should go with Linux so that I can install Oracle on a "proper" OS and also Vista applications are tough to come by.I am finding that Linux (I chose Ubuntu) has similar problems where I was not able to use the software that comes with my various Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com1

Webinar: Release 12 Multi-Org Access Control (MOAC) – An Inside Look

Solution Beacon - Tue, 2007-07-24 15:55
This is another in our Release 12 webinar series, and will be presented live, with the recorded replay available for registered attendees in the near future. The webinar will be presented on 7/25 at 1:30pm CDT, and registration is available here.Title Release 12 Multi-Org Access Control (MOAC) – An Inside Look Abstract Join us as our Solution Architect focuses on Multi-Org Access Control (MOAC),

Webinar: Release 12 Procurement Part I – The Professional Buyer's Work Center

Solution Beacon - Tue, 2007-07-24 15:55
This is another in our Release 12 webinar series, and will be presented live, with the recorded replay available for registered attendees in the near future. The webinar will be presented on 7/25 at 10:30am CDT, and registration is available here.TitleWebinar: Release 12 Procurement Part I – The Professional Buyer's Work CenterAbstractExciting things are happening to the Procurement Suite in

Simple Tutorial for Publishing FSG Reports Using XML Publisher

Solution Beacon - Tue, 2007-07-24 15:50
This simple tutorial will show you how to create a custom FSG Report using XML Publisher.1. Log in to Oracle Applications and select the "XML Publisher Administrator" responsibility (your Applications Administrator will have to grant access to this responsibility).2. Navigate to the Templates page.3. Type FSG% in the Name field and click "Go to query" for the standard FSG template

Remote debugging Code Tester

Jornica - Tue, 2007-07-24 12:04

Quest Code Tester for Oracle (Code Tester) helps you with defining test cases, generating test harnesses and presenting the test results in a structured way. Code Tester does not provide any features to debug your code. If you run into a red light situation when a test case fails, you have discover where the error is located. This means checking inputs and outcomes in order to exclude incorrect setup and incorrect initialization code. And of course checking your code, recompiling your (test) code and login again.

If this does not result into a green light, it is time to debug your code with a development IDE. As a result you have to transfer your test code into your development IDE. Wouldn't it be nice if you could enter debug mode seamlessly: when executing your test case the execution stops at a breakpoint and you can debug your code. The answer is: yes, you can. With SQL Developer you can remote debug your code within a test run.

The linking pin between Code Tester and SQL Developer is the package sys.dbms_debug_jdwp where jdwp stands for Java Debug Wire Protocol. This protocols needs a debugger process i.e. SQL Developer and a debuggee process i.e. Code Tester. The debugger listens for requests from the debuggee i.e. PL/SQL package procedure calls to connect_tcp and disconnect.

Setting up the debuggee
In Code Tester you have to modify the initialization section of your test case. Add the following code
dbms_debug_jdwp.connect_tcp(host => '', port => 4000);
The first parameter is your IP address of the client where the Code Tester IDE runs (as seen from the database server you're connected to). Because I'm running Code Tester and Oracle XE on the same machine, I use the local host address The second parameter is the default port. An alternative for the hard coded IP address is SYS_CONTEXT ('USERENV', 'IP_ADDRESS').

After the test case is executed, switch off remote debugging. Add the following code to the cleanup section:

Setting up the debugger
In SQL Developer login with the same user as Code Tester and right click on your connection, a context menu appears and select the 'Remote Debug' option. A small window with the title 'Debugger - Listen for the JPDA' (Java Platform Debugger Architecture) appears, enter the address or host name where SQL Developer should listen to connect. Use the same IP address as in dbms_debug_jdwp.connect_tcp. Also check if the port is the same.

Before switching to Code Tester again set a breakpoint in your code (and compile your code for debug) otherwise the debugger will not stop at your breakpoint. At last but not at least make sure the user has the debug connect session privilege and the debug any procedure when debugging other users objects.

Debugger meets debuggee
It is time to run your test with Code Tester. SQL Developer will stop on your breakpoint. Note: while debugging your code Code Tester will not respond. After stepping through your code press the resume button in SQL Developer to return to Code Tester. As an example I modified the code of the normal usage test case of the function qctod#betwnstr (see for an explanation and the source code How Quest Code Tester for Oracle can help you get rid of bugs in your PL/SQL procedures). In the picture below SQL Developer hits the breakpoint at line 13. In the data tab you can see all variables and their values.

This example shows how to use remote debugging with Code Tester. Instead of using SQL Developer as debugger you can also use Toad for Oracle or Jdeveloper. And also every front end can be used as debuggee for remote debugging as long the calls to dbms_debug_jdwp can be implemented. Let's start debugging!

I found the following links usefully while writing this blog entry:

An apology to Kevin Closson

Peter Khos - Tue, 2007-07-24 00:00
In my last post, I mentioned that I was going to blog about the spat between Burleson and Lewis but decided not to BUT I left a comment on Kevin Closson's blog referring to the spat. This is definitely the wrong thing to do as if I was not willing to use my blog to comment on, why should I use someone's blog to comment.Kevin, my sincere apologies (please remove all comments I left pertaining to Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com1

11g whitepapers @ OTN

Pankaj Chandiramani - Mon, 2007-07-23 02:09

I have seen couple of good technical whitepapers at otn , below is the link to the same .
These cover the complete series for new features , security , HA etc


Categories: DBA Blogs

How we solved a (ORA-02049 Timeout: Distributed Transaction Waiting for Lock) on our Apps Customized module

Fadi Hasweh - Sun, 2007-07-22 05:00
We have a customized Point of Sale module that is integrated with our Apps standard CRM and financial modules; we faced a serious issue on this customized module that is when users are trying to sale through this module they receive an ORA-02049 Timeout: Distributed Transaction Waiting for Lock, which require them to keep trying until they make the sale. This error used to show on daily basis on the peak hours only but we could not tell what the cause of it, simple search of the error on metalink return note 1018919.102 that advices that we should increases the distributed_lock_timeout value in the INIT.ORA file the default value was 60 seconds so we increased it to 300 seconds even though we don’t have any distributed transactions on the system all the transactions were local. We restart the issue and the problem became worse because now the end users have to wait for 5 minutes (300 seconds) before they receive the error message (ORA-02049) and because of that we had to set the value back to 60 seconds.

After that we tried to trace the error using different event trace levels but with no luck we were not able to determine what is causing the error.

We thought that it’s a database bug and oracle advised us to upgrade the database from to we did that but still the issue is there.

After a month of investigation/tracing and snapshot of when the problem is happing we managed to find out what was causing the problem. It was a bitmap index that was built on the table we were trying to insert data on.

When an end user was trying to sale without committing his transaction for some reason and at the same time another end user tries to sale he will receive the error message and a lock on the table happened and the error pops-up.

We solved the issue by dropping the bitmap index and creating a normal b-tree index even though the column has only three distinct values.

Anydata and anytype in 9i

Adrian Billington - Sun, 2007-07-22 03:00
An introduction to generic types in Oracle 9i. October 2002 (updated July 2007)

Encapsulating bulk pl/sql exceptions

Adrian Billington - Sun, 2007-07-22 03:00
Using object features to encapsulate FORALL .. SAVE EXCEPTIONS error-handling. July 2007

The "Golden Rule" of People Management

Peter Khos - Sat, 2007-07-21 13:53
I was going to blog about the current spat between Jonathan Lewis and Don Burleson on the OTN forums over LGWR and LGWR_IO_SLAVES but then decided that it wasn't worth the web space that it occupies. So, I will blog about a non-technical subject, managing people.People management is a complex subject and there are numerous books published by folks smarter than I on the subject. Here's my take Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com3

Problems with CVS removes?

Robert Baillie - Fri, 2007-07-20 11:30
Accidently removed a file in CVS that you want to keep? Sounds like a stupid question, because when you know the answer to this problem it just seems blindingly obvious, but what if you've issued a 'remove' against a file in CVS and before you commit the remove you decided that you made a mistake and still want to keep it? I.E you issued (for example) > cvs remove -f sheep.php But not issued > cvs commit -m removed sheep.php I've heard work arounds such as: Edit the "entries" file in the relevant CVS directory in your workspace, removing the reference to the file. This makes the file appear unknown to CVS.Perform an update in that directory. This gets the repository version of the file and updates the "entries" file correctly All you actually need to do is re-add the file: > cvs add sheep.php U sheep.php cvs server: sheep.php, version 1.6, resurrected When used in this way, the add command will issue an update against the file and retrieve the repository version of...


Subscribe to Oracle FAQ aggregator