SQL*Plus Line Insertion June 22, 2010
Posted by mwidlake in development.Tags: SQL
4 comments
I’m quite new to SQL*Plus, having only been using it for just shy of 19 years… And today I learnt something new about it. You can over-write lines anywhere in the buffer by stating the line number:
1 select p.surname 2 ,p.first_forename 3 ,a.house_number 4 ,a.post_code 5 from person p 6 ,adres a 7* where p.addr_id=a.addr_id -- Damn, miss-spelt address in line 6 TDB> 6 ,address a TDB> l 1 select p.surname 2 ,p.first_forename 3 ,a.house_number 4 ,a.post_code 5 from person p 6 ,address a 7* where p.addr_id=a.addr_id
I use SQL*Plus a lot as (a) I am getting stuck in my ways (b) it is always available on all Oracle sites (c) I can use all those useful little scripts I’ve developed over the years. Using “edit” to fire up vi or notepad to edit the file is fine but for correcting a typo I usually type in the line number and c/mistake/correct. This other method I’ve just come across of replacing the line could be useful.
But I played around and found I could do a couple of other things. I can add lines at the end of the script {maybe not so useful as, when you do a list, you are at the end of the buffer so you just insert and type) – but I can also insert lines at the START of the script:
1 select p.surname 2 ,p.first_forename 3 ,a.house_number 4 ,a.post_code 5 from person p 6 ,address a 7* where p.addr_id=a.addr_id TDB> 8 and a.dob <sysdate-(18*365) TDB> l 1 select p.surname 2 ,p.first_forename 3 ,a.house_number 4 ,a.post_code 5 from person p 6 ,address a 7 where p.addr_id=a.addr_id 8* and a.dob <sysdate-(18*365) -- if you enter a line number a way beyond the end of the buffer, SQL*Plus -- intelligently corrects it to the next valid line number TDB> 12 order by 1,2 TDB> l 1 select p.surname 2 ,p.first_forename 3 ,a.house_number 4 ,a.post_code 5 from person p 6 ,address a 7 where p.addr_id=a.addr_id 8 and a.dob <sysdate-(18*365) 9* order by 1,2 -- And it works from the other end of the file. Only it does not replace the -- first valid line, it inserts the new line and moves all the others "down". TDB> 0 select count(*) from ( TDB> l 1 select count(*) from ( 2 select p.surname 3 ,p.first_forename 4 ,a.house_number 5 ,a.post_code 6 from person p 7 ,address a 8 where p.addr_id=a.addr_id 9 and a.dob <sysdate-(18*365) 10* order by 1,2 TDB> a ) 10* order by 1,2 ) -- script finished... TDB> / and a.dob <sysdate-(18*365) * ERROR at line 9: ORA-00904: "A"."DOB": invalid identifier -- Damn! another typo. -- I think in this case I will just go to the line and <em>C</em>hange the character - it -- is less effort than typing the whole line again. TDB> 9 9* and a.dob <sysdate-(18*365) TDB> c/a./p./ 9* and p.dob <sysdate-(18*365) DWPDV1> l 1 select count(*) from ( 2 select p.surname 3 ,p.first_forename 4 ,a.house_number 5 ,a.postcode 6 from person p 7 ,address a 8 where p.addr_id=a.addr_id 9 and p.dob <sysdate-(18*365) 10* order by 1,2 ) TDB> / Any Key> COUNT(*) ---------- 31963 1 row selected.
Up until now, to insert a new line at the start of the file I either had to save and edit it or I altered the first line and re-inserted the original. I do quite often want to insert a first line as I like to use the “select count(*) from (original_query) to test the performance of a sql statement without having the actual data sent over SQL*Net to my session. It takes that network activity out of the equation.
The other thing I do occasionally is add an explain plan statement:
TDB> l 1 select count(*) from ( 2 select p.surname 3 ,p.first_forename 4 ,a.house_number 5 ,a.post_code 6 from person p 7 ,address a 8 where p.addr_id=a.addr_id 9 and p.dob <sysdate-(18*365) 10* order by 1,2 ) TDB> -13 explain plan set statement_id='MDW' for TDB> l 1 explain plan set statement_id='MDW' for 2 select count(*) from ( 3 select p.surname 4 ,p.first_forename 5 ,a.house_number 6 ,a.post_code 7 from person p 8 ,address a 9 where p.addr_id=a.addr_id 10 and p.dob <sysdate-(18*365) 11* order by 1,2 ) TDB> / Explained. TDB>