|
April, 2006
In
This Issue
|
|
All articles have been reprinted with the written consent of their respective authors. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Tapping into Oracle's Metadata - Part I by James F. Koopmann Having grown up with Oracle since version 6, and I am sure many of you out there are in my same boat, it has become second nature to rattle off the various DBA_ views that make up an objects metadata. A subset being DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS, DBA_IND_COLUMNS, DBA_CONSTRAINTS, and DBA_CONS_COLUMNS. The ability to rattle of these objects as well as the columns that make up these views stems from the fact that, in the olden days, there were not any modeling tools that we were able to develop structures with, let alone be able to reverse engineer into. Thus we needed to be able to compare database structures attribute by attribute. In order to recreate a database schema or object we needed to devise SQL that would extract some form of DDL or keep tight control through some form of version control for our database structures if we ever wanted to re-create the database from scratch. Yes, export and import were around but very difficult to work with for individual objects at times. Oracle has what I like to think is a good solution to the issues of DDL extraction, storage, and recreation of objects through their metadata API namely DBMS_METADATA. This mechanism allows us to focus on maintaining our database. Click here for the article.
Oracle Materialized Views and Partitioning Materialized views are an Oracle Silver Bullet when
pre-joining tables together for super-fast response time.
|
|
Application Context - Local Contexts You might have heard about global variables – variables that are set once during the session and are available to all processes in the same session. The simplest example of which can be a package variable as shown below: create or replace package pkg_a as
gv_deptno number;
end;
When you want to assign a value to this variable, you can issue: exec pkg_a.gv_deptno := 1 Later in the same session, you can retrieve the value of the variable, and it will be 1. declare
l_somevar number(1);
begin
l_somevar := pkg_a.gv_deptno;
end;
This type of global variables are very useful in cases where there is a disconnect between processes in the same session, e.g. a database logon trigger might populate a variable that will be accessed by all SQL statements subsequently executed in the session. Since the logon trigger executes when the session is established, there is no other way a value can be passed from it to a SQL statement further in the session. Global variables are the vehicles. Click here for the topic.
|
|
Oracle and SQL Server Data Interoperability Issues - Part 1 Most of us work with and specialize in only one RDBMS system, such as MySQL, Oracle, or IBM DB2. However, increasingly, we find ourselves dealing with a heterogynous database environment and are asked to solve data interoperability problems. Although the major RDBMS vendors try to follow the relational data model principals, their implementation of them can be quite different. In addition, although most of the major RDBMS vendors also comply with SQL-92 standard, they all have their own proprietary extension to that standard. For example, both Oracle's PL/SQL and Microsoft's T-SQL added tons of extra extensions to ANSI SQL. In a series of articles, I want to address data interoperability issues between SQL Server and Oracle. I will talk about data types used in these 2 different RDBMS. In particular, I will talk about Oracle's timestamp and SQL Server's datetime, because their incompatibility can cause a lot of problems in DTS, linked server, and distributed queries. Click here for part 1 of this article.
In A Nutshell
Interested in learning more tips and techniques for SQL Server? "In A Nutshell"
is what you are looking for. Kevin Kline,
author of O'Reilly's "SQL in a Nutshell" and "Transact-SQL Programming" and President of The Professional Association for SQL Server,
offers tips, techniques and much more. Updated numerous times a week,
there is always valuable material to be had!
Click
here
to see what Kevin is up to in the SQL Server world.
|
| Beginning PHP and MySQL 5: From Novice to Pro, Second Edition "Beginning PHP and MySQL 5: From Novice to Professional, Second Edition" (Apress, 2006. 904pp.) offers a comprehensive introduction and reference guide to the PHP scripting language and MySQL database. Updated to reflect changes to the PHP language since the first edition, new material regarding PEAR, the new date and time extension, PDO, the SQLite extension, and web services has been added. Additionally, the book has been updated for MySQL 5, and covers triggers, stored procedures, transactions, security, the new mysqli extension, and much more. More information about the book can be found at http://www.amazon.com/gp/product/1590595521/. An e-book version is also available through the Apress website (http://www.apress.com/ ).
Click here to see a sample chapter
on functions.
|
|
Before a project actually starts, you must spend an appropriate amount of time defining the work and building a workplan (schedule). For a small project, it may only take a few hours to complete this upfront work. A medium sized project might take 20 - 40 to define and plan. A large project will require even more time – maybe a couple hundred hours. Of course, some projects are much larger than others. As a project becomes even larger, the time it takes in the upfront definition might reach into the hundreds or even thousands of hours. Defining the work for very large projects takes enough time that it should be structured as a project itself. This is the purpose of defining a separate “Discovery Project”.
Click
here for more information about
discovery projects.
|
| Webcasts
|
|
|
|
Interactive Crossword
Puzzle: "Coffee"
|
|
We love getting white papers, tips, articles, and code examples/archives from our readers around the world. Send your submission to newsletter@quest-pipelines.com. If your article is published, it will be mailed to over 28,000 subscribers. Our mission at Quest Software - RevealNet Labs is to anticipate the daily responsibilities and challenges faced by database professionals. Our products help thousands of people solve problems and implement solutions every day. This newsletter is designed to help facilitate the sharing of information among database professionals. About the Newsletter This newsletter is distributed to Quest Software - RevealNet Labs customers, prospects and friends who have subscribed to it from our website. If you would like to unsubscribe, please visit http://qlist01.quest.com/UnsubMailingList/ Subscribe a Friend! Do you know someone who would like to receive the Pipeline Newsletter? If so, please enter their email address in the box below and click SUBMIT.
Past Issues of the Pipeline Newsletter | ||||||||||||||||||||||||||||||||||||||||||||||||||
