2-3 days ago, I came across a code, intended to make delete faster. Just have a look ;) [sql]. . . 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; . . .[/sql] Comments Comment by Vaibhav on 2009-10-10 07:58:17 +0530 What is the joke here? ...
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. ...
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 ! Comments Comment by Tyler on 2008-02-28 07:51:45 +0530 I’ve been working with regular expressions in PL/SQL a lot lately and the one tool that’s helped me more than any other is Regex Buddy (http://www.regexbuddy.com/). Yeah, it’s not free, but it was the best $40 I’ve spent. There are free options, like regex coach and a few free online options, but the problem is you have to find one that matches the Oracle specific Posix ERE format. Regex buddy even has a drop-down for Oracle syntax (http://www.regexbuddy.com/oracle.html). I have no affiliation with this tool whatsoever, just found it to be exceptionally useful. ...
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. ...
A small tip, I read on OTN about spooling to a .xls (excel) file: It goes like this [sourcecode language=‘css’]set feed off markup html on spool onspool c:\salgrade.xls select * from salgrade; spool offset markup html off spool off[/sourcecode] And the xls it makes shows up like: Sidhu Comments Comment by hemant on 2007-06-26 16:44:00 +0530 hi i am working for a bank and we are using 10g. i am very raw at the oracle and have just started teaching myself through a book i have. we have many reports devised by our vendor still we need some that r not available. so we wanted the data to be exported in xl wherein i could manipulate to data to our need. abt this spooling thing. i have copied down ur script and want to test it… but how do i access the shell prompt(do not know unix either) thanks for the help extend it bit further for me please hemu ...
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 [sourcecode language=‘css’] tar -xvf rlwrap-0.28.tar.gz [/sourcecode] It will create a directory with the same name. cd to the directory and run [sourcecode language=‘css’] ./configure [/sourcecode] ...