-
Notifications
You must be signed in to change notification settings - Fork 0
/
oracle.el
4457 lines (3933 loc) · 158 KB
/
oracle.el
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
;; Filename: oracle.el
;; Description: Major Mode for editing sql and pl/sql, and interacting with sqlplus.
;; Author: Stewart W. Bryson of Red Pill Analytics, LLC.
;; Maintainer: Stewart W. Bryson
;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;
;;; Requires custom, easy-menu
;;;
;; This code is two-parts borrowed, one-part original. It sprang from the desire
;; to combine the functionality of the sqlplus-mode from Jim Lange's original
;; sql-mode with the casing and indenting functionality from Josie Stauffer's
;; sqled-mode. So I pulled a lot of the code together into oracle-mode (I was
;; surprised the name wasn't already spoken for) and added some of other functionality I
;; thought was missing. Also included is some invaluable code contributed to
;; the original sql-mode by Thomas Miller of KnowledgeStorm Inc. It provides the
;; ability to interact with multiple SQL*Plus buffers.
;; sql-mode.el, Copyright (C) 1990 Free Software Foundation, Inc., and Jim Lange.
;; sqled-mode.el, Copyright (C) 2003 Josie Stauffer
;; This program is free software; you can redistribute it and/or
;; modify it under the terms of the GNU General Public License as
;; published by the Free Software Foundation; either version 2 of
;; the License, or (at your option) any later version.
;; This program is distributed in the hope that it will be
;; useful, but WITHOUT ANY WARRANTY; without even the implied
;; warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
;; PURPOSE. See the GNU General Public License for more details.
;; You should have received a copy of the GNU General Public
;; License along with this program; if not, write to the Free
;; Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
;; MA 02111-1307 USA
;;; FILE SECTIONS
;;; ============
;;;
;;; ** 1. USER OPTIONS
;;; ** 2. ORACLE-MODE
;;; ** 3. AUTO CASING AND INDENTATION FUNCTIONS
;;; ** 4. INTERACTIVE COMMENT FUNCTIONS
;;; ** 5. BULK CASING AND INDENTATION FUNCTIONS
;;; ** 6. FUNCTIONS FOR USE ON DESC-TABLE OUTPUT
;;; ** 7. MISCELLANEOUS INTERACTIVE FUNCTIONS
;;; ** 8. NON-INTERACTIVE UTILITY FUNCTIONS
;;; ** 9. CONTEXT INFORMATION
;;;; ** 10. ORACLE-MODE INTERACTING WITH SQLPLUS-MODE
;;; ** 11. SQLPLUS-MODE AND ACCOMPANYING FUNCTIONS
;;; USAGE
;;; =====
;;;
;;; If the following lines are contained in your .emacs file:
;;;
;;;(setq auto-mode-alist
;;; (append '(("\\.sql\\'" . oracle-mode)) auto-mode-alist))
;;;
;;; then Emacs should enter oracle-mode when you load a file whose name
;;; ends in ".sql"
;;;
;;; When you have entered oracle-mode, you may get more info by pressing
;;; C-h m. You may also get online help describing various functions by:
;;; C-h d <Name of function you want described>
;;;; ----------------------
;;;; ** 1. USER OPTIONS **
;;;; ----------------------
(require 'custom)
(defgroup oracle nil
"Major mode for editing SQL and PL/SQL code and interacting with SQL*Plus."
:group 'languages)
;;;=============================================================
;;; A. ORACLE-MODE options
;;;=============================================================
(defcustom oracle-file-directory "~/.oraclemode"
"Directory where oracle temp files and history files are written to."
:group 'oracle
:type 'string)
(defcustom oracle-explain-buffer "*Explain*"
"The name of the explain plan buffer created with `oracle-explain', `oracle-autotrace' and `oracle-autotrace-explain'."
:group 'oracle
:type 'string)
(defcustom oracle-append-buffer "*Query*"
"The name of the generic query buffer. This is called with `oracle-append-query'."
:group 'oracle
:type 'string)
(defcustom oracle-edit-buffer "*Edit*"
"The name of the generic buffer created when EDIT is typed in SQL*Plus."
:group 'oracle
:type 'string)
(defcustom oracle-temp-buffer "*Oratemp*"
"The name of the generic buffer used for several functions."
:group 'oracle
:type 'string)
(defcustom oracle-mode-hook nil
"*List of functions to call when Oracle Mode is invoked.
This is a good place to add Oracle environment specific bindings."
:type 'hook
:group 'oracle)
(defcustom oracle-comment-prefix "-- "
"*Prefix used by some `comment-fill` commands.
`comment-region' and `fill-comment-paragraph' insert this
string at the start of each line.
It is NOT the expression for recognizing the start of a comment
\( see `oracle-comment-start-re' )."
:type 'string
:group 'oracle)
(defcustom oracle-leftcomment-start-re "\\-\\-\\-\\|/\\*"
"*Regular expression for the start of a left-aligned comment.
`comment-region' and `fill-comment-paragraph' insert this
string at the start of each line.
It is NOT the expression for recognizing the start of a comment
\( see `oracle-comment-start-re' )."
:type 'string
:group 'oracle)
(defcustom oracle-fill-comment-prefix "/* "
"*Prefix used by some `comment-fill` commands.
`oracle-fill-comment-paragraph-prefix' and
`oracle-fill-comment-paragraph-postfix' insert this
string at the start of each line.
It is NOT the expression for recognizing the start of a comment
\( see `oracle-comment-start-re' )."
:type 'string
:group 'oracle)
(defcustom oracle-fill-comment-postfix " */"
"*Postfix used by `oracle-fill-comment-paragraph-postfix'.
This string is inserted at the end of each line by
`oracle-fill-comment-paragraph-postfix'."
:type 'string
:group 'oracle)
(defcustom oracle-fill-comment-postfix-re "[ \t]*\\*/"
"*Postfix recognized by some `fill-comment` commands.
A string matching this expression is deleted from the end
of each line when filling a comment paragraph using
`oracle-fill-comment-paragraph-prefix`."
:type 'string
:group 'oracle)
(defcustom oracle-comment-start-re
"\\(^\\(rem \\|remark \\)\\|--+ *\\w+\\|/\\*\\)"
"*Regular expression for the start of a comment.
\(ie text to be ignored for casing/indentation purposes).
The comment is considered to end at the end of the line."
:type 'string
:group 'oracle)
(defcustom oracle-group-fcn-re
"\\<\\(round\\|sum\\|max\\|min\\|count\\)\\>"
"*Regular expression for grouping functions.
Used to generate the `GROUP BY' clause in a select statement."
:type 'string
:group 'oracle)
(defcustom oracle-plsql-unit-re
"^ *\\(overriding +\\)?\\(\\(\\(member\\|constructor\\) +\\)?\\(procedure\\|function\\)\\)"
"*Regular expression for for constructing a statement navigator."
:type 'string
:group 'oracle)
(defcustom oracle-plsql-nav-ratio 3
"What ratio of the current frame should be used for the statement navigator"
:group 'oracle
:type 'number)
(defcustom oracle-plsql-nav-nlines 0
"Value for nlines to pass to `occur'"
:group 'oracle
:type 'number)
;;;=============================================================
;;; B. SQLPLUS-MODE options
;;;=============================================================
(defcustom sqlplus-keep-history nil
"If non-nil, save current session in file denoted by sqlplus-history-file when exiting."
:group 'oracle
:type 'boolean)
(defcustom sqlplus-load-history nil
"If non-nil, load the contents of sqlplus-history-file when starting."
:group 'oracle
:type 'boolean)
(defcustom sqlplus-do-commands-clear nil
"If non-nil, remove SQL*Plus commands and comments from buffer after starting.
This is only useful when sqlplus-keep-history is non-nil."
:group 'oracle
:type 'boolean)
(defcustom sqlplus-do-prompts-clear nil
"If non-nil, remove multiple SQL*Plus prompts after starting."
:group 'oracle
:type 'boolean)
(defcustom sqlplus-do-clear-dangerous-sql nil
"If non-nil, remove dangerous sql commands from the history file after loading.
This ensures that dangerous commands aren't accidentally executed.
The values in sqlplus-dangerous-sql-re determines the commands to look for."
:group 'oracle
:type 'boolean)
(defcustom sqlplus-history-file ".sqlhist"
"If non-nil, save current session to the history when exiting."
:group 'oracle
:type 'string)
(defcustom sqlplus-dangerous-sql-re "drop\\|delete\\|truncate\\|\\(identified by\\)"
"*Protect the line from auto-indentation.
If `oracle-auto-case-flag' and/or `oracle-auto-indent-flag'
is non-nil, and a string matching this re is found in the line,
the line is not indented."
:type 'string
:group 'oracle)
(defcustom sqlplus-lines-to-keep 1000
"Number of lines to keep in a SQL*Plus buffer when \\[sqlplus-drop-old-lines] is executed."
:group 'oracle
:type 'number)
(defcustom sqlplus-prompt "SQL>"
"The prompt in SQL*Plus. Should be customized to match prompt customizations in SQL*Plus."
:group 'oracle
:type 'string)
(defcustom sqlplus-dict-prefix "ALL"
"Prefix used to determine the set of views used for pulling objects from the
dictionary, i.e dba_tables, user_tables or all_tables."
:group 'oracle
:type 'string)
(defvar sqlplus-prompt-re (concat "^\\([0-9][0-9]:[0-9][0-9]:[0-9][0-9] \\)?\\(" sqlplus-prompt " \\)+")
"This allows for multi-line SQL statements in SQL*Plus.")
(defvar sqlplus-mode-syntax-table nil
"Syntax-table used for sqlplus-mode.")
(defcustom sqlplus-startup-commands
"set pause off
set tab off
set trimout on
set linesize 1000
set pause off
set pagesize 0
set colsep \" | \""
"*A string of commands that will be sent to SQL*Plus immediately after
it starts up.
Can be used as a replacement for the usual Oracle functionality of login.sql."
:group 'oracle
:type 'string)
(defcustom sqlplus-use-startup-commands nil
"If non-nil, execute sqlplus-startup-commands in each new SQL*Plus buffer when it starts."
:group 'oracle
:type 'boolean)
(defcustom sqlplus-sessions-query
"SET echo off
COLUMN status format a10
SET feedback off
SET serveroutput on
COLUMN username format a15 word_wrapped
COLUMN module format a30 word_wrapped
COLUMN action format a32 word_wrapped
COLUMN client_info format a64 word_wrapped
SELECT username, '('||sid||','||serial#||')' \"SID/Serial\", inst_id ,process, status, module, action, client_info
FROM gv$session
WHERE username IS NOT NULL
AND audsid <> SYS_CONTEXT ('USERENV', 'SESSIONID')
ORDER BY status DESC, username, module, action;
COLUMN USERNAME format a20
COLUMN sql_text format a55 word_wrapped
SET serveroutput on size 1000000
DECLARE
x NUMBER;
BEGIN
FOR x IN
( SELECT username||' ('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(logon_time,' Day HH24:MI') logon_time,
to_char(SYSDATE,' Day HH24:MI') current_time,
sql_address, last_call_et
FROM gv$session
WHERE status = 'ACTIVE'
AND audsid <> SYS_CONTEXT ('USERENV', 'SESSIONID')
AND rawtohex(sql_address) <> '00'
AND username IS NOT NULL ORDER BY last_call_et )
LOOP
FOR y IN ( SELECT MAX(decode(piece,0,sql_text,NULL)) ||
MAX(decode(piece,1,sql_text,NULL)) ||
MAX(decode(piece,2,sql_text,NULL)) ||
MAX(decode(piece,3,sql_text,NULL))
sql_text
FROM gv$sqltext_with_newlines
WHERE address = x.sql_address
AND piece < 4)
LOOP
IF ( y.sql_text NOT LIKE '%listener.get_cmd%' AND
y.sql_text NOT LIKE '%RAWTOHEX(SQL_ADDRESS)%')
THEN
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.last_call_et);
dbms_output.put_line(
REPLACE(substr( y.sql_text, 1, 250 ),'\"',NULL) );
END IF;
END LOOP;
END LOOP;
END;
/
SET feedback on"
"*SQL used for the `sqlplus-sessions' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-current-sql-query
"select sql_text
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
and sid = &sid
and serial# = &serial;"
"*SQL used from `sqlplus-current-sql' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-longops-query
"SELECT replace(opname,'''s',null) operation,
target,
(sofar/totalwork)*100 \"Percent Complete\"
FROM gv$session_longops
where sid = &sid
and serial# = &serial
order by time_remaining;"
"The query used to find long operations."
:group 'oracle
:type 'string)
(defcustom sqlplus-longops-px-query
"SELECT sid,
REPLACE(sl.opname,'''s',NULL)
operation,
sl.target,
(sl.sofar/sl.totalwork)*100 \"Percent Complete\"
FROM gv$px_session px1
JOIN gv$session_longops sl
USING (sid,serial#)
WHERE px1.qcsid IN (SELECT DISTINCT px2.qcsid
FROM gv$px_session px2
WHERE sid = &sid)
ORDER BY time_remaining;"
"The query used to find long operations fro a parallel query group."
:group 'oracle
:type 'string)
(defcustom sqlplus-waits-query
"SELECT event,
wait_class,
time_waited,
total_waits,
average_wait
FROM gv$session_event
WHERE sid=&sid
ORDER BY time_waited ASC;
SELECT event,
wait_class,
seconds_in_wait,
wait_time,
state
FROM gv$session_wait
WHERE sid=&sid
ORDER BY seconds_in_wait DESC;"
"The query used to generate the current and combined waits for a particular session."
:group 'oracle
:type 'string)
(defcustom sqlplus-waits-px-query
"SELECT event,
wait_class,
SUM(time_waited) time_waited,
SUM(total_waits) total_waits,
AVG(average_wait) average_wait
FROM gv$px_session px1
JOIN gv$session_event sl
USING (sid)
WHERE px1.qcsid IN (SELECT DISTINCT px2.qcsid
FROM gv$px_session px2
WHERE sid = &sid)
GROUP BY event,wait_class
ORDER BY time_waited;
SELECT event,
wait_class,
SUM(seconds_in_wait) seconds_in_wait,
SUM(wait_time) wait_time
FROM gv$px_session px1
JOIN gv$session_wait sl
USING (sid)
WHERE px1.qcsid IN (SELECT DISTINCT px2.qcsid
FROM gv$px_session px2
WHERE sid = &sid)
GROUP BY event,wait_class
ORDER BY seconds_in_wait;"
"The query used to generate the current and combined waits for a particular group a parallelized sessions."
:group 'oracle
:type 'string)
(defcustom sqlplus-object-ddl-query
(concat
"SELECT dbms_metadata.get_ddl(decode(object_type,'MATERIALIZED VIEW','MATERIALIZED_VIEW','DATABASE LINK','DB_LINK',object_type), object_name, owner) AS \" \"
FROM " sqlplus-dict-prefix "_objects
WHERE object_name like upper('%&object%')
AND object_type NOT LIKE upper('%body')
AND object_type NOT LIKE upper('%partition')
ORDER BY object_type;")
"*SQL used in `sqlplus-object-ddl' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-tablespace-ddl-query
(concat
"select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) as TABLESPACE_DDL
FROM dba_tablespaces
WHERE tablespace_name=upper('&object');")
"*SQL used in `sqlplus-tablespace-ddl' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-user-ddl-query
(concat
"select dbms_metadata.get_ddl('USER', username) as USER_DDL
FROM " sqlplus-dict-prefix "_users
WHERE username=upper('&object');")
"*SQL used in `sqlplus-user-ddl' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-index-ddl-query
(concat
"select dbms_metadata.get_ddl('INDEX', index_name, owner) as INDEX_DDL
FROM " sqlplus-dict-prefix "_indexes
WHERE table_name=upper('&object');")
"*SQL used in `sqlplus-index-ddl' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-desc-tab-query
"SET define on
SET echo off
SET recsep off
COLUMN dummy noprint
COLUMN type format A15
COLUMN name format A30
COLUMN infos format A45 word_wrapped
COLUMN n format a5
BREAK on col# on name on N on type
SET verify off
SET feedback off
SET timing off
SELECT '&object' as \" \" from dual;
SELECT c.col# dummy,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
substr(cm.comment$, 1, 1000) infos,
0 dummy
FROM sys.obj$ o,
sys.user$ u,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot,
sys.com$ cm
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND u.user# = o.owner#
AND o.obj# = c.obj#
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND cm.obj# = o.obj#
AND cm.col# = c.col#
AND c.col# > 0
UNION
SELECT c.col#,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
'*** PK ' || to_char(cc.pos#) || '/' || to_char(cd.cols),
1
FROM sys.obj$ o,
sys.user$ u,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot,
sys.cdef$ cd,
sys.ccol$ cc
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND u.user# = o.owner#
AND o.obj# = c.obj#
AND c.col# > 0
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND cd.obj# = o.obj#
AND cd.type# = 2
AND cd.con# = cc.con#
AND cc.obj# = o.obj#
AND cc.col# = c.col#
UNION
SELECT c.col#,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
'*** IDX ' || oi.name || decode(bitand(i.property, 1), 0, ' ', '(U) ')
|| to_char(ic.pos#) || '/' || to_char(i.cols),
3
FROM sys.obj$ o,
sys.user$ u,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot,
sys.ind$ i,
sys.obj$ oi,
sys.icol$ ic
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND u.user# = o.owner#
AND o.obj# = c.obj#
AND i.bo# = o.obj#
AND oi.obj# = i.obj#
AND ic.obj# = i.obj#
AND ic.bo# = i.bo#
AND ic.col# = c.col#
AND c.col# > 0
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND NOT EXISTS (SELECT 'x'
FROM sys.cdef$ cd,
sys.ccol$ cc,
sys.con$ co
WHERE cc.obj# = c.obj#
AND cc.col# = c.col#
AND cc.con# = cd.con#
AND co.con# = cd.con#
AND co.name = oi.name
AND cd.type# = 2)
UNION
SELECT c.col#,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
'*** IDX ' || oi.name || '(C) ' || to_char(ic.pos#)
|| '/' || to_char(i.cols),
3
FROM sys.obj$ o,
sys.user$ u,
sys.tab$ t,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot,
sys.ind$ i,
sys.obj$ oi,
sys.icol$ ic,
sys.clu$ cl,
sys.col$ clc
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND u.user# = o.owner#
AND o.obj# = c.obj#
AND c.col# > 0
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND o.obj# = t.obj#
AND t.bobj# = cl.obj#
AND clc.obj# = cl.obj#
AND clc.segcol# = c.segcol#
AND i.bo# = cl.obj#
AND oi.obj# = i.obj#
AND ic.obj# = i.obj#
AND ic.bo# = i.bo#
AND ic.col# = clc.col#
UNION
SELECT c.col#,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
' ', 0
FROM sys.obj$ o,
sys.user$ u,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND u.user# = o.owner#
AND o.obj# = c.obj#
AND c.col# > 0
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND NOT EXISTS (SELECT 'x'
FROM sys.icol$ ic
WHERE ic.bo# = c.obj#
AND ic.col# = c.col#
UNION
SELECT 'x'
FROM sys.tab$ t,
sys.clu$ cl,
sys.col$ clc
WHERE t.obj# = o.obj#
AND t.bobj# = cl.obj#
AND clc.obj# = cl.obj#
AND clc.segcol# = c.segcol#
UNION
SELECT 'x'
FROM sys.com$ cm
WHERE cm.obj# = c.obj#
AND cm.col# = c.col#)
UNION
SELECT c.col#,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
'*** FK --> ' || o2.name || '(' || c2.name || ') '
|| ltrim(to_char(cc1.pos#))
|| '/'
|| ltrim(to_char(cd1.cols)) , 2
FROM sys.obj$ o,
sys.user$ u,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot,
sys.cdef$ cd1,
sys.ccol$ cc1,
sys.ccol$ cc2,
sys.obj$ o2,
sys.col$ c2
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND o.obj# = c.obj#
AND c.col# > 0
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND cd1.obj# = o.obj#
AND cd1.con# = cc1.con#
AND cc1.obj# = c.obj#
AND cc1.col# = c.col#
AND cd1.type# = 4
AND cc2.con# = cd1.rcon#
AND cc2.obj# = cd1.robj#
AND cc2.obj# = o2.obj#
AND cc2.obj# = c2.obj#
AND cc2.col# = c2.col#
AND cc1.pos# = cc2.pos#
UNION
SELECT c.col#,
c.name,
decode(c.null$, 0, '', '*') n,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2(', 'VARCHAR2(')
|| to_char(c.length) || ')',
2, decode(c.precision#,
126, 'FLOAT',
'NUMBER' || decode(c.scale, NULL, '',
'(' || to_char(nvl(c.precision#,
(c.length - 3) * 2))
|| decode(c.scale, 0, ')',
',' || to_char(c.scale)
|| ')'))),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR(', 'VARCHAR(')
|| to_char(c.length) || ') VARYING',
12, 'DATE',
23, 'RAW' || '(' || to_char(c.length) || ')',
24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR(', 'CHAR(')
|| to_char(c.length) || ')',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, ot.name || '(REF)',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB',
114, 'BFILE',
115, 'CFILE',
121, ot.name,
122, ot.name,
123, ot.name,
to_char(c.type#)) TYPE,
'*** FK <- ' || o2.name || '(' || c2.name || ') '
|| ltrim(to_char(cc2.pos#))
|| '/'
|| ltrim(to_char(cd2.cols)) , 2
FROM sys.obj$ o,
sys.user$ u,
sys.col$ c,
sys.coltype$ ct,
sys.obj$ ot,
sys.cdef$ cd2,
sys.ccol$ cc1,
sys.ccol$ cc2,
sys.obj$ o2,
sys.col$ c2
WHERE u.name = decode(instr('&object', '.'), 0, USER,
upper(substr('&object', 1, instr('&object', '.') - 1)))
AND o.name = upper(substr('&object', instr('&object', '.') + 1))
AND cd2.robj# = o.obj#
AND cd2.rcon# = cc1.con#
AND cc1.obj# = o.obj#
AND cc1.col# = c.col#
AND o.obj# = c.obj#
AND c.col# > 0
AND c.obj# = ct.obj# (+)
AND c.col# = ct.col# (+)
AND ct.toid = ot.oid$ (+)
AND cd2.type# = 4
AND cd2.obj# = o2.obj#
AND cc2.obj# = o2.obj#
AND cc2.con# = cd2.con#
AND cc2.obj# = c2.obj#
AND cc2.col# = c2.col#
AND cc1.pos# = cc2.pos#
ORDER BY 1, 6;
SET heading on
SET feedback on
SET timing on
@login"
"*SQL used from `sqlplus-desc-tab' function."
:group 'oracle
:type 'string)
(defcustom sqlplus-desc-query
"SET define on
SET echo off
SET recsep off
SET linesize 60
prompt &object
DESC &object
SET linesize 10000"
"*SQL used from `sqlplus-desc' function."
:group 'oracle
:type 'string)
(defvar sqlplus-startup-message
(concat
"Emacs SQL*Plus Interpreter: by Stewart Bryson of Red Pill Analytics, LLC.\n"
"Based on sql-mode.el written by Jim Lange of Oracle Corporation.\n"
"Enhancements by Thomas Miller to original version are included here.\n"
"_________________________________________________________________________\n")
"Message displayed when \\[sqlplus] is executed.")
(defvar sqlplus-last-output-start nil
"In a sqlplus-mode buffer, marker for beginning of last batch of output.")
(defvar sqlplus-prompt-re nil
"In a sqlplus-mode buffer, string containing prompt text.")
(defvar sqlplus-last-process-buffer nil
"A file in oracle-mode remembers the last buffer entered.")
(defvar sqlplus-continue-pattern "^\\([0-9][0-9]:[0-9][0-9]:[0-9][0-9] \\)?[ 0-9][ 0-9][0-9][* \t][ \t]*\\| "
"In a sqlplus-mode buffer, regular expression for continuation line prompt.")
(defvar sqlplus-stack-pointer 0
"Current command recalled from history of commands.")