• August 18, 2005 18 Aug'05

    NULL can be your friend

    I often see, but dislike, SQL that uses NOT or OR. Can nulls be two tics with no space between them and eliminate the OR?  Continue Reading

  • August 17, 2005 17 Aug'05

    Three consecutive months in fiscal year

    I have a conveyance_claim table with claim date and type, one record per month. I have to check in financial year (April to March) if three continuous months have claims of the same type. How can I find it?  Continue Reading

  • August 17, 2005 17 Aug'05

    Undocumented secrets for super-sizing your PGA

    Different tasks in the Oracle database require different RAM areas, and the trick has been to allow "enough" PGA RAM for sorting and hash joins without having any high-resource task "hog" all of the PGA, to the exclusion of other users. Guru Don ...  Continue Reading

  • August 16, 2005 16 Aug'05

    Calculating database efficiency

    How can I calculate database efficiency?  Continue Reading

  • August 16, 2005 16 Aug'05


    What is the correct circumstance to store a numeric string as VARCHAR2 vs. NUMBER? The response I have received before is to store all numeric strings as VARCHAR2 unless the intention is to manipulate the field mathematically; but no reason has ...  Continue Reading

  • August 16, 2005 16 Aug'05

    Finding Oracle instances

    I want to find out how many Oracle instances (SIDs) are created on any machine. If I use the command "Select * from v$database" I get only the current instance name in the output.  Continue Reading

  • August 16, 2005 16 Aug'05

    Checking for single quotes in a variable

    I'm trying to insert records in Oracle and values are in a variable. How do I check to see if the variable has a single quote?  Continue Reading

  • August 16, 2005 16 Aug'05

    Errors during import of dump file

    I created a User Export dump of an Oracle 9i database on Windows 2003 Server, and the export terminated successfully. While importing this dump in Oracle 9i on another system with Windows 2000 Advanced Server, the import is getting terminated with ...  Continue Reading

  • August 15, 2005 15 Aug'05

    Concatenating XML fragments

    Using XMLElement and XMLForest I have created several XML "strings." In a final XMLElement select statement with XMLForest I want to concat the earlier strings within the output of the final statement. Can concat be used within XMLElement or ...  Continue Reading

  • August 15, 2005 15 Aug'05

    Oracle and SOAP

    I am using Oracle9i and Forms6i and want to retrieve records from another Oracle database which is only accessible through a web service using SOAP/XML objects. Help me with the XML code to retrieve a particular record from the target table.  Continue Reading

  • August 12, 2005 12 Aug'05

    Ranking within a group

    How can I get the rank of a certain column without using the rank() function, just using a simple SQL query?  Continue Reading

  • August 12, 2005 12 Aug'05

    Another FULL OUTER JOIN example

    I am trying to create a query which should return four columns. I would be glad to even have just three columns, since the total can be computed in the display table. So far I have a query which returns just two rows under the same column. Am stuck ...  Continue Reading

  • August 12, 2005 12 Aug'05

    Latest version of each jobcode

    I am trying to retrieve information from a single table. The table contains job descriptions, about 500,000 rows. Each description has multiple rows. My subqueries work separately, but not together. Do you have any suggestions?  Continue Reading