Monday, January 16, 2006

More Simple and Important Things to Learn on The Way

Vanilla text output: SELECT 'Executing backup at ', SYSDATE FROM DUAL;

Executing a stored procedure: EXECUTE PACKAGE_NAME.PROCEDURE_NAME;

Disabling column headings: SET HEADING OFF;

Thursday, January 12, 2006

Viewing a View

When you SELECT from a view, it shows the information relevant to the current schema, the one with which you connected.

I spent hours trying to figure out why a view was showing data which had nothing to do with what i expected to see, until i figured out (with a little help from my friends) that it shows quite different things if you're logged in with the SYSTEM schema. I should have viewed it in an application-specific schema.

Tuesday, January 03, 2006

Little Notes About Creating Views and Tables

I'm doing simple edits to a bunch of view and table creation scripts. Creating a view seems very simple:

CREATE OR REPLACE VIEW view_name
          (
          col1name,
          col2name,
          col3name
          )
AS
SELECT col1,
       col2,
       col3
FROM   table;

I looked into the documentation in Oracle's SQL Reference, hoping to find something to criticize, but found it to be easy to find and well written.

When i ran the script, i got one error, about creating a table. One of the scripts had a CREATE TABLE statement and that table already existed. I asked my team mate for his opinion on this and he said that all table-creating scripts should drop the table before creating. I boldly suggested it to our DBA, and he thought otherwise — he says that this will make the system much more accident-prone. And i should learn why doesn't CREATE TABLE have a OR REPLACE clause, like CREATE VIEW.

This reminded me of my Russian-speaking cousin Slavik, who taught me a lot about computers when i was a teenager. When he spoke about any kind of computer operation called "view" (remember Norton Commander?), he, not knowing much English, would ignore the w and pronounced it vyev ("вьев").

Sunday, January 01, 2006

... Coming Back to Life

I neglected this blog a long time ago. Since then i found a new job - and the little i did learn about Oracle 10g certainly helped me get it.

Now i use Oracle here and learn new stuff every day. Suddenly i recalled that i have this blog - so why not record the experiences?

I'm reviving it.