Oracle and more – Amardeep Sidhu

After pretty long time, I logged in to one of my oldest Yahoo mail account to check for a mail. It logged me in successfully but threw a bloody message “Your account have been de-activated and all your stuff has been deleted”. It was my first internet email account. I stopped using it long time back as Gmail almost compeletly replaced Yahoo but i am still a regular user of Yahoo messenger and use this account to login. Being the first account there was so much of information stored there. I lost it all . Someone not touching yahoo at all can be told that he didn’t use Yahoo services for long but being a regular user of Yahoo messenger its so sad to lose everything like this .

Today one of my colleague was working on development of a screen in Oracle Forms to give the end user an option to schedule a job using dbms_scheduler. With the hope that i would be able to explain it properly, the whole scenario is like this:

1. User will log in to the application with his username (Lets say USER01) and password (basically every application user is a database user).
2. He is provided with a screen where he can enter details about the job and the code behind the button calls a PL/SQL procedure in the main application schema (lets say APP1) which in turn uses DBMS_SCHEDULER.CREATE_JOB to schedule the new job.
3. The ultimate task of the job is to move data from one table in the first database to a table in the second database using a DB Link.
4. There is a VPD policy applied on all the application users to restrict the view of data. Policy function uses SYS_CONTEXT to fetch some information about the logged in user. The main application user APP1 is exempted from policy and can see the whole data.

Things seem to work fine till the schedule part. But when the job runs it hits ORA-02070: database does not support operator SYS_CONTEXT in this context as SYS_CONTEXT and DB link doesn’t go together.

I did a bit of troubleshooting and came to know that the job gets created with JOB_CREATOR (a field in DBA_SCHEDULER_JOBS) as the user who is logged in (ie USER001). Now when the job runs from USER001, there is a VPD policy which is going to append a where clause to the query and there is a DB link being used, hence ORA-02070.

So the way out would be to schedule and run the job from some user that has no VPD policy applied to it. The best choice would obviously be the main application user; APP1 but as the user logs in with his own username so the job would always be created with JOB_CREATOR as USER001. After a bit of thought provoking an idea hit me:

Create a table in the APP1 schema. Now when the user schedules the job, insert the values of the parameters required to schedule the job in the table. Schedule one master job in APP1 schema which would read this table and in turn call DBMS_SCHEDULER.CREATE_JOB to schedule the job required by the user. Now as there is no policy applied on the APP1 database user so the job is not going to hit ORA-02070. The frequency of the master job can be set as per the requirements. To identify which entries in the table have been processed either keep a flag which can be updated or delete the record from the table after scheduling.

That is how it clicked in my mind at that time. Suggestions about any other better (or worse ) methods are welcome

PS: About the title: Nothing really was coming into my mind so i picked up the all three words and titled it DBMS_SCHEDULER, DBMS_RLS and SYS_CONTEXT

Since long time i have almost been writing useless posts only. Now, i guess my blog doesn’t even look like an Oracle blog. So thought about posting something related to Oracle

Day before yesterday a colleague at my workplace asked that she was running an SQL script (which contained a simple DBMS_MVIEW.REFRESH() statement to refresh an MVIEW), it ran successfully but after completion re-ran the last command run in the session. I was also puzzled and checked the SQL script but it contained simple DBMS_MVIEW.REFRESH() statement. Next try revealed that the script actually had a / (slash) in the second line (with no semi-colon at the end of the first line). Something like this (I used dbms_stats instead of dbms_mview):

exec dbms_stats.gather_table_stats(user,'EMP')
/

Now this thing, when run in SQL* Plus session can be confusing:

SCOTT@TESTING >
SCOTT@TESTING >delete emp1;
delete emp1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@TESTING >@c:\test

PL/SQL procedure successfully completed.

delete emp1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@TESTING >

There is no semicolon at the end of the first statement but it executes without that also. So the slash in the 2nd line simply re-executes the last SQL, as expected . But it does get confusing !

Previously also, on my personal blog, i wrote about performance of DishTV’s website. Today again i opened the website to recharge my account. And oh yes it didn’t  let me click any link in IE8, Firefox or Chrome. Then i uninstalled IE8 and finally it worked in IE6. After logging in, the very first message it flashed was that to ensure your security you should change your password every 15 days and it was the time to do so. I entered the same password again and thank God, it didn’t accept it (Then i removed one letter from the end, and it accepted it   ).  So i was done with the log in to enjoy the terrible experience. Website sucks completely as i wrote previously also. Just accidentally this time i concentrated on the security. Here are some screen shots from different pages of the website. See for yourself, security unlimited !

The website runs on 202.46.200.213 and this particular page was last modified on 25-04-2009

This one on 19-02-2009

Again this one on 25-04-2009

Oh…this one lies in the root

I am neither a hacker nor a web developer but can clearly see the standard of the website. Zee Group, one of the biggest in India, i guess can’t have a proper website for their service. Bullshit !

Just tried Bing and its not that impressive, in my first try . I searched for lajpat nagar, new dehi (instead of Delhi) in Bing maps & Google maps and here are the results:

Clearly, Google is smarter ! Moreover it looks like a complete copy of Google stuff (see the options on the top). Also Google has much more to offer than only search. Long way to go for Bing, i guess !