Archive for the ‘SQL’ Category
Delete Delete Faster Faster ;)
2-3 days ago, I came across a code, intended to make delete faster. Just have a look
. . . LOOP SELECT COUNT (1) INTO v_cnt FROM table1 WHERE ROWNUM < 2; IF v_cnt = 0 THEN EXIT; END IF; DELETE FROM table1 WHERE ROWNUM < 1000; COMMIT; v_cnt := 0; END LOOP; . . .
Missing grants
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
Learning Regular Expressions
From Eddie’s blog I got a link to 3 posts on Regular Expressions on OTN written by CD. Wonderful stuff. Check out.
& Thanks CD…wonderful work buddy !
Shell script to spool a no of tables into .xls files…
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
Spool to a .xls (excel) file…
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