(Sanjay Ray) This tip was written for use with Oracle8i. This code tip demonstrates a technique to track changes occurring in the underlying database tables following a sequence of events in the front-end application. That is, the user keys in data through the application, or runs a process,and the
Tag: Oracle
Oracle SQL and index internals: EXPLAIN and understand
(Kimberly Floss) As someone who has worked in the database trenches for more than 15 years now, I have a terrible confession to make: I have never been really good at reading EXPLAIN plan output. Oh, I can do the basics like recognize table scans, spot Cartesian joins, and zero in on unnecessary sor
Introduction to Oracle 10g’s New SQL Tuning Advisor
(James Koopmann) Come along and see how Oracle has helped us in alleviating the pain associated with tuning SQL. Gone are the days of staring at countless structures and statistics just attempting to tune a SQL statement. Oracle has once again given us a great tool that will assist us with manua
Create a Robust Document Management System with Oracle Text
(Brian Carr) If you need a way to search through various documents with differing formats (PDF, Word documents, PowerPoint presentations, Excel spreadsheets)—including Web content—use Oracle Text. Oracle Text has a powerful text-search engine that allows users to search and manage documents and URLs
Inserting Custom Messages in Oracle Alert/Trace files
(Amar Kumar Padhi) A colleague of mine showed me a link on the OTN site regarding writing messages to alert log and trace files. I probed further and came across a couple of routines in the DBMS_SYSTEM package that allows us to insert our own customized messages in Alert log and/or trace files. I tr
Perform Without Waiting
(Arup Nanda) John, the DBA at Acme Bank, is on the phone with an irate user, Bill, who complains that his database session is hanging, a complaint not unfamiliar to most DBAs. What can John do to address Bill’s complaint? Acme Bank’s database is Oracle Database 10g, so John has many options. Aut
Oracle Tip: Serialize data with ANYDATA columns
(Scott Stephens) Oracle version 9 introduced interesting new data types, which allow developers to declare a variable that can contain any type of data. The data types are ANYDATA for a single item, ANYDATASET for a TABLE or VARRAY of data, and ANYTYPE, which describes the type of data stored in ANY
Use an Oracle API to Extract and Store Database Objects’ DDL
(Boris Milrud) There are times when database data manipulation requires extracting DDL (Data Definition Language) commands for various objects, such as database tables, indexes, constraints, triggers, etc. Recently I was charged with the task of writing a set of database packages to perform a hi
Oracle’s Wrap Utility
(Steve Callan) One thing that Oracle and UPS have in common is that they both deal with wrapped packages. Why bother with wrapping packages? One obvious reason is to hide the contents from prying eyes. You can wrap a real package with nondescript wrapping paper or use transparent cellophane. Thi
Bulk Binds and Collects in PL/SQL – Part 2
(Amar Kumar Padhi) The SQL engine will bulk bind all the values present for a column in a table before returning the collection to the PL/SQL engine. If the amount of data being retrieved is immense, this fact itself, will result in degradation of performance. An alternative is to retrieve one
