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 !

On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don’t have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.

cat list.txt | while read a
do
echo "spooling $a"
sqlplus username/password@string <<EOF
set feed off markup html on spool on
spool /home/oracle/$a.xls
select * from $a;
spool off
set markup html off spool off
EOF
done

I didn’t see any work around for Windoze as SQLPLUS << EOF thing doesn’t seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.

Sidhu

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

Well, some information about how to go about generating and reading explain plan of a SQL query. Explain plan gives the information about the access path that Oracle is going to follow to execute your query [explain plan doesn't actually execute the query, it just tells about the access path that Oracle will follow to execute the query; that too in current session, with all the current settings, it may be totally different in another session on the very same database].

So after reading the explain plan of a particular query, we can see that is it executing efficiently ? Are the indexes being used ? and so on. Let us go through the basic setup required to use explain plan.

First of all we need to create a table called PLAN_TABLE (It is the default name used by Oracle, you can use any other name also. But then, you would need to mention the name every time you run the explain plan stuff) where explain plan will store the details of the query plan.

To create this table, run the script provided by Oracle $ORACLE_HOME/rdbms/admin/utlxplan.sql

I would like to mention the Tom Kyte approach here. In his book Effective Oracle by design, Tom suggests to create plan_table as a GLOBAL TEMPORARY TABLE with the ON COMMIT PRESERVE ROWS option and then granting all privileges on PLAN_TABLE to public. Also create a public synonym, so that everyone can use the same table.

Now when the plan table has been created, we are ready to go. Lets do EXPLAIN PLAN of a query:

For the first time, we are going to use the simplest “Hello World” query: select * from emp; So lets see…

SQL> explain plan for2  select * from emp;Explained.

This is the way to run EXPLAIN PLAN for some query. I simply write explain plan for and then the query itself. Now this Explained means Oracle has stored all the details n PLAN_TABLE and we have to read that data to see the details.

Again to view those details, Oracle provides a script called $ORACLE_HOME/rdbms/admin/utlxpls.sql. For your convenience you can ed in your session and create this script over there itself as I did with the name explain.

So its the time to see that explain plan which we have just run. As I said I run the script explain:

select * from emp;

SQL> @explainPLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.SQL>
Now we are ready to use it. Well, lets use a query based on emp and deptno tables.
select * from emp,deptwhere emp.deptno=dept.deptno;
To see explain plan of this query we write
SQL> explain plan set statement_id=’q1′ for  2  select * from emp,dept  3  where emp.deptno=dept.deptno;Explained.SQL>
This is the syntax for using explain plan. set statement_id we use to store multiple plans in the plan table. Then we write for and the SQL query we want to see explain plan for. SQL Plus will prompt “Explained” and we are back to SQL prompt. Now the plan has been stored in PLAN_TABLE table and we need to read it. For that Oracle provides us with a script called utlxpls.sql (in $ORACLE_HOME/rdbms/admin/). [I generally create the same script in bin folder, so that to run it I can simply write @utlxpls]
SQL> @utlxplsPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |----------------------------------------------------------------------------|   0 | SELECT STATEMENT             |             |       |       |       ||   1 |  NESTED LOOPS                |             |       |       |       ||   2 |   TABLE ACCESS FULL          | EMP         |       |       |       ||   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |       |       |       ||*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |       |       |       |----------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Note: rule based optimization17 rows selected.SQL>
These are the details that explain plan has provided us. Now there comes interpretation of this information. First of all to figure out what happens first, second and so on ? Well this is shown by the indentation of various steps in plan_table_output. The rightmost step (the most indented one) is executed first and then the 2nd most indented and so on. So in our case step with id 4 is executed first, then 2 and 3, and then 1. In predicate information we can see that…….
to be continued…