Oracle and more - Amardeep Sidhu

Today one of my colleague was working on a simple PL/SQL procedure. Based on some logic it was returning count(*) from all_tab_columns for few tables. It gave count incorrectly for one table out of around fifty in total. He just hard coded the table name and ran it but again it showed count as zero.

Then he took the code out of procedure and wrote it in DECLARE, BEGIN, END and after running it showed the correct count. But ran as database procedure it always shows incorrectly.

Finally just as hit and trial, he gave SELECT on the TABLE to database user [Table was in different schema], used to run the procedure and everything was ok. Isn’t it bit stupid

Update: Well, it happens for a reason. Nigel Thomas pointed out in the comment. The reason is that privileges granted to a role are not seen from PL/SQL stored procedures. You need to give direct grant to the user for this or another method is to define the procedure or package with invoker rights.

Thanks Nigel

From Eddie’s blog I got a link to 3 posts on Regular Expressions on OTN written by CD. Wonderful stuff. Check out.

Part 1

Part 2

Part 3

& Thanks CD…wonderful work buddy !

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on
spool onspool c:\salgrade.xls
select * from salgrade;
spool offset markup html off
spool off

And the xls it makes shows up like:

Sidhu

Found a very interesting article on Dizwell’s blog. It was about keeping history of the SQL commands in SQL Plus on Linux. It is almost very simple. Just need to download a small utility called rlwrap from here. Its a tar.gz file. Download it, un-tar using

tar -xvf rlwrap-0.28.tar.gz

It will create a directory with the same name. cd to the directory and run

./configure

Now do

make install

(I was logged in as oracle user, then did su, but it gave some errors, finally I logged in as root and it worked fine)

Now what is left to be done is make an alias for sqlplus as

alias sqlplus='rlwrap sqlplus'

Using up/down arrows, commands can be scrolled up and down just like windows. Have a look at full article here.

Cheers

Sidhu