Oracle DB News

Oracle Magazine?

noreply@blogger.com (LewisC) — Thu, 17 Jul 2008 23:57:00 +0000

I ran across a new magazine, Oracle Journal by sys-con. It must be very new because while browsing around the site, I was unable to actually find any Oracle content. It has three news stories (paragraph long news blurbs actually) that are repeated at least 4 times on the page that I can see.

It is an interesting concept. I like Oracle magazine but it is published by Oracle. There's a certain conflict as far as hard hitting news goes. But for a magazine to work, it actually needs content, not just ads. I wonder if they need a writer? I could do some articles for them. Hmmm. Worth thinking about.

LewisC

Technorati : magazine, oracle, sys-con

Oracle RAC and Grid Q&A With The Experts

noreply@blogger.com (LewisC) — Wed, 28 May 2008 18:42:00 +0000

LewisC's An Expert's Guide To Oracle Technology

Q&A about RAC and Grid with the RAC Experts

I recently got the opportunity to sit down and talk to two RAC gurus and learn what RAC is and how it relates to Oracle's Grid technology. Listen to the discussion with Philip Newland and Scott Jesse, both from Oracle Corp. Philip is a technical manager with Oracle's RAC Pack team and Scott is an Oracle Support Services Manager for the RAC Assurance Team. Scott is also the co-author of two Oracle Press books, "Oracle9i for Windows 2000 Tips & Techniques" and "Oracle Database 10g High Availability with RAC, Flashback & DataGuard".

There were plenty of questions asked and answered. Here are five important questions. Listen to the podcast to hear the rest.

What does Oracle mean by Grid?

An Oracle Grid allows you to add computing capacity, CPUs or storage, on demand as needed without pre-purchasing monolithic hardware. On an Oracle Grid, you can add capacity one cheap PC at a time.

There is no product for sale called Oracle Grid. You can't just walk into a store and buy a grid. Oracle Grid is a technology composed of several innovative Oracle products.

What components make up an Oracle Grid?

RAC

Oracle Real Application Clusters (RAC) allows Oracle customers to add database capacity (by adding servers) to an existing cluster. RAC allows a database to be spread across multiple servers. You can dynamically add and remove nodes (servers) as required.

RAC allows an application to transparently scale, add performance and be available 24/7. RAC can be very affordable to scale because it allows you to add cheap servers when you need them.

ASM

Oracle Automatic Storage Management (ASM) eases storage management by abstracting file systems to where DBAs need them. ASM also allows consolidation of storage so that applications that need storage have it available when they need it.

Clusterware

Oracle Clusterware provides the intelligence for a cluster. A cluster is a series of servers acting as a single entity. Clusterware provides the management and monitoring of a cluster.

Oracle Clusterware is not limited to providing scalability and high availability for Oracle Databases. With Oracle Clusterware, you can provide these services for third-party databases, application servers and pretty much any other kind of application.

Cluster File System

Oracle Cluster File System (OCFS & ACFS2) allows an Oracle database cluster to share disk across many servers. OCFS ensures that Oracle sees a consistent image of the disks on each server.

Does RAC work with Oracle Standard Edition?

Oracle Standard Edition, the edition of Oracle for the SMB market, comes with a license for a 4 node RAC cluster. A standard edition license allows up to 4 CPU sockets in a cluster. Those 4 sockets may be in a single server or in two, three or four servers. As long as your cluster does not exceed 4 sockets, RAC is included as part of your Standard Edition License.

Standard Edition does not limit the size of your database in anyway so as long as the processing power for a four socket configuration suits your needs, this can be a great way to save. If you need to scale to a larger cluster, Standard Edition can easily be upgraded to Enterprise Edition allowing you up to 1000 nodes.

Standard Edition RAC configurations are required to use ASM. Because ASM saves time and effort, this is not really a limitation but more of a method for Oracle to help you ensure your success.

How do I monitor my Grid?

Oracle provides both DB Control and Grid Control for monitoring and managing your databases. DB control is a web based tool that allows you to manage one database at a time. Oracle Grid Control allows you to manage entire grids and is an add on package. Grid Control can simplify all of your grid management tasks.

Can I mix and match hardware?

Yes. Oracle expects that vendors are improving hardware constantly and that customers will need to upgrade hardware over time. Rather than force customers to maintain a single vendor or configuration, Oracle RAC will work across various hardware configurations. While one server may be a single CPU and 4GB of RAM, another server in the same cluster can be 4 CPUs (with dual cores) and 16GB of RAM.

The only requirement is that the Operating Systems must match: Windows to Windows, Linux to Linux, 32 bit to 32 bit, etc.

Summary

Grid Computing - Oracle allows you to scale by tying together cheap hardware in a cluster allowing multiple servers to act as one. Oracle provides the software that enables database grids (via RAC) as well as storage grids (via ASM and OCFS). Oracle's Grid allows a business to start as large or small as they need, spending money on hardware and other system resources only when it is actually needed. RAC and Grid is transparent to the application allowing painless and immediate scaling when required.

I think this is one of my better podcasts. Check it out.

LewisC

Del.icio.us : grid, oracle, rac, vldb

When to use Triggers (and when not to!)

noreply@blogger.com (LewisC) — Fri, 23 May 2008 12:52:00 +0000

LewisC's An Expert's Guide To Oracle Technology

By request, this entry will discuss some issues surrounding DML triggers. Triggers are a nifty feature. When you need them, they are very useful. They can also make maintenance and debugging an absolute nightmare.

API vs Trigger Approaches

I am an API style programmer. I do not mean that I put all of my logic in INSERT/UPDATE/DELETE procedures for each table, although that is not a bad idea. What I mean is that I tend to write functional packages and those packages deal with DML.

As an example, let's say I have an HR system. I have a People table, a Job table, a Pay-Scale table and an Assignment table. Jobs have pay scales and the Assignment table associates a person to a job. All of the tables sequences for primary keys and all tables use database defined referential integrity.

I would have an API for Jobs. That API would deal manipulating Jobs and Pay-Scales. The insert code would select the PK sequences.

I would have an API for adding people. An Employee MUST have an assignment (that is kind of what defines an employee), so the Assignments table is also manipulated in the People API. A person can change Jobs (get a new assignment), be separated (remove the assignment), etc. If for some bizarre reason I wanted to have the Job name in the assignment table in addition to the Job PK (I don't recommend this, it's just an example), I would do that in the API.

So, what how does this relate to this blog topic? Not a single trigger. If there is an error anywhere, I have a couple of APIs to look at.

What would be a trigger based approach? A trigger to populate the PKs. A trigger to populate the denormalized Job name. Possibly even triggers to deal with integrity and referential constraints (but I sure hope not!).

In the trigger based approach, you now have code in many places, any of which can (and at some point will) break. In addition to the maintenance issue, there is also a performance impact with row-level trigger processing.

I would choose the former, API approach over the trigger based approach every time. I have inherited trigger based applications and suffered through every one of them. When I have time, I convert those to APIs as soon as I can.

Referential Integrity

Do not use triggers instead of foreign keys. I have seen trigger based foreign keys and the reason was that the "model was too complex for database referential integrity". In every case where that statement has been made (at least that I have seen), it was due to a poor data model and not a limitation in the database.

Distributed referential integrity is a different beast. I have seen one case where a trigger was used to maintain integrity with data in a different database. I have to admit that in this case, I don't see any alternatives. The trigger incurred some overhead in the application but it was an acceptable amount. If the remote database was down, the trigger failed but that was an intended effect.

Auditing

I have used Triggers for auditing database changes in the past. I even wrote a code generator that generated the triggers for me by looking at the dictionary and dynamically getting columns and such. At one time, I think that was the best approach. Today, Fine Grained Auditing offers an incredibly robust auditing mechanism. It works with SELECT statements and it also allows you to capture other information that is impossible or difficult to get with any other method.

Replication and Data Feeds

Another use of triggers has traditionally been to replicate all or subsets of data to different schemas or databases. Sometimes for application usage and other to aggregate data in a warehouse. Almost all databases now offer some kind of a database link and any database worth using offers some kind of replication.

In many cases, the database will create triggers behind the scenes but at least the database is then responsible for maintaining those triggers. Oracle offers Oracle Streams which is very robust and offers scheduled or real-time replication and Change Data Capture.

Multi-database Support

If you truly need to maintain your application on multiple databases, triggers can be useful. I don't mean that you use a bunch of Oracle features and hope to one day migrate but you actually, actively maintain your code base to run on multiple platforms.

In this case, triggers can keep you from changing your code as often. Take the PK example of using sequences. Some databases use an auto-increment data type, other databases use sequences but the select syntax is different from Oracle's syntax. In this case, you can encapsulate the logic in a trigger and only change that code when required. In the case of migrating to a database with auto-increment, you won't need the trigger at all.

Also, when auditing your application, you may have specific needs that individual databases might not support. In that case, you might want to use triggers to audit. You would want to have a common audit record across database types so it would make sense to use the triggers even on databases that support advanced auditing.

Third-party Applications

If you need to alter data or flow in a third-party application, your only option might be a trigger. In that case, you really have no choice. Before you choose a trigger, think about the other available options. In many cases, Fine Grained Auditing or streams can achieve the same thing and will be a lot less intrusive to the application.

I hope this answers some of the questions about using triggers. Did I miss any cases where it really makes sense to use a trigger? Have you seen triggers used in other ways (good example or bad)?

LewisC

Learn Oracle: Triggers

noreply@blogger.com (LewisC) — Tue, 13 May 2008 11:15:00 +0000

LewisC's An Expert's Guide To Oracle Technology

Today I will be writing about triggers. One of the questions I get fairly often is "what is the difference between a function, a procedure and a trigger?" I already wrote about functions and procedures in Learn Oracle: Procedures and Functions. You should probably read that one before you read this post.

What is a trigger?

A trigger is a special case of stored procedure that is fired during an event rather than being explicitly executed. A function or a procedure can be called from a command line or from within a different calling program. A trigger is called automatically when an event is fired.

In Oracle, a trigger can have the procedural code embedded in the body of the trigger, you can call out to another existing function or procedure, or a combination of both. The code in a trigger is generally the same as any procedure or function. You are allowed to create Java triggers but I see this rarely in live systems.

You cannot execute transaction control statements with in a trigger. That means no commits or rollbacks. If you need to rollback a transaction due to logic in a trigger, raise an exception and allow the calling program to take the appropriate action. As for commits, the calling application should always commit or rollback as needed.

Technically, there is a way to execute transaction control within a trigger but that can be abused, and really damage your data integrity, if done poorly. I will leave that topic for a discussion on advanced PL/SQL.

What are trigger events?

Various databases support different triggering events. Most all enterprise class databases support DML triggers (excluding SELECT). That means that the database will optionally fire anytime a table has a row updated, deleted or inserted. Many databases, including Oracle, also support DDL triggers. These fire when you create, alter or drop an object.

Oracle supports many events in addition to DML events. Oracle supports system events such as the database starting or stopping or when there is a server error. Oracle also supports user events such as a user logging on or off.

For a new user or developer, it is important that you understand DML triggers as you will find many applications that are Dependant on triggers. DDL, user and system triggers tend to have more advanced uses although you will see examples of those for various security related processing (such as VPD).

For the purpose of this blog entry, I'll stick to DML triggers.

Trigger Syntax

There are three main types of DML triggers: before, after and instead of. A before or after trigger can be a statement level trigger or a row level trigger.

Before

Obviously, a before statement trigger will fire before a statement is executed. A before row level trigger fires before each row that is affected by a statement.

After

An after statement trigger will fire after a statement is fired. A before row level trigger fires before each row that is affected by a statement.

Statement vs Row

The difference between a statement level trigger and a row level trigger is very easy to understand. If you perform an update that will affect 10 rows, a statement level trigger will fire once and a row level trigger will fire 10 times.

A statement level trigger does not have access to individual column values. A row level trigger does have access to column values. A before row trigger can change new column values.

Instead Of

An instead of trigger is built on a view. Many views are updateable with an instead of trigger. Many more views are not updateable for various reasons. With an instead of trigger, you can update any of these views. As the name implies, an instead of trigger is executed instead of Oracle trying to execute the command directly against the view.

Trigger Syntax

The syntax of a trigger varies database by database but for Oracle and PL/SQL (as well as for EnterpriseDB and SPL), the basic syntax of a DML trigger is:


CREATE OR REPLACE TRIGGER trigger_name
  BEFORE|AFTER|INSTEAD OF       
  INSERT|DELETE|UPDATE ON table_name 
         [OF column_names]
  [REFERENCING [NEW AS new_cols] [OLD AS old_cols]]
  [FOR EACH ROW [WHEN (where_condition)]]
  [DECLARE]
  BEGIN
  EXCEPTION
  END;

Let's look at the command row by row. Anything within square brackets ([]) is an optional clause. Any items delimited with a pipe (|) is part of a list.

Row 1: The create or replace should seem familiar if you are familiar with procedures and functions. Naming of a trigger follows standard naming conventions and rules.

Row 2: The BEFORE|AFTER|INSTEAD OF clause tells the database what type of trigger it will be.

Row 3: This line allows you define if the triggering event will be on UPDATE, DELETE, INSERT, a combination or all of these. The "ON table_name" identifies the table for which the trigger will be created.

Row 4: An optional clause is the "OF column_names". This statement only affects an UPDATE. If you only want to fire an update trigger when certain columns are modified, you may include this optional statement and list the columns that should be monitored.

Row 5: In a row level trigger, you have the values of the columns as they existed before the command and the new values as a result of the command. For an insert, there are no old values and for a delete, there are no new values. In an update, you have both old and new.

The REFERENCING clause allows you to name the old row and the new row. By default, they are named :OLD and :NEW. You refer to them as :OLD.column_name and :NEW.column_name. You can query them in both before and after row triggers, like:

IF :old.gl_id = :new.gl_id THEN...

In a before row trigger, you can even set the values of :new columns, i.e.

:new.gl_id := sequence.nextval();

Statement level triggers cannot access row level column data.

Row 6: A DML trigger, by default, is a statement level trigger. When you use "FOR EACH ROW", you turn it into a row level trigger.

The WHEN clause allows you to introduce additional matching criteria that Oracle will test before executing a trigger. It is standard where clause type criteria,

FOR EACH ROW WHEN (old.order_date BETWEEN sysdate - 10 and sysdate)

Row 7 and on are standard PL/SQL syntax. Think of them as an anonymous block attached to the trigger name and criteria.

Example

Here is a very simple trigger using the Employees table from Oracle XE.


SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

Now, I'll create the trigger on employees. The trigger will only fire on insert or update (not delete), update will only fire when email or hire_date is modified and the trigger body will only fire when the :old.salary is greater than 7500. Because it will only fire when an :old value is set, it will never fire for an insert, even though I am defining it for insert.


SQL> create or replace trigger emp_email
  2  before insert or update of email, hire_date
  3  on employees
  4  for each row when (old.salary > 7500)
  5  begin
  6    dbms_output.put_line('Trigger executing');
  7  end;
  8  /

  Trigger created.

Next, I'll fire two inserts to add a couple of records. Only one of the records will have a salary > 7500. After the inserts, I will update each record. Only the record with a salary > 7500 will display the trigger message.


  SQL> set serveroutput on

  SQL> insert into employees
  2  (employee_id, last_name, email,
  3   hire_date, job_id, salary)
  4  values
  5  (101010, 'Smith', 'smith@barney.com',
  6   sysdate, 'PR_REP', 5000);

   1 row created.

  SQL> insert into employees
  2 (employee_id, last_name, email,
   3 hire_date, job_id, salary)
   4 values
   5 (101011, 'Jones', 'jones@barney.com',
   6 sysdate, 'PR_REP', 8000);

   1 row created.

   SQL> commit;

   Commit complete.

   SQL> update employees set
   2 email = 'under7500@noexec.com'
   3 where employee_id = 101010;

   1 row updated.

   SQL> update employees set
   2 email = 'over7500@willexec.com'
   3 where employee_id = 101011;
   Trigger executing

   1 row updated.

   SQL> commit;

   Commit complete.

   SQL>

That's pretty much it for DML triggers.

LewisC

Oracle Connections to Non-Oracle Databases

noreply@blogger.com (LewisC) — Wed, 23 Apr 2008 13:50:00 +0000

LewisC's An Expert's Guide To Oracle Technology

Have you ever needed to bring data from a non-Oracle databases into your Oracle database? This is very common in a warehouse and is even somewhat common in ERP solutions. For a price, Oracle offers Transparent Gateways to many popular databases. What do you do if you need to load some data from a data source but there is no Transparent Gateway (or if you have no budget for one)? If that data source happens to support ODBC, you're in luck and it won't cost you anything above and beyond what you are already paying for your database.

The solution is called Heterogeneous Services Generic Connectivity. I usually just call it HS links. Basically, you configure an ODBC data source and then create a database link against that source. You can run DML against the source just as you would against an Oracle database.

ODBC is available for most flavors of Unix, Linux and, of course, windows. I believe there is even an ODBC package for Mac but I am not a mac user so I can't say that it works. Any features you want to use must be supported by your ODBC driver.

If you are on a 64 bit Windows OS and you are connecting to a 32 bit database, you MUST use the 32 bit ODBC admin tool (c:\windows\syswow64\odbcad32.exe) versus the 64 bit ODBC admin tool (c:\windows\system32\odbcad32.exe). You can get more details about this on my Postgres blog, 32 Bit ODBC Drivers in Vista 64.

Here is the short story about how to get this installed and working. It's really fairly easy. These instructions are for 10g. They probably work in other versions but I have not specifically ran these instructions through those versions.

NOTE: This configuration is done on the database server, not your client. You must have access to an account on your database server that will let you work with system and oracle configuration files..

Configure ODBC

First, get your ODBC drivers for the database you want to connect to. I will use EnterpriseDB since I just happen to have a database loaded and running already. ;-)

If you have installed Advanced Server, you should already have the odbc drivers installed. I know Ingres and SQL Server also automatically install drivers. MySQL provides a separate download. There are so many different tools available that I can't give you detailed instructions on finding and installing your drivers.

Ok, now that you have ODBC installed, create an ODBC data connection. This is really very simple. In Windows, make sure that you choose to create a System DSN. I will call my DSN "EnterpriseDB". Test your DSN to make sure your information is correct. Windows includes a test button on the form where you create the DSN. Make sure to save the DSN.

That's it as far as playing with ODBC goes but remember the DSN name you just created. You'll use it in several places later on.

Configure Connectivity Agent

The next step is to identify the data source to Oracle. You do this by create an initialization file just like you would for an Oracle database. Well, not just like it. But, you get the point.

You should have an HS directory in your Oracle home directory. In HS\ADMIN you should find an INITHSODBC.ora file. Copy this to a init<dsnname>. Mine will be INITEnterprisedbDB.ora. In Windows, case is not significant. In general, I lowercase all file names so my file is really initenterprisedb.ora.

Edit this file. You'll need to change the values of two parameters. You must supply the DSN we created above and a log level. For now I am turning logging off. My file looks like this:

HS_FDS_CONNECT_INFO = EnterpriseDB HS_FDS_TRACE_LEVEL = off

Save the file, making sure to leave it in the HS\ADMIN directory.

Configure The Listener

At the bottom of your listener.ora file (in Oracle home/network/admin) you need to add a new service under the sid_list_listener:


(SID_DESC = (SID_NAME=EnterpriseDB)
   (ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server)
   (PROGRAM=hsodbc)
)

The HSODBC program has under gone name changes. If you are not using 10g, make sure you find the current name to use in the documentation. HSODBC may work in 11g bit I do know the program has been updated and has a new name. The SID_NAME parameter is the ODBC DSN name.

Configure TNSNAMES.ora

You will need to enter a new entry in your TNSNAMES.ora file (in Oracle home/network/admin).

It seems counter intuitive but here you are actually configuring the server where the listener is running not where the remote database is running. Notice the PORT is 1521. That is the port (and the host) where the Oracle listener is listening for connections. ODBC will ensure that your connections make it to the right remote server.


EDB = 
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)
             (HOST=localhost)
             (PORT=1521)
    )
    (CONNECT_DATA = (SERVICE_NAME=EnterpriseDB)
    )
    (HS = OK)
  )

SERVICE_NAME is, again, the ODBC DSN.

Create a Database Link


  create database link edb1 connect to "enterprisedb" identified by "edb" using 'edb';

Notice here that I have the name and password quoted. That's because the remote server, EDB AS, is case sensitive. If the user name and password are not quoted, Oracle will send them in upper case and you'll get an error.

I'm calling my link edb1 but you can call it anything that doesn't violate Oracle naming rules.

In the USING section, you need to use the name defined in your tnsnames.ora.

Test the Link


SQL> create database link edb1 connect to "enterprisedb" 
     identified by "edb" using 'edb';

Database link created.


SQL> select * from dual@edb1;


D
-
X

SQL> select "lanname" from "pg_language"@edb1 where rownum < 5;


lanname
-----------------------------------
internal
c
sql
plpgsql


SQL>

Notice here that, like user name and password, I have the column and table name quoted. Columns and tables in EDB and Postgres are stored in lower case unless quoted during creation.

There are many parameters you can use to tweak HS. For something like an ETL process, you would want to tweak it as much as possible. For an occasional query, plain vanilla is probably good enough.

Take care,

LewisC

Technorati : database, dblink, gateway, heterogenous, links, oracle

My Book Has Been Printed

noreply@blogger.com (LewisC) — Fri, 04 Jan 2008 01:14:00 +0000

Well, it's taken over a year but it has finally arrived. I started writing the book back in Aug 2006. I finished in late Jan 2007 and the technical editor finished his work in March. Now, In Jan 2008, EnterpriseDB: The Definitive Reference is available. I'm glad I didn't wait for the movie. ;-)

It's kind of ironic. Just this morning, I posted that I am working on my second book. My wife called me at work and told me two boxes of books had arrived. 2008 is turning out to be a good year. I'll have some additional news in the near future.

Anyway, the book looks good. I've already found a few typos and grammar errors just skimming around. I like the font and the pages look really crisp. All in all, I have to say I am very happy that the book is out.

Check out some photos:

I think I want to give a couple away. Maybe some kind of contest for the blog readers. Any ideas of something fair? I am willing to pay shipping for a couple but that would only be here in the US. Overseas shipping gets expensive.

LewisC

oracle enterprisedb

Hotsos Profiler Extension for SQL Developer

noreply@blogger.com (LewisC) — Sun, 21 Oct 2007 22:59:00 +0000

Hotsos has released its Profiler extension for SQL Developer. The extension is free but you must have a license for the profiler for the extension to do you any good.

The profiler works almost like the Explain Plan tool in SQL Developer. Enter the text you want to profile, press a key and then view the output.

You do need access to the trace files that are generated. The page linked above had instructions on how to enable access and what permissions you need.

A neat feature is that you can save profiling sessions for historical access. That can be a life saver if you are trying to tune and are trying out different approaches. Instead of keeping your own backups, use the history. You can also share that history so that all of you developers have the same baseline. Nice.

Hotsos also provides a discussion group for questions relating to the extension. If you are a Hotsos profiler user, you should check out this extension.

oracle hotsos

CUNY Chooses Oracle

noreply@blogger.com (LewisC) — Sat, 20 Oct 2007 02:05:00 +0000

City University of New York (CUNY) has standardized on PeopleSoft Financials, Human Capital and Campus Solutions. Of course, all of this will be running on Oracle 11g and, eventually, Fusion.

"We chose Oracle to provide the highest quality service and advanced solutions to accommodate our institutions' various sizes and non-traditional programs," said Brian Cohen, CUNY's chief information officer and co-project leader for CUNY FIRST. "We chose Oracle's PeopleSoft Enterprise Campus Solutions 9.0 for the new capabilities this version offered and look forward to leveraging it to transform our student information system."

CUNY selected Oracle due to the functionality and scalability of its applications - vital factors considering CUNY's unique composition of senior and community colleges and other programs and institutions - and the number of PeopleSoft implementations within higher education institutions in the U.S. Oracle's application solutions and database will enable CUNY to implement a single database instance for the entire university in a true multi-institution configuration.

"We chose Oracle to provide the highest quality service and advanced solutions to accommodate our institutions' various sizes and non-traditional programs," said Brian Cohen, CUNY's chief information officer and co-project leader for CUNY FIRST. "We chose Oracle's PeopleSoft Enterprise Campus Solutions 9.0 for the new capabilities this version offered and look forward to leveraging it to transform our student information system."

CUNY selected Oracle due to the functionality and scalability of its applications - vital factors considering CUNY's unique composition of senior and community colleges and other programs and institutions - and the number of PeopleSoft implementations within higher education institutions in the U.S. Oracle's application solutions and database will enable CUNY to implement a single database instance for the entire university in a true multi-institution configuration.

Ron Spalter, deputy chief operating officer and co-project Leader for CUNY FIRST stated, "It was significant to us that Oracle offered CUNY the Oracle Insight program, which we refined to focus on a high profile area for CUNY students - financial aid. We are working with Oracle to frame, analyze and resolve a number of outstanding opportunities in this area."

The City University of New York is the nation's largest urban public university. CUNY comprises 23 institutions: 11 senior colleges, six community colleges, the William E. Macaulay Honors College at CUNY, the Graduate School and University Center, the CUNY Graduate School of Journalism, the CUNY School of Law at Queens College, the CUNY School of Professional Studies and the Sophie Davis School of Biomedical Education. The University serves more than 231,000 degree-credit students and 230,000 adult, continuing, and professional education students. College Now, the University's academic enrichment program for 32,500 high school students, is offered at CUNY campuses and more than 300 high schools throughout the five boroughs of the City of New York.

oracle peoplesoft

Oracle's Letter to Bea

noreply@blogger.com (LewisC) — Sat, 13 Oct 2007 15:03:00 +0000

Oracle tried to buy Bea, Bea slapped them down, Oracle responds:

October 12, 2007
Board of Directors
BEA Systems, Inc.
2315 North First Street
San Jose, CA 95131

Dear Members of the Board of Directors:
As discussed with your management team, Oracle remains available to discuss and complete a transaction quickly and efficiently.

We believe that it is important to clarify the sequence of activities that have transpired over the past few days. Upon receipt of Bill Klein's letter dated October 11, I contacted him to address any process concerns. Bill and I agreed on an accelerated process that would be, by anyone's standard, "short in duration" and not "open-ended" and that would permit BEA to not "divulge competitively sensitive information."

BEA's management agreed to meet this morning at 10:00am Pacific time to commence a process intended to result in the execution of definitive agreements before the open of business on Monday, October 15. Unfortunately, BEA cancelled the meeting late last night and declined our invitations to reschedule. In my subsequent discussions with Bill earlier today, I asked whether there was any process that BEA would prefer to follow to move towards a friendly transaction and was told that BEA had no such process in mind.

We are available to proceed immediately with a process that would lead to a friendly transaction. In the meantime, we remain committed to our proposed price of $17.00 per share, provided that the BEA Board and management team do not institute any measures which reduce the value of the company or shift value from BEA's shareholders to the management team. Our proposed price is a substantial premium to an already-inflated stock price that reflected speculation of the potential sale of BEA and represents a more than 40% premium to BEA's stock price before the appearance of activist shareholders in mid-August of this year.

Sincerely,
ORACLE CORPORATION
Charles Phillips
President

oracle bea

Oracle Makes PHP Code Open Source (PHP License)

noreply@blogger.com (LewisC) — Wed, 10 Oct 2007 00:58:00 +0000

Oracle today announced the contribution and a preview release of an enhanced Oracle Call Interface (OCI8) database driver for PHP. This helps bring breakthrough scalability to PHP applications, further enhancing PHP as a viable development environment for mission-critical applications. The OCI8 database driver for PHP supports important Oracle® Database features such as connection pooling and fast application notification, enabling a single industry-standard server to support tens of thousands of database connections while providing higher availability.

The enhanced OCI8 database driver for PHP provides new, improved integration between PHP and Oracle Database 11g, to allow a server-side connection pool shareable across web servers and languages, significantly enhancing the scalability of web-based systems. Highlights include:

* Breakthrough Scalability -- leveraging Oracle Database 11g's Database Resident Connection Pool (DRCP) feature, a large number of users can be supported with significantly reduced memory consumption. Multiple web servers running on different systems can share a single database connection pool, helping to further reduce consumption of system resources;

* High Availability -- supports advanced Oracle Database features such as fast application notification with Oracle Real Application Clusters and Oracle Data Guard. PHP applications can benefit from reduced downtime and higher levels of availability with this feature;

* Extended Compatibility -- existing PHP applications can take advantage of DRCP and fast application notification without changes in application code, resulting in an immediate boost in scalability, enabling more efficient hardware utilization and lower TCO.

"Oracle continues its long track record of contributions to the open source community, and with this OCI8 database driver for PHP we also help PHP developers leverage the power of the industry-leading Oracle Database 11g," said Ken Jacobs, vice president Product Strategy, Server Technologies, Oracle. "With these new enterprise-class features that Oracle brings to the PHP community, we expect to further strengthen PHP as a tool of choice and expand use of Oracle Database for Web 2.0 and mission-critical enterprise application deployments."

oracle php

Oracle Awarded for Innovation

noreply@blogger.com (LewisC) — Wed, 10 Oct 2007 00:46:00 +0000

Oracle won the CMP Channel VARBusiness 2007 Tech Innovator award in the Business Enterprise category for the Oracle SOA Suite. The SOA Suite is part of Oracle Fusion. SOA Suite is Oracle's answer to web services and service oriented architecture.

oracle award

Team Oracle Flies for Fleet Week

noreply@blogger.com (LewisC) — Sat, 06 Oct 2007 22:58:00 +0000

Awesome picture over San Fran.

oracle planes

Video Game Distributor Chooses Oracle Demantra

noreply@blogger.com (LewisC) — Sun, 30 Sep 2007 17:33:00 +0000

Jack of all Games, the largest dedicated full line stocking video games distributor in the United States, has successfully implemented Oracle's Demantra to revamp its demand planning and forecasting operations. The leading game distributor implemented Oracle's Demantra to establish an efficient and reliable process for capturing and managing information while streamlining sales and ordering. Jack of all Games maintains exclusive distribution agreements with Take 2, Globalstar, Rockstar and Gathering of Developers to ensure that the hottest game titles reach national retailers as quickly as possible.

"Across the United States, we are shipping the latest games to all major retailers as well as many regional locations, video stores and all branches of the military. To ensure that our supply is consistent with our customers' demand, we needed a solution that would help to ensure that the right games, in the appropriate quantities are available when needed," said Jack of all Games Director, Manufacturing and Distribution Systems, David Moon. "After evaluating multiple vendor offerings, Oracle's Demantra stood out as the most comprehensive, scalable demand planning solution available today. The implementation took place in less time than originally forecasted and under budget."

oracle demantra

The Coming of the Oracle Database Appliance

noreply@blogger.com (LewisC) — Fri, 28 Sep 2007 22:16:00 +0000

It looks like Oracle is making the move towards appliances, albeit in a more componentized way.

Oracle today announced the Oracle Optimized Warehouse Initiative to help accelerate data warehouse deployments by offering a choice of optimized solutions that combine the performance, reliability and scalability of Oracle(r) Database with hardware and storage from industry leading manufacturers.

As part of this initiative, Dell, EMC and Oracle today introduced the initial Oracle Optimized Warehouse. (See today's related press release at: http://www.oracle.com/corporate/press/2007_sep/dell-emc-oracle-warehouse.html ). Available through Dell, the Oracle Optimized Warehouse for Dell and EMC is comprised of Dell PowerEdge servers, EMC CLARiiON networked storage systems and Oracle Database.

"As the data warehousing market continues to grow and mature, Oracle is evolving to meet the changing needs of our customers," said Ray Roccaforte, vice president of Data Warehousing and Business Intelligence Platform, Oracle. "With the Oracle Optimized Warehouse Initiative, customers no longer need to choose between proprietary data warehouse solutions and Oracle-based solutions custom built on leading hardware platforms. Oracle now delivers the advantages of its market-leading database within a simple-to-buy, pre-built product optimized for data warehousing out-of-the-box."

oracle appliance data warehouse dell

Oracle vs SAP gets trial date

noreply@blogger.com (LewisC) — Wed, 26 Sep 2007 22:46:00 +0000

Are you following this case? Didn't SAP admit wrong doing? Why is this going to court?

From ZDNet, Oracle vs SAP gets trial date.

Here are the key dates:

* Feb. 12, 2008: Another case management hearing.
* Nov. 13, 2008: The last date the judge will hear motions regarding the case.
* Feb. 9, 2009: The trial date.

Seems like a huge waste of resources to me.

oracle sap

Oracle Struts Its Software

noreply@blogger.com (LewisC) — Sat, 22 Sep 2007 17:38:00 +0000

A good article on Motley Fool, discusses Oracle's outstanding financial performance. I still think Oracle needs to do more to counter open source offerings but numbers like these are hard to argue with.

The numbers continue to speak for themselves at Oracle. First-quarter sales advanced 26% and earnings grew 28%. Better yet, new software license revenue grew 35%, well ahead of the 23% growth in license updates and product support and the 25% improvement in services revenue. Management explained it by noting that its installed base is growing, which only leads to lucrative product updates and related services down the road.

It looks like buying out the competition is having a doubly good effect. The competition no longer exists so Oracle really rules the business software arena. While competitors on the database market have made good progress (I'm talking DB2 v9, Postgres 8.2 and MySQL 5.2), they are always playing catchup. DB2 V9 started closing in on Oracle 10g but 11g creates a new playing field. Postgres and MySQL are working hard but still have a long way to go.

oracle financials

Dubai Police Depend on Oracle

noreply@blogger.com (LewisC) — Sun, 16 Sep 2007 22:36:00 +0000

Dubai Police has chosen Oracle as their database of choice.

Dubai Police has achieved remarkable returns on its technology investment as a result of deploying Oracle software solutions. Oracle built a robust foundation to automate and streamline Dubai Police's messaging processes with its market-leading Oracle® Database software. The solution now serves more than 5000 internal users.

"As a major government entity, Dubai Police has a growing pool of internal and external IT users - employees and clients respectively. In line with Dubai Government's e-government initiative, Dubai Police had a strategic need to migrate toward an enterprise-level IT infrastructure to enhance productivity and streamline operations," said Colonel Ahmed Hamdan Bin Dalmook, manager e-services department, Dubai Police. "We wanted to organise and automate our daily correspondence, and align our business processes between departments."

oracle oracle

Oracle® Database 11g Running on HP with Windows Sets New World Record

noreply@blogger.com (LewisC) — Sat, 15 Sep 2007 03:29:00 +0000

Nifty! Oracle® Database 11g Running on HP with Windows Sets New World Record.

Oracle announced a new world record price/performance result with the TPC-C benchmark running Oracle® Database 11g on Windows. With this result, Oracle now holds the top two record benchmark positions in the coveted Top Ten TPC-C price/performance category. Optimized for small, medium and growing businesses (SMBs), the combination of Oracle Database 11g and Windows on an HP ProLiant server provides an ideal platform to meet these companies' individual business needs. This benchmark result further demonstrates Oracle's commitment to providing customers of all sizes unmatched price/performance and scalability.

"Out of the gate, Oracle Database 11g sets a new price/performance bar for the industry and it will continue to excel as it matures," said Juan Loaiza, senior vice president Systems Technology, Oracle. "This benchmark result furthers Oracle's commitment to SMBs, and our continuing promise to deliver on a long tradition of database excellence. SMBs that once thought Oracle was only for large enterprises need to take another look at Oracle Database."

oracle 11g

Oracle Buys Netsure, Analytics Provider

noreply@blogger.com (LewisC) — Sat, 08 Sep 2007 20:43:00 +0000

Ireland based Netsure, a communications intelligence and analytics provider, will become part of Oracle's Communications Global Business Unit, which delivers compelling communications-specific applications to service providers worldwide.

"The addition of Netsure's products to Oracle's comprehensive communications applications suite is expected to help Oracle's customers improve network utilization, optimize capacity planning and financial modeling, and streamline end-to-end network lifecycle management," said Oracle Senior Vice President and General Manager, Bhaskar Gorti. "In conjunction with Oracle's ERP, supply chain, inventory management and network discovery solutions, Oracle plans to provide service providers the ability to improve their network ROI and increase their operational efficiency as they launch, deploy and profit from next-generation IP services."

oracle netsure

Employers, Post Your Job Here

noreply@blogger.com (LewisC) — Mon, 03 Sep 2007 16:57:00 +0000

I have signed up with a new service, Job-a-matic, to list and promote Oracle related jobs. Why would you want to list your jobs here?

Post a Job!
$75 for 30 days

This is a job listing that targets Oracle professionals. As such, you can target your Oracle needs to a very specific market.
It’s easy to post jobs to Oracle-Jobs using Job-a-matic’s four step process.
Your jobs will also be posted across the entire Simply Hired jobs network, which includes sites like MySpace Jobs, LinkedIn, MyWay and, of course, Simply Hired.
I will promote and high-light weekly job offers.
At $75/month per listing, it’s a darn good deal!
Do you really need Oracle-savvy talent? Give Oracle-Jobs a try.

oracle jobs listing employer

Oracle Jobs

noreply@blogger.com (LewisC) — Mon, 03 Sep 2007 16:53:00 +0000

I am now listing Oracle Jobs here on the Oracle DB News blog. You can click the banner above or on the list of jobs to the right.

You can also click this banner:

Post a Job! $75 for 30 days

If you need a job, think about going this route.

LewisC

oracle jobs

Common Mistakes in Oracle PL/SQL Programming

noreply@blogger.com (LewisC) — Sat, 01 Sep 2007 01:30:00 +0000

I was browsing around YouTube tonight and ran across this video of a webinar that Steve Feuerstein did back in 2005. It's about an hour long. He covers various pl/sql tips that for the most part you should know by now unless you are just starting out. It is probably a good refresher for someone who doesn't spend most of their time coding PL/SQL.

Some of the things he calls mistakes, I would not. Some are just preference issues. Worth watching though.

LewisC

oracle video pl/sql tips

A Match Made In Heaven

noreply@blogger.com (LewisC) — Wed, 29 Aug 2007 02:27:00 +0000

eHarmony, the large, internet dating site selects Oracle. eHarmony, who call themselves the #1 relationship service, has chosen Oracle to run its websites and data warehouse.

eHarmony uses its patented Compatibility Matching System, developed from 35 years of clinical and empirical research, to match highly compatible singles online. To support the more than 10,000 new users who register each day, eHarmony required a data management solution that would scale to accommodate its growing user base and to help ensure that its site is available 24 hours a day, seven days a week. To enhance its online services and end user experience, eHarmony contracted Cloud Creek Systems, Inc., a Southern California-based Oracle Certified Partner, to assist in the migration of eHarmony's Web sites and data warehouse to Oracle. eHarmony deployed Oracle Database 10g, Oracle Real Application Clusters, Oracle Clusterware, Oracle Automatic Storage Management, and Oracle Enterprise Manager 10g on multiple Sun Fire X4600 servers running Windows.

In addition to its clustered environment, eHarmony operates a rapidly growing Oracle data warehouse to support its extensive real-time and historical reporting initiatives. Oracle Partitioning is used in conjunction with the data warehouse to improve performance. eHarmony's Data Analytics Group relies on the information to support critical business decisions such as how to improve service offerings and enhance the eHarmony customer experience.

"We moved to Oracle because our services required greater scalability and availability," said Mark Douglas, vice president of technology, eHarmony. "We felt we had reached the limits with our previous platform. As a result of the move to Oracle, we're able to confidently support more users and we've improved Web site performance."

oracle eharmony

Army Corps Chooses Oracle 11g

noreply@blogger.com (LewisC) — Mon, 27 Aug 2007 21:05:00 +0000

From GCN.com, Army Corps ready to build on Oracle 11g, Researchers find potentially valuable advances in Web services and working with geographic data.

The Army Corps of Engineers has been beta testing Oracle 11g and is planning to use new Geo-Spatial extensions and 3D data types.

“Our geospatial information and our business information are being dovetailed…more in the same space.” — Michael Smith, Cold Regions Research Engineering Laboratory

In addition to the geospatial data structures, 11g offers other advanced data structures, such as point cloud storage and indexing.

A point cloud is a set of data collection points that represent 3-D space. The laboratory uses this type of data structure for recording measurements that come from its Light Detection and Ranging instruments.

Until now, such data had to be stored on flat text files. Databases had no way to tie together the datasets in a way that would let them calculate the datasets against a single entity. However, 11g can store a point cloud as a single object, against which developers can write simple queries to do line-of-sight, data point intensity or nearest-neighbor calculations “without even moving the data out of the database,” Smith said.

They will also be using ApEx for front-ends.

LewisC

oracle 11g spatial 3d

10 Basic Interview Questions for Oracle

noreply@blogger.com (LewisC) — Sat, 25 Aug 2007 01:24:00 +0000

For Basic SQL:

How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.

What is an aggregate function? Grouping, sums or counts, etc.

What is an interval? Specifies a period of time.

What is a nested subquery? A subquery in a where clause.

What is the dual table? A single row table provided by oracle for selecting values and expressions.

For Basic PL/SQL:

Describe the block structure of PLSQL. Declaration, Begin, exception, end.

What is an anonymous block? Unnamed PL/SQL block.

What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.

What is the difference between an explicit cursor and a select into? An explicit cursor is declared in the declaration section, a select into is declared in the body of the code.

Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a "good thing".

Is this list helpful to you? Would you like me to do a series on interviewing and interview questions?

LewisC

oracle interview sql questions