Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
code1
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
declare v_c numeric(5); v_tab varchar; v_cn varchar; v_cl varchar; v_col varchar; v_ind varchar; v_s varchar; cursor c_ind is select distinct cc.table_name, cc.constraint_name from (select table_name, constraint_name from (select b.table_name, b.constraint_name, max(CASE position WHEN 1 THEN column_name ELSE Null END) cnamel, max(CASE position WHEN 2 THEN column_name ELSE Null END) cname2, max(CASE position WHEN 3 THEN column_name ELSE Null END) cname3, max(CASE position WHEN 4 THEN column_name ELSE Null END) cname4, max(CASE position WHEN 5 THEN column_name ELSE Null END) cname5, max(CASE position WHEN 6 THEN column_name ELSE Null END) cname6, max(CASE position WHEN 7 THEN column_name ELSE Null END) cname7, max(CASE position WHEN 8 THEN column_name ELSE Null END) cname8, count(*) col_cnt from (select table_name, constraint_name, column_name, position from user_cons_columns) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by b.table_name, b.constraint_name ) cons where col_cnt > ALL (select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cnamel, cname2, cname3, cname4, cname5, cname6, cname7, cname8) and i.column_position <= cons.col_cnt group by i.index_name )) cc; cursor c_col is select column_name from USER_CONS_COLUMNS where table_name = v_tab and constraint_name = v_cn order by position; begin select max(to_number(substr(index_name,8,8))) into v_c from user_ind_columns uc where upper(uc.index_name) like 'IDX_FK_0%'; v_c := nvl(v_c,0); open c_ind; loop fetch c_ind into v_tab, v_cn; exit when c_ind%notfound; v_c := v_c + 1; v_col := ''; v_s := ''; open c_col; loop fetch c_col into v_cl; exit when c_col%notfound; v_col := v_col||v_s||v_cl; v_s := ','; end loop; close c_col; v_ind := 'create index IDX_FK_'||lpad(to_char(v_c),8,'0')||' on '||v_tab||'('||v_col||') TABLESPACE SUFD_INDX_TS'; execute immediate v_ind; end loop; close c_ind; end;
Absolute service time: 0,44 sec, absolute service time: 0,44 sec
edit mode
|
history
|
discussion
Error(s), warning(s):
42601: syntax error at or near "numeric"