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>
