NULLs in subqueries.

I ran into a basic query today that perplexed me. I wanted to list all the values in one table (TABLE_A) that were not in another table (TABLE_B).

SELECT value
FROM table_a a
WHERE a.value NOT IN (SELECT DISTINCT b.value
FROM table_b b);


TABLE_A had the value '82' in it. TABLE_B did not have '82'. The query listed no rows. It should have listed '82' right? So thinking I was wrong and TABLE_B did have '82' in it, I tried:

SELECT value
FROM table_a a
WHERE a.value IN (SELECT DISTINCT b.value
FROM table_b b);


and it (more...)

Why can’t I add a unique index?

Adding unique indexes once data is already loaded into a table can prove to be a bit of a headache. The question of how to clean up duplicate keys is one that I have had posed to me many times and it seems somewhat simple. I guess anything is simple if you already know how to do it, so to that end, I thought I would write my solution.

This became an issue again recently because the UNIX admin where I work was creating a database to store system statistics. He has daily flat files that he wants to load (more...)

Generate Days in Month (PIPELINED Functions)

This cool example is not one I can take the credit for but since it is used pretty heavily in our organisation, I thought I would share it as it's not only pretty cool buy also demonstrates how useful Oracle Pipelined functions can be.

In essence a Pipeline table function (more...)

Missing AppsLogin.jsp…

Uncategorized
| Aug 24, 2009
I am still facing the same problem with my R12 upgrade.When running the post install checks using Rapidwiz , only the JSP and the Login page show errors.For jsp I see 'JSP not responding, waiting 15 seconds and retesting'and the Login Page shows 'RW-50016: Error. -{0} was not created. File= {1}'The strange thing is that all other checks are oke. Even the /OA_HTML/help check !So, the problem is

R12 upgrade

Uncategorized
| Aug 23, 2009
I Finally upgraded my 11.5.10.2 environment to R12.I followed the steps mentioned in the different upgrade guides. What do I have runing right now ?- Oracle eBS 12.0.6- Oracle database 10.2.0.4- Oracle tech stack (old ora directory) 10.1.2.3.0- Oracle tech stack (old iAS directory) 10.1.3.4.0So, having no problems during the upgrade proces, I finished starting al services. When trying to login my

PL/SQL: Create Dynamic PL/SQL Functions with the AnonymousFunction Data Type

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, November 9, 2008)

Spending some time with loosely typed languages like JavaScript and LISP has made me realize how powerful anonymous functions can be.  In my last big project using ADF Faces, I found it necessary to rewrite some the ADF Faces JavaScript functions on the fly (at runtime) in order to force a specific and non-native behavior. Since JavaScript is loosely typed (meaning, in a nutshell, I don’t need to explicitly specify a type for my functions or variables) and supports anonymous functions (functions defined and executed at runtime), (more...)

JavaScript: The Utlimate RegExp Email Address Format Validator

(Originally posted on the “old” Jason Bennett’s Developer Corner, Thursday, September 18, 2008)

I developed the following email address format validator for my current ADF Faces project.  The QA person keep failed my feeble attempt at a validator, so I decided to go big guns and create a validator that conforms to the standards for email addresses as set forth in this wiki entry:http://en.wikipedia.org/wiki/E-mail_address .  I created a single Regular Expression to handle all cases (unless someone can break it).  Here is the function:

(Make sure you put the regexp in one long string if you cut and (more...)

ADF Faces: Passing a Java Collection from a Custom ViewObjectImpl Class to a PL/SQL Function

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, August 17, 2008)

I recently had the challenge of creating several complex query search screens using ADF Faces.  These new search screens had to integrate seamlessly into our current application’s (not a J2EE application …) search screens (look the same, act the same, feel the same …).  One of the challenges that presented itself was to determine how to pass multiple search parameters with various operators (=, <, LIKE,SOUNDEX, etc …) and the values associated with them and map them to their associated columns in a dynamic where (more...)

The Oracle Report Bean

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, June 15, 2008)

The Oracle Report Bean is a cool little bit of code I developed this week that will let you to execute an Oracle Report from within your JEE or plain old Java application.  The bean allows you to configure all of the report execution parameters and contains methods to execute the report as a printed report or have the report streamed back to the client if the chosen format (PDF, RTF, HTML, XML, etc).  The code basically constructs the URL you need to access the (more...)

Oracle Application Server 10.1.3.5 Patchset Released

10.1.3.5 patchset has been released for Linux x86 and Windows 32 bit (64-bit Linux coming soon).
Patch is downloadable from Metalink:
Patchset 8626084
Description PLACEHOLDER BUG TO UPLOAD AS 10.1.3.5 PATCH-SET ON ARU

One of the major new features is for example as BPEL (more...)

AR.HZ_LOCATIONS_N15 is UNUSABLE

Uncategorized
| Aug 17, 2009
Problem;
*******
AR.HZ_LOCATIONS_N15 is UNUSABLE .

SQL> select index_name,owner,status from dba_indexes where status='UNUSABLE';

INDEX_NAME OWNER STATUS
------------------------------ ------------------------------ --------
HZ_LOCATIONS_N15 AR UNUSABLE


Solution
********
a) delete user_sdo_geom_metadata
where table_name='HZ_LOCATIONS'
and COLUMN_NAME='GEOMETRY';

b) drop index AR.HZ_LOCATIONS_N15 force;

c) cd $AR_TOP/patch/115/sql
sqlplus ar/ar @ARHGEOIS.sql
sqlplus apps/apps @ARHGEOID.sql

You'll be prompted to enter a value, choose AR
Enter the value for value 1 : AR


Verify the Object by running the below query as APPS.

select index_name,status from dba_indexes where table_name='HZ_LOCATIONS' and index_name='HZ_LOCATIONS_N15';

Rgds,
Madan

From Oracle to a Successful Vineyard


Last Month , I was there in Nasik ( One Small Town near Mumabi ). We roam around the city and visited many places in town. Our Driver suggested to visit Sual Vineyard. I am not very found of wine ,

But had very good time with friends in Napa Valley, CA, US, So thought let me experience Indian Vineyard.In Vineyard , we learned that owner of Sula Vineyard Mr. Rajeev is graduated in Economics and Industrial Engineering from Stanford and was working with Oracle in the Silicon Valley. Latter he left

Job and started Vineyard in India , (more...)

Knowing the trend of Deadlock occurrences from the Alert Log

Recently, my client deployed a new application and had this intermittent “Deadlock Storm” …

A trace file was sent and I was able to pinpoint the cause of the deadlock and the session that caused it.
The deadlock was a TX enqueue with mode of 4 (S – share) which could be verified by looking at the following lines of the Process State dump:

   last wait for 'enq: TX - row lock contention' blocking sess=0x 7000000cb239d60 seq=7849 wait_time=2929705 seconds since wait started=3
            name|mode=54580004, usn<<16 | slot=a0028, sequence=283f2

the “enqueue and lock mode” is explained as:
mode=54580004 (see above)
5458 (hex) (more...)

Computer Software articles to use on your website

Often, when getting a web site or online service started the 'shop front' can look bare until you build up content. Producing your own content can take time. One option is to use free articles from other sources. The Computer Software section at Article Alley has hundreds of software product (more...)

Load Balancing Strategies for SOA Infrastructures

When designing SOA infrastructures or Enterprise Architectures, most architects miss one component which may impact the overall system performance dramatically – the hardware load balancer.

Typically a hardware load balancer like F5 BigIP exists as a shared component for many applications and services to distribute load between multiple servers or (more...)

R12 (12.1.1) Installed on Solaris 10

Uncategorized
| Aug 11, 2009
After some initial struggle with server we were able to finish our 12.1.1 installation on Solaris 10.
I am trying to list mistakes we made or issues we encountered.
1. We started 12.1.1 on Solaris 8, which was mistake. As 11g database is not certified on Solaris 8.
2. After moving to Solaris 10, our team forget to bounce Server after setting Kernal parameters.
3. As we have one disk and it was damn slow, so took 17 hrs to complete.
4. Make sure you have atleast 250GB free space for Vision instance installation.
5. 47 (more...)

How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

I have been given with a challenging task to convert one of our critical production databases, which is of 1 TB (Terabyte) in size, to Oracle 10g RAC with ASM storage option. Even though, there are many methods and tools available to perform this activity, I have preferred to use the RCONFIG tool.

We prepared an input XML file required for RCONFIG tool, and run the RCONFIG utility as follows:

$ cd /oracle/ora102/db_1/assistants/rconfig/sampleXMLs
$ rconfig ConverToRAC.xml
When we start the RCONFIG tool to convert the database to RAC, the RCONFIG tool initially moves all the non-ASM database files to (more...)

The Humble PL/SQL Dot

Like many other languages, PL/SQL has its own "dot notation". If we assume that most people can intuit or easily look up things like the syntax for '''IF/THEN/ELSIF''', that means that first-timer users might quickly run into dots and want to understand their significance.The authoritative docs on the dots is in the Oracle Database 11g PL/SQL Language Reference, in particular Appendix B, How PL/

Detecting Corrupt Data Blocks

Or more accurately, how not to detect corrupt data blocks.

This thread on Oracle-L is regarding lost writes on a database.

One suggestion was made to use the exp utility to export the database, thereby determining if there are corrupt blocks in the database due to disk failure. I didn't give it much thought at first, but fellow Oak Table member Mark Farnham got me thinking about it.

Using exp to detect corrupt blocks, or rather, the absence of corrupt blocks may work, but then again, it may not. It is entirely possible to do a  full table scan on (more...)

Delay in Blogging

I have been getting mails inquiring if I would not be blogging anymore ? I must apologise to the readers of this blog for not being able to post new blog post. As most of you are aware I have just joined my new job, so i am still in the (more...)