From the Database Geek.

Part of the ANSI SQL standard calls for an INFORMATION_SCHEMA. This schema contains a standardized data dictionary that is (or is supposed to be) common across various databases. Most database vendors offer a native data dictionary and a sub-set INFORMATION_SCHEMA (called info schema from this point on). Oracle is the only major database vendor (that I know of) that doesn’t even offer a subset of the info schema.

Oracle’s data dictionary (especially if you include the V$ views in that) is the most robust of all the databases I have used.

From the Database Geek.

I recently had a requirement to populate the day dimension of a data mart (I won’t put all of the code here as it’s pretty large). That’s not that big deal but part of the requirement was to set several columns: BUSINESS_DAY_FLAG, BUSINESS_DAY_NO and BUSINESS_DAYS_REMAINING_NO.

• The BUSINESS_DAY_FLAG is Y is the date is MON-FRI and N is the date is SAT or SUN.
• BUSINESS_DAY_NO is the business day of the month. There are 5 business days per week so if the month started on a monday, the second monday would be business day 6.
• BUSINESS_DAYS_REMAINING_NO is the number of business days remaining in the month.

From the Database-Geek.

In addition to frequently being asked to recommend books for newbies to Oracle, I am also asked for guidance on certification. Basically, what materials are available and worth purchasing. I used Self Test software and the Sybex books when I was working on my certification. That was back in the 9i days. If you are working on 9i certification, I strongly recommend them.

However, I figure you are probably working on an 11g certification; 10g at least. In that case, I would guess that Sybex and Self Test are still good options, I just haven’t personally worked with the latest versions (software or books).

From the Database-Geek.

Continuing with the parser, begun week in PL/SQL Parser Part 1 and PL/SQL Parser Part 2, today I am going to modify the code to account for keywords, operators and data. By data, I don’t mean strings. I mean anything not a keyword, not a comment and not an operator. Data may be a quoted string (which we accounted for in Part 2), but it is also non-language functions and procedures. If you call a user defined procedure, that procedure call is considered data (at least it is here, for now).

In the code presented below, I have done several things.

Posted by the Database-Geek.

DBAs and developers, do you suffer from PD? PD, performance dysfunction, is not a topic that many like to discuss. A few people do but they mostly hang out together at conferences and talk about the size of their tuples. For the rest of us, PD is an evil, evil thing.

There are as many types of PD as there are causes. Today I want to talk about a particularly insidious type referred to as PO or Premature Optimization. PO can strike at any time, any where. It’s nothing to be embarrassed about but it is something to fix.

Posted by the Database-Geek.

Summary Totals Using SQL

Have you ever needed to get totals for different combinations of columns in the same query? Did you ever want to create a nice little report from a simple SQL statement? Here’s a tip that can make your life a lot easier when you need it. The great thing about this is that you can use it in a ref cursor called from other programming languages and reduce the number of calls you need to make to the database.

Let’s say you are running queries against the Employees table of the sample HR schema provided by Oracle.

SQL> SELECT count(*) FROM Employees;

  COUNT(*)
----------
       107

Now, we want to get a total of salaries for each job in each department.

LewisC’s An Expert’s Guide to Oracle Technology
Is it possible to build a PL/SQL parser using nothing but PL/SQL? To answer that question, I guess I need to define “PL/SQL parser”. What would be the intention of this parser?
I would like a way to pa…

thnx

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/what-is-the-difference-between-oracle-sql-and-plsql-9602!***!

First of all, I would like to thank you for sharing this helpful article. I am doing my BSc Computer science(second year). I wanted to know more about oracle.I am thinking of getting a certificate in Oracle course; But I’m confused in which category? I would be happy if you would suggest me something! As i will be learning SQL and database in my third year, so I think it would boost my carrier too. If I’m wrong then pls correct me.

Thank you,

Prajit Nair.

!***! Entry Link:http://it.t

Hello, I tried to answer, but I could not

first:

Write PL/SQL function to find the employee details .function must pass empid as a parameter?

second:

Write a PL/SQL Procedure to update the employee information by using employee ID?

thank you

!***! Entry Link: Learn PL/SQL: Procedures and Functions!***!

sir,

I am a second year engineering student with IT branch.

I want to learn oracle upto the best level.Kndly guide me,like how should i start and provide me some good notes.

thankyou sir

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/learn-oracle-what-is-oracle-5454!***!

wow power leveling Grinding Guide

Some people consider grinding the fastest way to level in the wow power leveling, grinders will choose wow powerleveling a zone where the mobs are close to their world of warcraft power leveling (maybe a little higher) and they will just kill and <A href=”http://www.wow-p

hi sir,

I am a student. Doing my masters in software engineering.

I want to learn oracle. Can u please help me in learning. LIKE where can i get correct material or notes to learn.

thanking you sir,

Krishna Reddy.Palli

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/learn-oracle-what-is-oracle-5454!***!

Lin,

That’s a topic I have been meaning to do for quite a while. I think I may spend some time on that in the next couple of weeks.

Thanks,

LewisC

!***! Entry Link: Learn PL/SQL: Procedures and Functions!***!

thanks for yr article

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/learn-oracle-what-is-oracle-5454!***!

can you pls discuss procedure vs package?Thank you

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/learn-plsql-procedures-and-functions-13030!***!

learn dbms and sql and pl/sql

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/learn-oracle-what-is-oracle-5454!***!

Hi Sir,

Firsttime i am vising your site, i am very happy to c you, and happy to get more information on SQL as well as PL/SQL.

Sir, the information you have provided is more understandable, and we still hoping the same Sir, thank you.

Regards,

Vikramasimha

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/what-is-the-difference-between-oracle-sql-and-plsql-96

suvro,

If you just want to learn database management from a Java perspective, there is no reason to learn PL/SQL. However, if you want to learn database management from an oracle perspective, you must learn pl/sql. It really depends on what you want to do, code for databases, or code for Oracle.

Even though Oracle supports java in the datavase, PL/SQL gives you so much more control that I would say it is mandantory for anyone who truly wants to be an oracle expert.

T

hi Lewis,

i read your articles n it gave me a nice precise idea about oracle,sql n pl/sql.currently i’m doing my engineering n i know java well.right now i want to learn database management skills.but i have the following questions in my mind.hope u can help.

1.as java n pl/sql are so similar in nature, can i use my java knowledge through oracle for learning database management?

2.will it better than learning pl/sql in anyway?

3.n if yes then what exactly do i need to learn?
<b

i am new to this site and i am using thgis opportunity to greet all my friends and brothers in here God bless you all. am brain rollas by name and i will like to know about this subject: ORACLE. feel free to email me with more information and explanation. Thanks and God bless.

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/learn-oracle-what-is-oracle-5454!***!

Hello Sir,

I read your article and feedbacks.

I know the difference char, varchar and varchar2.

But be practical I understood first time with the help of Length() function.

Your explanation procedure is good and helpful.

Thanks sir

!***! Entry Link: What is the difference between Oracle, SQL and PL/SQL?!***!

LewisC’s An Expert’s Guide to Oracle Technology
I needed to update some files and that meant updating some utility functions that I haven’t looked at in a long time. I decided to share a few of them today.
One of the most important aspects of PL/SQL…

LewisC’s An Expert’s Guide to Oracle Technology
Well, I have been using Oracle’s new SQL Developer Data Modeler (OSDM) for a couple of months now. I’ve upgraded to beta 2. I have to say I really like it. I have given some feed back and the response …

LewisC’s An Expert’s Guide to Oracle Technology
Ok, I figured out how I am going to do this. I am making my book available three ways: print, ebook and html.
For print, you can get it on amazon or through my e-store. It’s $14.95
For ebook, you can g…

I have written a new book on SQL DML. This is a total beginner book: how to commit and rollback, how to query, how to add data, etc.

Probably not of interest to most of the people who read this blog but if you know of anyone completely new to SQL, this would make a great Christmas present. Only 14.95. It is completely vendor agnostic, although the examples all use Oracle and MySQL.

You can view the Table Of Contents, Preface and Index here. I plan to release some of the chapters for free on the blog and will make the PDF of the book available at a discount.

LewisC’s An Expert’s Guide to Oracle Technology
My SQL book is now available for print. You can see it on my CreateSpace e-store. The book is SQL DML – SQL Starter Series Book 1. This is a book for beginners. It takes someone step by step through TC…

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.

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.

I’m in Baltimore this weekend at the Postgres East 2008 conference. I’m giving a presentation tomorrow on SQL/XML for developers. Below is the presentation and below that is the code used in the presentation.

Code to follow along:

If you aren’t using javascript, you can get download the presentation or the script from scribd.
Or, to make it easy, just grab this code:

Slide 25

CREATE TABLE EMP
(
  LAST_NAME text,
  EMP_ID integer NOT NULL,
  FIRST_NAME text,
  DEPT_ID integer,
  SALARY integer,
  CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID)
)
WITH (OIDS=FALSE);
ALTER TABLE EMP OWNER TO xmluser;

Slide 26

INSERT INTO
    EMP(
       LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY)
    VALUES
      ('Blow', 1, 'Joe', 1, 10000),
      ('Head', 2, 'Helmut', 1, 12000),
      ('Jack', 3, 'Noe', 1, 12000),
      ('Hard', 4, 'Blow', 2, 20000),
      ('First', 5, 'Hugo', 2, 21000),
      ('Spaem',6, 'Kingoof', 2, 20000),
      ('Ventura', 7, 'Ace', 3, 35000),
      ('Nickleby', 8, 'Nick', 3, 400000),
      ('Budd', 9, 'Billy', 4, 99000),
      ('Cleaver', 10, 'Wally', 4, 100000) ;

Slide 29

SELECT xmlelement(name main, last_name) from emp;

Slide 30

SELECT xmlelement(name main, last_name),
       xmlelement(name main, first_name)
FROM emp;

Slide 31

SELECT XMLForest(last_name, first_name)
FROM emp;

Slide 32

SELECT xmlelement(name main,
       xmlforest(last_name, first_name) )
FROM emp;

Slide 33

SELECT XMLAgg(
  XMLForest(last_name, first_name) )
FROM emp;

Slide 34

SELECT XMLElement(name main,
       XMLAgg(XMLForest(last_name, first_name) ))
FROM emp;

Slide 35

SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum) )
FROM emp;

Slide 36

CREATE TEMP SEQUENCE t_seq;

SELECT xmlelement(name main, xmlattributes(nextval('t_seq') AS rownum),
       xmlforest(last_name, first_name) )
FROM emp;

DROP SEQUENCE t_seq;

Slide 38

SELECT xmlelement(name main,
       xmlforest(last_name || first_name AS fullname, salary) )
FROM emp;

Slide 39

SELECT xmlelement(name main,
       xmlelement(name fullname, xmlattributes(dept_id), last_name || first_name ),
       xmlforest(salary) )
FROM emp;

Slide 40

SELECT xmlelement(name main, xmlcomment('Each Name Goes Here'),
       xmlforest(last_name, first_name))
FROM emp;

Slide 41

SELECT xmlelement(name lastname, last_name),
       xmlelement(name firstname, first_name)
FROM emp;

Slide 42

SELECT xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) )
FROM emp;

Slide 43

SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp;

Slide 44

CREATE TABLE xmltab (col1 XML);

INSERT INTO xmltab (
  SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp);

Slide 45

SELECT * FROM xmltab;

Slide 47

SELECT xpath('/main/firstname/text()', col1) FROM xmltab;

Slide 48

SELECT textcol[1] FROM (
  SELECT xpath('/main/firstname/text()', col1) AS textcol
    FROM xmltab ) AS xmlsource;

Slide 49

select query_to_xml('select * from emp', TRUE, TRUE, 'scott');

Slide 50

select table_to_xml('emp', TRUE, TRUE, 'scott');

postgresql conference sql/xml presentation