LewisC’s An Expert’s Guide To Oracle Technology

Ok, I know you you’ve heard of them. Heck, in some cases, you’ve been beaten over the head with them. So why   aren’t you using them?

I’ve heard some people saying that it’s easier to code without bind variables. Really? Concatenating strings,   potentially with embedded quotes and/or varying formats, is easier than using bind variables?

I got an email the other day from a reader who asked to remain anonymous. He said that he read that he should use   bind variables and understood why but wasn’t sure he understood the concept of actually implementing code with   bind variables.

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.

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.

Recently I posted an article about ORA_Tweet, an Oracle Twitter Client. I was asked by someone reading the code why I put several variables in the BODY of the package rather than the SPEC. The question was posed something like this:

Why not put the variables in the package spec where they are modifiable? That would involve less maintenance.

I specifically put them in the body so that they are not modifiable. I don’t see public global variables as a particularly good thing. To understand why, let’s take a look at the benefits of a package.

First, you can group logical functions and procedures together.

From the database geek.

Update March 29, 2009:  PL/JSON is now available as an open source project (MIT License).  Read the FAQ at http://oracleoss.com

Do you use JSON? Do you use Oracle? How would you like to use JSON in Oracle? How would you like to store it, generate it and read it? I am creating a new JSON data type that will work like XMLType.

I am assuming you are familiar with Oracle (or you probably wouldn’t be reading this blog). If you are not familiar with JSON it is JavaScript Object Notation, a lightweight data-interchange specification. Think of it as a replacement to XML.

Get the latest source from the ORA Tweet project page at sourceforge.

I had some free time this last Saturday night (after the family was all in bed) and I wanted to do a little mini-project. I don’t get to do enough PL/SQL anymore so I like to look for utilities and smaller fun things to code. While I was thinking about it, I was also following along on twitter to the posts that the people I follow had recently made.

I decided to put together a twitter procedure that would let me make posts from the database.

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.

A mathematician, an accountant and an economist apply for the same job. The interviewer calls in the mathematician and asks “What do two plus two equal?”

The mathematician replies “Four.”

The interviewer asks “Four, exactly?” The mathematician looks at the interviewer incredulously and says “Yes, four, exactly.”

Then the interviewer calls in the accountant and asks the same question “What do two plus two equal?” The accountant says “On average, four – give or take ten percent, but on average, four.”

Then the interviewer calls in the economist and poses the same question “What do two plus two equal?”

The economist gets up, locks the door, closes the shade, sits down next to the interviewer and says “What do you want it to equal?”

And now for something completely different:

What is AQ?

Advanced Queues, or AQ, is Oracle’s messaging solution.

Welcome to my blog. My name is Lewis R Cunningham and I’m an Oracle geek. I love playing with Oracle, working with Oracle and sharing what I know about Oracle. In my travels as a consultant and employee, I have found that, in the Oracle world at least, managers, project leads, etc. often have to make decisions regarding their projects, employees and day to day operations with very little, and/or erroneous, information. These decisions often negatively impact the business bottom line and ongoing projects. The lack of information can be caused by an incomplete team, a lack of communication inside the team or even ego or attitude problems.

My goal with this blog will be to remove some of the mystery surrounding Oracle technology and to describe ways to integrate that technology in various projects and business areas.

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.

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

That was excellent!

I was having a little trouble when I was setting p_table_owner to a schema other than my own (I even have DBA and the select on all_tab_columns was successful).

Statement:

v_lcr.set_value(‘new’, ‘first_name’…

was throwing that ‘first_name’ did not exist in the LCR.

Once I copied the table to my own schema, works great!

Good for now – exactly what I needed – trying to develop the DML_Handlers before Streams is up and running.

That was excellent!

I was having a little trouble when I was setting p_table_owner to a schema other than my own (I even have DBA and the select on all_tab_columns was successful).

Statement:

v_lcr.set_value(‘new’, ‘first_name’…

was throwing that ‘first_name’ did not exist in the LCR.

Once I copied the table to my own schema, works great!

Good for now – exactly what I needed – trying to develop the DML_Handlers before Streams is up and running.

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

Thank you very much for sharing the information of the book,in fact i was depressed by SOA..when boss told me that the new project needed SOA,in my mind,there is a big question…why ?what and how? boss said to me,you must designed it,and big questions appeared…but i cant say that boss,i dont know how to do it and why it is needed… so i searched much information about SOA,in fact,this is not a software or ide,just a thought… later i went to a meeting about SOA,and learned something

1.w

Oracle Modernization Solutions
A practical guide to planning and implementing SOA integration and Re-architecting to an Oracle platform
Authors: Tom Laszewski, Jason Williamson

Co-Authors: Mark Rakhmilevich, Michael Oara, Prakash Nauduri

Publisher…

LewisC’s An Expert’s Guide to Oracle Technology
For those of you who know Oracle Streams and might be looking for something a bit different, try a short term job in Mexico. AltiusPAR Solutions is looking for help with an Oracle Streams Replication I…

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

thank you. i’ve installed it and i have SQL command line but in your article above it says i should have SQL plus, how would i install that

? or does it come with the link that u told me to insall?

!***! Entry Link: Introduction to Basic SQL, Part 1!***!

Turns out that it was less than 1% of the work force. In this economy, I think they are doing a good job keeping people employed.

Of course, that doesn’t help those who were laid off. Hopefully, they all find jobs soon.

LewisC

!***! Entry Link: Oracle Announces Layoffs!***!

Thank you. Comments like that are exactly why I do this.

LewisC

!***! Entry Link: 10 Tips For Complete Database Newbies!***!

Wow. I’ve been toying around the idea of learning Oracle. Have read few manuals and tried my hands on 9i. But then later I moved on to .Net and now I’m back to Oracle and am looking to improve my hands on 10g. And after going thtrough your blog, it’s kinda motivating and guiding. Nice posts. Thanks a lot Lewis.

!***! Entry Link: <a href=”http://it.toolbox.com/blogs/oracle-guide/10-tips-for-complete-database-newbies-24

Fast forward a couple of years and looks like Oracle is going through another big round of layoffs… I’ve heard anywhere from 500 to 8,000 will be cut in the next 2 months. There’s a lot of static about it on the web rt now, but here?s a post that helps to sort the facts from rumors about the Oracle layoffs

!***! Entry Link:http://it.toolbox.com/blogs/oracle-guide/oracle-anno

Fast forward a couple of years and here we are with Oracle making another round of layoffs following a merger (BEA). There’s a lot of static about it on the web and it’s been hard to find solid info, so I posted an article that helps to sort out the fact from the rumors surrounding the oracle layoffs

!***! Entry Link: <a href=”http://it.toolbox.com/blogs/oracle-guide/orac