Read Only Table.

I saw this post on Richard foote's blog which pointed me to this otn thread.

So i gave it a try. This is what i did.

Say I have a table as given below in production.

SQL> CREATE TABLE hx_my_read_only_table (no INTEGER CHECK(NO>0), name VARCHAR2(100));

Table created.

SQL> INSERT INTO hx_my_read_only_table VALUES(1,'KARTHICK');

1 row created.

SQL> INSERT INTO hx_my_read_only_table VALUES(2,'VIMAL');

1 row created.

SQL> INSERT INTO hx_my_read_only_table VALUES(3,'VIJAY');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM hx_my_read_only_table;

NO NAME
---------- --------------------------------------------------------------------------------
1 KARTHICK
2 VIMAL
3 VIJAY

And I want to make it read only. I will use ACCESS CONTROL to achieve it.

SQL> CREATE CONTEXT hx_my_context USING hx_my_pkg;

Context created.

SQL> CREATE OR REPLACE PACKAGE hx_my_pkg
2 AS
3 PROCEDURE hx_set_context (
4 hx_p_fld VARCHAR2,
5 hx_p_value VARCHAR2);
6
7 FUNCTION hx_get_val (
8 hx_p_fld varchar2) RETURN NUMBER;
9
10 FUNCTION hx_get_key(
11 p_schema in varchar2,
12 p_object in varchar2) RETURN VARCHAR2;
13 END hx_my_pkg;
14 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY hx_my_pkg
2 AS
3 PROCEDURE hx_set_context (
4 hx_p_fld VARCHAR2,
5 hx_p_value VARCHAR2)
6 IS
7 BEGIN
8 DBMS_SESSION.SET_CONTEXT('HX_MY_CONTEXT', hx_p_fld, hx_p_value);
9 END;
10
11 FUNCTION hx_get_val (
12 hx_p_fld varchar2) RETURN NUMBER
13 IS
14 BEGIN
15 RETURN SYS_CONTEXT('HX_MY_CONTEXT', hx_p_fld);
16 END;
17
18 FUNCTION hx_get_key(
19 p_schema in varchar2,
20 p_object in varchar2) RETURN VARCHAR2
21 IS
22 lPredicate VARCHAR2 (2000);
23 BEGIN
24 lPredicate := 'NO = SYS_CONTEXT(''HX_MY_CONTEXT'', ''NO'')';
25
26 RETURN lPredicate;
27 END;
28
29 END hx_my_pkg;
30 /

Package body created.

SQL> BEGIN
2 DBMS_RLS.ADD_POLICY (USER, 'HX_MY_READ_ONLY_TABLE', 'HX_READ_ONLY_POLICY',USER, 'hx_my_pkg.hx_get_key', 'INSERT, UPDATE, DELETE' );
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 hx_my_pkg.hx_set_context('NO',0);
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> UPDATE hx_my_read_only_table
2 SET NAME = 'RICHARD'
3 WHERE NO = 1;

0 rows updated.

SQL> DELETE FROM hx_my_read_only_table;

0 rows deleted.

SQL> INSERT INTO hx_my_read_only_table VALUES(4,'RICHARD');

1 row created.

I was expecting this one not to insert record. I have to see what the problem is.

SQL> ROLLBACK;

Rollback complete.

Mean while I restricted the insert by with this trigger. I personally hate triggers but....

SQL> CREATE OR REPLACE TRIGGER hx_my_trigger BEFORE INSERT ON hx_my_read_only_table FOR EACH ROW
2 BEGIN
3 IF :NEW.NO <> hx_my_pkg.hx_get_val('NO')
4 THEN
5 RAISE_APPLICATION_ERROR(-20001, 'Insert Not possible...');
6 END IF;
7 END;
8 /

Trigger created.

SQL> INSERT INTO hx_my_read_only_table VALUES(4,'RICHARD');

INSERT INTO hx_my_read_only_table VALUES(4,'RICHARD')
*
ERROR at line 1:
ORA-20001: Insert Not possible...
ORA-06512: at "SYSADM.HX_MY_TRIGGER", line 4
ORA-04088: error during execution of trigger 'SYSADM.HX_MY_TRIGGER'

So i have Restricted INSERT, UPDATE, DELETE on this table.

SQL> SELECT * FROM HX_MY_READ_ONLY_TABLE;

NO NAME
---------- --------------------------------------------------------------------------------
1 KARTHICK
2 VIMAL
3 VIJAY

The bridge is falling down.

Few days back tom wrote this in his blog. I felt there is lot of truth in it and was very happy to hear it from him. Today when I came into office the first mail I came across had these pictures.

This one is an unacceptable construction flaw. The mail said it has happened in Korea. Such thing may be very… very… rare event in construction business. But here in software we see lots of bridges falling down every day. After seeing this I have started to worry even more about what he said.

DML Error Logging in 10gR2

This is a cool stuff in oracle. I just tried it few days back and I was exited so thought of sharing it here.

Have you ever stuck up with a bulk insert failing on a check constraint? In such a situation we spend most of our time trying to find out the culprit rows. Oracle has come up with a great stuff by which such rows can be logged separately without disturbing the valid rows.

To put it straight by using the DML Error Logging feature you can just pick up all the invalid rows and put it separately and let the valid records go into the target table.Following is a small demonstration of this feature.

This is the source table from which the data is going to be selected.

SQL> CREATE TABLE SOURCE(NO NUMBER, NAME VARCHAR2(100))
2 /

Table created.

SQL> INSERT INTO SOURCE SELECT LEVEL, 'KARTHICK' FROM DUAL CONNECT BY LEVEL <=15
2 /

15 rows created.

This is the target table into which the data from the source table is going to be inserted.

SQL> CREATE TABLE TARGET(NO NUMBER, NAME VARCHAR2(100))
2 /

Table created.

I am creating a check constraint as follows

SQL> ALTER TABLE TARGET ADD CONSTRAINT SOURCE_CHK CHECK(NO <10)
2 /

Table altered.

Now let’s try to insert the source table data into target table.

SQL> INSERT INTO TARGET SELECT * FROM SOURCE;
INSERT INTO TARGET SELECT * FROM SOURCE
*
ERROR at line 1:
ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated

As we have a check constraint on column TARGET.NO which says NO should be less than 10, if we have any row which does not satisfy this condition then the entire insert fails. But I want the valid rows to be inserted into my target table and all the invalid rows to be logged separately. I am going to achieve this using the DML Error Logging technique.

To achieve that first we need to create a Error Log Table. The following command helps us in doing that.

SQL> BEGIN
2 DBMS_ERRLOG.create_error_log (dml_table_name => 'TARGET');
3 END;
4 /

PL/SQL procedure successfully completed.

This creates a new error log table for our TARGET table. The error log table is described below

SQL> desc err$_TARGET;

Name Null? Type
----------------------------------------- -------- ----------------------------

ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
NO VARCHAR2(4000)
NAME VARCHAR2(4000)

Now we need to use LOG ERROR INTO syntax in our INSERT INTO statement to log the error.

SQL> INSERT INTO TARGET SELECT * FROM SOURCE LOG ERRORS INTO err$_TARGET ('INSERT') REJECT LIMIT UNLIMITED
2 /

9 rows created.

Now you can observe that of the 15 rows 9 rows that satisfy the check constraints are inserted into the TARGET table.So the invalid records will be logged into the Error Log table that we have created above. Let’s have a look into the error log table.

SELECT * FROM ERR$_TARGET

ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ NO NAME
--------------------------------------------------------------------------------------------------------------------------------------------
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 10 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 11 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 12 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 13 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 14 KARTHICK
2290 ORA-02290: check constraint (SYSADM.SOURCE_CHK) violated I INSERT 15 KARTHICK

Now which this information we can easily start our trouble shooting to figure out why the rows failed the check constraint.

Do i need to repel?

I saw this posting by tom today. I felt like commenting it. And this is what i wrote.

It is not most of the time "I don't know the answer" but its "I don't know the question".

What I want?

Almost and always in the programming world it’s not what I want, it’s what they want me to want. Unclear requirements we see it every day don't we.

Why such questions?

The inability to question the authority is what leads to such questions. Some of the questions asked may not be looking for the answer but may be in the phase of trying to understand what is needed. I know it’s a wrong (very... very...) approach. But in the practical world that’s the way it is.

Don’t you here it?

Do what I say...
Don't do it that way, Do it my way...

Do I need to repel?

Is it right to say what you ask for just does not make any sense to me. If I tell that I know the next question (if the person sitting opposite to you is a genuine person) would be "Then tell me how it should be?” Do we really have answer at that moment is what matters. If we have then we may find a way out. But most of the time we know that something is wrong but we don’t know what is right. To find the right thing we need time. And that is what most of them don’t have with them. For all this as I said earlier the person should be genuine. What if he or she is a bossy person? It’s simple..."You are screwed up".

So I feel such question is born not because "I don’t know what I want" but because "I don’t know what they want me to want".

Do you prefer to end up this way?

Think about it. I don't want to end up this way. Definitely not....

Few days back we had a discussion on how to build a better team, of which one of the important aspects was communication between the team members. After seeing this article I am getting a strong feeling about it.

There are situations where we don’t even know who the person sitting next to us. Yes that’s true. There could be several reasons for that. He may not be working on the same project on which you are. Or he may be into different technology.

So one argument that rose during our discussion was if the person sitting next to us is not in your team or your project is it necessary to communicate with him (This opinion could differ based on out personal interest). I was taking the "Not necessary side".

So if the person next to you is not in the same team as that of you then the question is why he is next to you? Again there could be several reasons for that. And we may not have control over those reasons.

After seeing this article I am starting to feel it may be "Extremely necessary and important". So I think it’s totally up to us to decide on what to do. The organization can only create the atmosphere for us to mingle with one another but it can make us to.

So I think we are the one who need to decide how much it is important.

How does it work? Does it matter!!!

Ok as my profile says I am an oracle PL/SQL developer!!!

That’s what I do for my living. I write code. That is all I do most of the time. I write some SQL or PL/SQL and throw it into the database. I have never cared about what oracle does with it. Why should I its oracles headache.

To write a SQL all I need to know is the correct syntax. That’s all I don’t need any thing else. Even for that I have SQL Reference to help me. May be in need the ability to think in Set’s. So I know SET... I know what is a JOIN and UNION.

Why should I care what oracle does with the SQL that I submitted. Why do I have to care about how it stores my data or how it gets me what I need? Why... we have the DBA guys to worry about that.

Do you feel the same way as I did? Do you? I hope the answer is not YES if it is then you are going down, down from day one. Yes that’s right!! No second thoughts you are going down... Down to hell!!

Have you heard about Bind Variables? If the answer is NO then you are not going down to hell as you are already living in there.

To know the importance of Bind Variable it’s essential to know how Oracle Works. What it does with the so called sophisticated code that you wrote.

Not only that, we need to know lot more. How the data are organized and stored, how they are retrieved. To put it in one word know the oracle database "Architecture".

A nice place to start with would be here.

http://blogs.ittoolbox.com/bi/confessions/archives/post-index-how-oracle-works-10605

I found it easy and interesting to read. The way the author has written is like a story narration. I like stories and "How oracle works" is a great story.

Once you understand all of this you will really come to know how much of your code is actually sophisticated.

So happy reading…

It's the Initiation that matters...

Taking Initiation has always been my biggest problem. Because once I get started I put lot of effort and dedication into it to get it done and to do it in a nice way. But starting it is the biggest problem. By “It” I mean any thing and every thing.

Take it to read some thing may be a book or to do a presentation or getting my ideas into execution. I always find it difficult to get that initial inspiration. Once it is there it’s always like sky is the limit. It starts to flow and I love it. I guess lots of people go through that state. Not sure if they do.