From The Database Geek

I like playing with new tools and software so I thought I would start a new weekly series, Cool Tool of the Week. I will just cover a tool that I have used or discovered that I think is cool. I’m not necessarily endoring it, just giving it a little publicity so that others will know about it and can give it a try.

This week is the open source data modeling tool Open ModelSphere. This Open Source project is available for windows with a GPL license. It was originally known as SilverRun. I used this windows-based CASE tool when I was at Yale.

Continuing from Part 1.

So now we have our transactional model and a basic user story:

Our first request from the business for our data mart is that they want to be able to query all of the orders by date, by customer and/or by region (state, city or country). They want to be able to aggregate (sum and average) across those items.

The first thing we need to do is talk to the business and find out exactly what that request means. Do the users want to see information about daily orders in general?

It was pointed out to me that I had a few errors in the previous release.  Those have been fixed and the install should go a lot smoother now.  No additional functionality here, just bug fixes.  If you tweaked the previous release to install it, you don’t need this one.

As always, you can download the new code directly from the PL/JSON SourceForge page.

Change Log:

Version: 0.6.2
Fixed exit commands in various code files
Removed the “CREATE OR REPLACE” on the string_handler.printf function

Version: 0.6.1
Added missing string_handler package

Version: 0.6
Added support for creating JSON from text input

Version 0.5
Initial release
Includes a JSON data type
Can create tables and columns of JSON
Supports API creation of JSON data type

LewisC

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.

PL/JSON v0.6.1 has now been released to Sourceforge.net. The reason for the .1 is that I forgot a dependency on the 0.6 release.

Anyway, PL/JSON can now create JSON via the API or through parsing a text string. The parser is implemented as a stand along package. That means you can write your own parser (or use an external procedure) if you don’t like mine. This will make it very extensible. I also plan to add PL/SQL callbacks to the parser at some point for custom processing.

Example of parsing:

SET SERVEROUTPUT ON

DECLARE
  v_json json;
  v_json2 VARCHAR2(32000) :=
   '{
      "abc": "dpkxvdvvcxz\"vxasasa   ",
      "def": 12345,
      "ghi":{"isit":true, "nope":false,"denada":true },
      "jkl": [1234, 45678.99, 121211, 21323232, 00000]
   }';
BEGIN

  v_json := json(v_json2);
  v_json.print;
END;
/

{
"abc":"dpkxvdvvcxz\"vxasasa   ",
"def":12345,
"ghi":{
"isit":true,
"nope":false,
"denada":true}
,
"jkl":[1234,45678.99,121211,21323232,0]}

Output from PL/JSON checks out on JSONLint.

From the Database Geek.

I recently needed to query all of the arguments to the various stored procedures, functions, packaged objects and types. Oracle provides all of this information in the data dictionary in the ALL_PROCEDURES and ALL_ARGUMENTS views. The problem with this is that it’s kind of kludgey to suck out all of the pertinent data.

One problem is that arguments for procedures and functions in packages is access differently than arguments for stand alone procedures and functions. What I did was combine the two calls into a single pipelined function that returned the data I needed.

From the Database Geek.

I’m not even going to try to explain why I wrote this. Let’s just say that it comes in handy on occasion.

What this very simple procedure does is use DBMS_SCHEDULER to execute a shutdown command in windows. This could easily be adapted to Unix and/or Linux.

Use at your own risk. By default you get a 30 or 60 second delay (depending on windows version, I think) before the shutdown/reboot.

If you pass FALSE in to the procedure, or don’t pass anything, the server will shutdown. If you pass in a TRUE, the server will reboot.

I get a lot of email, and comments on blog entries, asking questions about many different topics. I am going to try to answer some of the more common questions as FAQs. Today I am starting with some NUMBER FAQs. I am specifically talking about Oracle and I am specifically talking about the NUMBER data type, not numerics in general. I’ve written about NUMBERs before.

Q: What is the difference between an INTEGER and a NUMBER.

A: INTEGER is a subtype of NUMBER. INTEGER is declared as NUMBER(38,0). That means it is a “constrained” NUMBER.

I had the opportunity to spend a day in a seminar with Ralph Kimball. If you don’t know who that is, he is a guru of data warehousing. If you’re involved in data warehouses, I hope you are at least familiar with his work. Currently in the industry there are two primary, competing warehousing methodologies, i.e. practically religions to some, Kimball vs. Inmon. I think that’s kind of silly. A methodology is like a hammer or a drill; choose the best one for the job. If I absolutely have to pick, I’m in the Kimball camp.

From the database geek.

Postgres 8.4 is just around the corner. I haven’t had much time lately to keep up with Postgres. I’ve had too much to do in the Oracle world (new job, writing, etc). However, I do try to keep up with a few email groups just to keep touch with what’s coming. Here are three things in Postgres 8.4 that I think will be interesting to a lot of people. Two of them bring Postgres that much closer to Oracle and one of them is useful (at times).

SQL Analytics

SQL Analytic Functions, also called Window Functions, allow you to execute functions across multiple sets of data within a single query.

For example, you can query the max value (or sum, average, whatever) of a field for multiple sort orders in a single query.

From the Database-Geek.

Continuing with the parser, begun last week in Building a PL/SQL Code Parser Part 1, today I am going to modify the code to allow for comments. Rather than dive back into code already covered, I will discuss what I have added and then provide the full code below.

I changed my sample code and test proc to a new format:

declare

  v_string varchar2(32000) :=
  'CREATE OR REPLACE PROCEDURE yada' || chr(10)  ||
      ' AS ' || chr(10) ||
      ' /* This is a comment */  ' || chr(10) ||
     ' BEGIN' || chr(10) ||
     '   DBMS_OUTPUT.PUT_LINE(''string with a space'' ||' || chr(10) ||
     '   ''string with emb''''edd''''''''ed quotes'');' || chr(10) ||
     '   -- second_func(''test'');' || chr(10) ||
     ' END;' ;

begin

  v_string := lrc_plsql_parser.parse_line(v_string);

END;

Notice that in addition to the comments, I have added line feeds.

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.

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 parse a PL/SQL code block and let me determine some statistics: number of lines, number of keywords, number of DML statements. It would need to recognize comments. I also want to be able to format the code for pretty printing. I want to be able to apply user defined checks for adherence to coding standards.

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

One most important questions….

whats the vertica database License cost??

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/state-of-the-database-2008-vertica-part-1-26171!***!

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!***!

The OTN license specifically says “The programs may be installed on one computer only, and used by one person in the operating environment identified by us.” So if you install Oracle on a server and more than 1 person is using the server to develop, technically you need a license.

But the agreement also says that if you fail to comply with it, Oracle has the right to terminate the agreement and you must then destroy the software. But there is no penalty or fee to worry about if you ar

thanks for yr article

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

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

I think Phil would say that according to the Oracle page for the database machine:

The HP Oracle Database Machine is a complete system, including software, servers, and storage, designed to run large, multi-terabyte data warehouses 10x faster than conventional data warehouse systems.

Oracle is targeting data warehouses not “normal, everyday usage of Oracle Database.”

Lewis,

Are we comparing apples with apples?

The Oracle database machine seems to me to be a generic machine trying to improve performance for normal, everyday usage of Oracle Database.

Oracle database includes some ROLAP functionality but is not, strictly speaking, a dedicated data warehouse database (cf HOLAP and MOLAP).

Because of this I would not expect the Oracle offering to perform better than the Netezza one. What would Phil Francisco say to this?

<

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

Need a database in your cloud? Check out CouchDB.
What is CouchDB?
CouchDB is an Apache project. CouchDB is not a relational database. It seems that cloud computing has spawned, or at least made popular, a new breed of database. Rather than the hierarchical, network or relational databases of yore*, we have a new paradigm: key/value pairs. [...]

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

Open source the way it ought to be. Today, Percona announced a replacement for InnoDB that improves performance and fixes bugs. The new engine is called XtraDB.

According to Vadim at Percona:

It’s 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

The release is pure GPL (v2) and commercial support is available from Percona. If percona keeps this up, they just might become the new MySQL.

The source is available from Launchpad and from Percona.

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!***!