jump to navigation

SQL*Plus Line Insertion June 22, 2010

Posted by mwidlake in development.
Tags:
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>