DISCONNECT

You can return from SQL*Plus to the operating system using exit or quit:

 

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

UNIX>

 

SQL> quit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

UNIX>

 

You can also use disconnect, which terminates your database session but leaves you in SQL*Plus:

 

SQL> show user

USER is "ORACLE"

SQL> disconnect

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> show user

USER is ""

SQL>

 

This lets you enter and/or modify SQL*Plus commands but does not allow you to run them:

 

SQL> select sysdate from dual

  2  /

SP2-0640: Not connected

SQL> c/sysdate/sysdate + 1/

  1* select sysdate + 1 from dual

SQL> /

SP2-0640: Not connected

SQL>

 

To do that, you have to reconnect to the database:

 

SQL> show user

USER is ""

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> l

  1* select sysdate + 1 from dual

SQL> /

 

SYSDATE+1

---------

17-FEB-12

 

SQL>

Oracle 9 Merge Statement

This example shows the Oracle 9 version of the merge statement. We start with a master table containing 2 rows. The 1st row is for Andrew, who has a salary of £10000. The 2nd row is for Colin, who has a salary of £13000.

SQL> create table master
  2  (first_name varchar2(10),
  3  salary    number)
  4  /

Table created.

SQL> insert into master
  2  values ('Andrew', 10000)
  3  /

1 row created.

SQL> insert into master
  2  values ('Colin', 13000)
  3  /

1 row created.

SQL> select * from master
  2  order by first_name
  3  /

FIRST_NAME     SALARY
---------- ----------
Andrew          10000
Colin           13000

SQL>

Then we create a table of updates. The 1st update changes Andrew's salary to £11000. The 2nd update adds a new employee called Brian, who has a salary of £12000.

SQL> create table updates
  2  (first_name varchar2(10),
  3  salary    number)
  4  /

Table created.

SQL> insert into updates
  2  values ('Andrew', 11000)
  3  /

1 row created.

SQL> insert into updates
  2  values ('Brian', 12000)
  3  /

1 row created.

SQL> select * from updates
  2  order by first_name
  3  /

FIRST_NAME     SALARY
---------- ----------
Andrew          11000
Brian           12000

SQL>

The merge statement applies the updates. There is no row for Colin in the updates table so his salary does not change:

SQL> merge into master m
  2  using updates u
  3  on (m.first_name = u.first_name)
  4  when matched then
  5  update set m.salary = u.salary
  6  when not matched then
  7  insert (first_name, salary)
  8  values (u.first_name, u.salary)
  9  /

2 rows merged.

SQL> select * from master
  2  order by first_name
  3  /

FIRST_NAME     SALARY
---------- ----------
Andrew          11000
Brian           12000
Colin           13000

SQL>

When Did a Non-Existent Rowid Cause an ORA-00600?

I was looking through some old notes today. I found some which showed a query trying to use a non-existent rowid to access a table. This caused an ORA-00600 with the following arguments:
[2846], [6], [77249], [1], [51200], [], [], []
There was no date on the notes nor any mention of an Oracle version but I decided to try it out
on an Oracle 10 database. First I created a table with 1 row and checked its rowid:

SQL> create table andrews_table
  2  as select 1 col1 from dual
  3  /

Table created.

SQL> select rowid, col1 from andrews_table
  2  /

ROWID                    COL1
------------------ ----------
AAAMl6AABAAAOtqAAA          1

SQL>

Then I checked that I could use the rowid to retrieve the row:

SQL> select col1 from andrews_table
  2  where rowid = 'AAAMl6AABAAAOtqAAA'
  3  /

      COL1
----------
         1

SQL>

Then I made a small change to the rowid and tried again:

SQL> l
  1  select col1 from andrews_table
  2* where rowid = 'BAAMl6AABAAAOtqAAA'
SQL> /
select col1 from andrews_table
                 *
ERROR at line 1:
ORA-01410: invalid ROWID

SQL>

This only produced an ORA-01410 error message. I looked in the alert log but there was no sign of an ORA-00600 message there either. So I assume that the ORA-00600 messages were produced by a bug which has since been fixed.

ORA-00027

In the SQL*Plus session below, I connect as SYS to an Oracle 10 database. Then I show that there is only one user logged on as SYS i.e. me. I note the sid and serial# and try to use them to kill my current session. Oracle does not allow this and displays an ORA-00027:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select sid, serial# from v$session
  2  where username = 'SYS'
  3  /

       SID    SERIAL#
---------- ----------
       159          5

SQL> alter system kill session '159,5';
alter system kill session '159,5'
*
ERROR at line 1:
ORA-00027: cannot kill current session

SQL>

SQL*Plus ASCII and CHR Functions

This example was run on Oracle 10 on Linux. The chr function takes a number and displays (if it can) the character whose binary equivalent corresponds to that number. If you supply a negative number you get an error:

SQL> select chr(-1) from dual
  2  /
select chr(-1) from dual
       *
ERROR at line 1:
ORA-01426: numeric overflow

SQL>

chr(0) is not null, at least not in an Oracle database:

SQL> select 'CHR(0) is null' from dual
  2  where chr(0) is null
  3  /

no rows selected

SQL> select 'CHR(0) is not null' from dual
  2  where chr(0) is not null
  3  /

'CHR(0)ISNOTNULL'
------------------
CHR(0) is not null

SQL>

chr(9) is the tab delimiter:

SQL> select 'X'||'Y'||'Z' from dual
  2  /

'X'
---
XYZ

SQL> select 'X'||chr(9)||'Y'||chr(9)||'Z' from dual
  2  /

'X'||
-----
X    Y    Z

SQL>

The character with a binary value of 65 is the letter A:

SQL> select chr(65) from dual
  2  /

C
-
A

SQL>

The ascii function takes a character and displays its ASCII representation.
So, if we check the ASCII value of A, it should be 65:

SQL> select ascii('A') from dual
  2  /

ASCII('A')
----------
        65

SQL>

The PL/SQL below sets a variable i to each of the numbers from 0 through 255. It passes each value of i as a parameter to chr and tries to display the result. Then it checks that ascii(chr(i)) = i. Note the use of chr(10) to provide a line feed at the end of the 2nd dbms_output.put_line statement. Also note how chr(10) creates an extra line feed in the output:

SQL> set serveroutput on
SQL> begin
  2  for i in 0..255 loop
  3  dbms_output.put_line('chr('||i||') = '||chr(i));
  4  dbms_output.put_line('ascii(chr('||i||')) = '||ascii(chr(i))||chr(10));
  5  end loop;
  6  end;
  7  /
chr(0) =
ascii(chr(0)) = 0

chr(1) =
ascii(chr(1)) = 1

chr(2) =
ascii(chr(2)) = 2

chr(3) =
ascii(chr(3)) = 3

chr(4) =
ascii(chr(4)) = 4

chr(5) =
ascii(chr(5)) = 5

chr(6) =
ascii(chr(6)) = 6

chr(7) = 
ascii(chr(7)) = 7

chr(8) =
ascii(chr(8)) = 8

chr(9) =    
ascii(chr(9)) = 9

chr(10) =

ascii(chr(10)) = 10

chr(11) =

ascii(chr(11)) = 11

chr(12) =
ascii(chr(12)) = 12

chr(13) =
ascii(chr(13)) = 13

chr(14) =
ascii(chr(14)) = 14

chr(15) =
ascii(chr(15)) = 15

chr(16) =
ascii(chr(16)) = 16

chr(17) =
ascii(chr(17)) = 17

chr(18) =
ascii(chr(18)) = 18

chr(19) =
ascii(chr(19)) = 19

chr(20) =
ascii(chr(20)) = 20

chr(21) =
ascii(chr(21)) = 21

chr(22) =
ascii(chr(22)) = 22

chr(23) =
ascii(chr(23)) = 23

chr(24) =
ascii(chr(24)) = 24

chr(25) =
ascii(chr(25)) = 25

chr(26) =
ascii(chr(26)) = 26

chr(27) =
ascii(chr(27)) = 27

chr(28) =
ascii(chr(28)) = 28

chr(29) =
ascii(chr(29)) = 29

chr(30) =
ascii(chr(30)) = 30

chr(31) =
ascii(chr(31)) = 31

chr(32) =
ascii(chr(32)) = 32

chr(33) = !
ascii(chr(33)) = 33

chr(34) = "
ascii(chr(34)) = 34

chr(35) = #
ascii(chr(35)) = 35

chr(36) = $
ascii(chr(36)) = 36

chr(37) = %
ascii(chr(37)) = 37

chr(38) = &
ascii(chr(38)) = 38

chr(39) = '
ascii(chr(39)) = 39

chr(40) = (
ascii(chr(40)) = 40

chr(41) = )
ascii(chr(41)) = 41

chr(42) = *
ascii(chr(42)) = 42

chr(43) = +
ascii(chr(43)) = 43

chr(44) = ,
ascii(chr(44)) = 44

chr(45) = -
ascii(chr(45)) = 45

chr(46) = .
ascii(chr(46)) = 46

chr(47) = /
ascii(chr(47)) = 47

chr(48) = 0
ascii(chr(48)) = 48

chr(49) = 1
ascii(chr(49)) = 49

chr(50) = 2
ascii(chr(50)) = 50

chr(51) = 3
ascii(chr(51)) = 51

chr(52) = 4
ascii(chr(52)) = 52

chr(53) = 5
ascii(chr(53)) = 53

chr(54) = 6
ascii(chr(54)) = 54

chr(55) = 7
ascii(chr(55)) = 55

chr(56) = 8
ascii(chr(56)) = 56

chr(57) = 9
ascii(chr(57)) = 57

chr(58) = :
ascii(chr(58)) = 58

chr(59) = ;
ascii(chr(59)) = 59

chr(60) = <
ascii(chr(60)) = 60

chr(61) = =
ascii(chr(61)) = 61

chr(62) = >
ascii(chr(62)) = 62

chr(63) = ?
ascii(chr(63)) = 63

chr(64) = @
ascii(chr(64)) = 64

chr(65) = A
ascii(chr(65)) = 65

chr(66) = B
ascii(chr(66)) = 66

chr(67) = C
ascii(chr(67)) = 67

chr(68) = D
ascii(chr(68)) = 68

chr(69) = E
ascii(chr(69)) = 69

chr(70) = F
ascii(chr(70)) = 70

chr(71) = G
ascii(chr(71)) = 71

chr(72) = H
ascii(chr(72)) = 72

chr(73) = I
ascii(chr(73)) = 73

chr(74) = J
ascii(chr(74)) = 74

chr(75) = K
ascii(chr(75)) = 75

chr(76) = L
ascii(chr(76)) = 76

chr(77) = M
ascii(chr(77)) = 77

chr(78) = N
ascii(chr(78)) = 78

chr(79) = O
ascii(chr(79)) = 79

chr(80) = P
ascii(chr(80)) = 80

chr(81) = Q
ascii(chr(81)) = 81

chr(82) = R
ascii(chr(82)) = 82

chr(83) = S
ascii(chr(83)) = 83

chr(84) = T
ascii(chr(84)) = 84

chr(85) = U
ascii(chr(85)) = 85

chr(86) = V
ascii(chr(86)) = 86

chr(87) = W
ascii(chr(87)) = 87

chr(88) = X
ascii(chr(88)) = 88

chr(89) = Y
ascii(chr(89)) = 89

chr(90) = Z
ascii(chr(90)) = 90

chr(91) = [
ascii(chr(91)) = 91

chr(92) = \
ascii(chr(92)) = 92

chr(93) = ]
ascii(chr(93)) = 93

chr(94) = ^
ascii(chr(94)) = 94

chr(95) = _
ascii(chr(95)) = 95

chr(96) = `
ascii(chr(96)) = 96

chr(97) = a
ascii(chr(97)) = 97

chr(98) = b
ascii(chr(98)) = 98

chr(99) = c
ascii(chr(99)) = 99

chr(100) = d
ascii(chr(100)) = 100

chr(101) = e
ascii(chr(101)) = 101

chr(102) = f
ascii(chr(102)) = 102

chr(103) = g
ascii(chr(103)) = 103

chr(104) = h
ascii(chr(104)) = 104

chr(105) = i
ascii(chr(105)) = 105

chr(106) = j
ascii(chr(106)) = 106

chr(107) = k
ascii(chr(107)) = 107

chr(108) = l
ascii(chr(108)) = 108

chr(109) = m
ascii(chr(109)) = 109

chr(110) = n
ascii(chr(110)) = 110

chr(111) = o
ascii(chr(111)) = 111

chr(112) = p
ascii(chr(112)) = 112

chr(113) = q
ascii(chr(113)) = 113

chr(114) = r
ascii(chr(114)) = 114

chr(115) = s
ascii(chr(115)) = 115

chr(116) = t
ascii(chr(116)) = 116

chr(117) = u
ascii(chr(117)) = 117

chr(118) = v
ascii(chr(118)) = 118

chr(119) = w
ascii(chr(119)) = 119

chr(120) = x
ascii(chr(120)) = 120

chr(121) = y
ascii(chr(121)) = 121

chr(122) = z
ascii(chr(122)) = 122

chr(123) = {
ascii(chr(123)) = 123

chr(124) = |
ascii(chr(124)) = 124

chr(125) = }
ascii(chr(125)) = 125

chr(126) = ~
ascii(chr(126)) = 126

chr(127) = 
ascii(chr(127)) = 127

chr(128) = ?
ascii(chr(128)) = 128

chr(129) = ?
ascii(chr(129)) = 129

chr(130) = ?
ascii(chr(130)) = 130

chr(131) = ?
ascii(chr(131)) = 131

chr(132) = ?
ascii(chr(132)) = 132

chr(133) = ?
ascii(chr(133)) = 133

chr(134) = ?
ascii(chr(134)) = 134

chr(135) = ?
ascii(chr(135)) = 135

chr(136) = ?
ascii(chr(136)) = 136

chr(137) = ?
ascii(chr(137)) = 137

chr(138) = ?
ascii(chr(138)) = 138

chr(139) = ?
ascii(chr(139)) = 139

chr(140) = ?
ascii(chr(140)) = 140

chr(141) = ?
ascii(chr(141)) = 141

chr(142) = ?
ascii(chr(142)) = 142

chr(143) = ?
ascii(chr(143)) = 143

chr(144) = ?
ascii(chr(144)) = 144

chr(145) = ?
ascii(chr(145)) = 145

chr(146) = ?
ascii(chr(146)) = 146

chr(147) = ?
ascii(chr(147)) = 147

chr(148) = ?
ascii(chr(148)) = 148

chr(149) = ?
ascii(chr(149)) = 149

chr(150) = ?
ascii(chr(150)) = 150

chr(151) = ?
ascii(chr(151)) = 151

chr(152) = ?
ascii(chr(152)) = 152

chr(153) = ?
ascii(chr(153)) = 153

chr(154) = ?
ascii(chr(154)) = 154

chr(155) = ?
ascii(chr(155)) = 155

chr(156) = ?
ascii(chr(156)) = 156

chr(157) = ?
ascii(chr(157)) = 157

chr(158) = ?
ascii(chr(158)) = 158

chr(159) = ?
ascii(chr(159)) = 159

chr(160) =
ascii(chr(160)) = 160

chr(161) = !
ascii(chr(161)) = 161

chr(162) = ?
ascii(chr(162)) = 162

chr(163) = #
ascii(chr(163)) = 163

chr(164) = ?
ascii(chr(164)) = 164

chr(165) = Y
ascii(chr(165)) = 165

chr(166) = |
ascii(chr(166)) = 166

chr(167) = ?
ascii(chr(167)) = 167

chr(168) = ?
ascii(chr(168)) = 168

chr(169) = ?
ascii(chr(169)) = 169

chr(170) = ?
ascii(chr(170)) = 170

chr(171) = <
ascii(chr(171)) = 171

chr(172) = ?
ascii(chr(172)) = 172

chr(173) = -
ascii(chr(173)) = 173

chr(174) = ?
ascii(chr(174)) = 174

chr(175) = ?
ascii(chr(175)) = 175

chr(176) = ?
ascii(chr(176)) = 176

chr(177) = ?
ascii(chr(177)) = 177

chr(178) = ?
ascii(chr(178)) = 178

chr(179) = ?
ascii(chr(179)) = 179

chr(180) = '
ascii(chr(180)) = 180

chr(181) = ?
ascii(chr(181)) = 181

chr(182) = ?
ascii(chr(182)) = 182

chr(183) = ?
ascii(chr(183)) = 183

chr(184) = ?
ascii(chr(184)) = 184

chr(185) = ?
ascii(chr(185)) = 185

chr(186) = ?
ascii(chr(186)) = 186

chr(187) = >
ascii(chr(187)) = 187

chr(188) = ?
ascii(chr(188)) = 188

chr(189) = ?
ascii(chr(189)) = 189

chr(190) = ?
ascii(chr(190)) = 190

chr(191) = ?
ascii(chr(191)) = 191

chr(192) = A
ascii(chr(192)) = 192

chr(193) = A
ascii(chr(193)) = 193

chr(194) = A
ascii(chr(194)) = 194

chr(195) = ?
ascii(chr(195)) = 195

chr(196) = A
ascii(chr(196)) = 196

chr(197) = ?
ascii(chr(197)) = 197

chr(198) = ?
ascii(chr(198)) = 198

chr(199) = C
ascii(chr(199)) = 199

chr(200) = E
ascii(chr(200)) = 200

chr(201) = E
ascii(chr(201)) = 201

chr(202) = E
ascii(chr(202)) = 202

chr(203) = E
ascii(chr(203)) = 203

chr(204) = I
ascii(chr(204)) = 204

chr(205) = I
ascii(chr(205)) = 205

chr(206) = I
ascii(chr(206)) = 206

chr(207) = I
ascii(chr(207)) = 207

chr(208) = ?
ascii(chr(208)) = 208

chr(209) = ?
ascii(chr(209)) = 209

chr(210) = O
ascii(chr(210)) = 210

chr(211) = O
ascii(chr(211)) = 211

chr(212) = O
ascii(chr(212)) = 212

chr(213) = ?
ascii(chr(213)) = 213

chr(214) = O
ascii(chr(214)) = 214

chr(215) = ?
ascii(chr(215)) = 215

chr(216) = ?
ascii(chr(216)) = 216

chr(217) = U
ascii(chr(217)) = 217

chr(218) = U
ascii(chr(218)) = 218

chr(219) = U
ascii(chr(219)) = 219

chr(220) = U
ascii(chr(220)) = 220

chr(221) = Y
ascii(chr(221)) = 221

chr(222) = ?
ascii(chr(222)) = 222

chr(223) = ?
ascii(chr(223)) = 223

chr(224) = a
ascii(chr(224)) = 224

chr(225) = a
ascii(chr(225)) = 225

chr(226) = a
ascii(chr(226)) = 226

chr(227) = ?
ascii(chr(227)) = 227

chr(228) = a
ascii(chr(228)) = 228

chr(229) = ?
ascii(chr(229)) = 229

chr(230) = ?
ascii(chr(230)) = 230

chr(231) = c
ascii(chr(231)) = 231

chr(232) = e
ascii(chr(232)) = 232

chr(233) = e
ascii(chr(233)) = 233

chr(234) = e
ascii(chr(234)) = 234

chr(235) = e
ascii(chr(235)) = 235

chr(236) = i
ascii(chr(236)) = 236

chr(237) = i
ascii(chr(237)) = 237

chr(238) = i
ascii(chr(238)) = 238

chr(239) = i
ascii(chr(239)) = 239

chr(240) = ?
ascii(chr(240)) = 240

chr(241) = ?
ascii(chr(241)) = 241

chr(242) = o
ascii(chr(242)) = 242

chr(243) = o
ascii(chr(243)) = 243

chr(244) = o
ascii(chr(244)) = 244

chr(245) = ?
ascii(chr(245)) = 245

chr(246) = o
ascii(chr(246)) = 246

chr(247) = ?
ascii(chr(247)) = 247

chr(248) = ?
ascii(chr(248)) = 248

chr(249) = u
ascii(chr(249)) = 249

chr(250) = u
ascii(chr(250)) = 250

chr(251) = u
ascii(chr(251)) = 251

chr(252) = u
ascii(chr(252)) = 252

chr(253) = y
ascii(chr(253)) = 253

chr(254) = ?
ascii(chr(254)) = 254

chr(255) = y
ascii(chr(255)) = 255


PL/SQL procedure successfully completed.

SQL>