I want to create several index on the Oracle table which has these columns TRX_DATE,CUSTOMER_ID,time_stamp,username,password,address using this query:
declare
type rtype is table of varchar(50);
cols rtype := rtype('time_stamp', 'trx_date',concat('TRX_DATE'||'-'||'CUSTOMER_ID') ,'trx_time');
t_name varchar(100) := upper('test');
table_space varchar(100) := 'users';
col varchar(50);
ix_name varchar(50);
begin
for j in 1..cols.count loop
col := cols(j);
ix_name := col || '123_ix';
execute immediate 'create index ' || ix_name || ' ON ' || t_name || '(' || col || ') local unusable';
for i in (select partition_name from user_tab_partitions where table_name=t_name) loop
execute immediate 'alter index ' || ix_name || ' rebuild partition ' || i.partition_name || ' tablespace ' || table_space || ' parallel(degree 2)';
end loop;
end loop;
end;
When I run the above query, I receive this error:
Error report -
ORA-00969: missing ON keyword
ORA-06512: at line 12
00969. 00000 - "missing ON keyword"
*Cause:
*Action:
Moreover, when I change that query to this one:
declare
type rtype is table of varchar(50);
cols rtype := rtype('time_stamp', 'trx_date','TRX_DATE'||'CUSTOMER_ID' ,'trx_time');
t_name varchar(100) := upper('test');
table_space varchar(100) := 'users';
col varchar(50);
ix_name varchar(50);
begin
for j in 1..cols.count loop
col := cols(j);
ix_name := col || '123_ix';
execute immediate 'create index ' || ix_name || ' ON ' || t_name || '(' || col || ') local unusable';
for i in (select partition_name from user_tab_partitions where table_name=t_name)
loop execute immediate 'alter index ' || ix_name || ' rebuild partition ' || i.partition_name || ' tablespace ' || table_space || ' parallel(degree 2)'; end loop; end loop; end;
When run the above query,receive this error:
Error report -
ORA-00904: "TRX_DATECUSTOMER_ID": invalid identifier
ORA-06512: at line 12
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Would you please guide me how to solve that query?
Any help is really appreciated.
You're being too optimistic, executing statement you didn't verify. Therefore: compose the statement first, display it (so that you could see what you're actually trying to do) and then - once it is verfied - execute it (instead of displaying it).
SQL> DECLARE
2 TYPE rtype IS TABLE OF VARCHAR (50);
3
4 cols rtype
5 := rtype ('time_stamp',
6 'trx_date',
7 'TRX_DATE' || '-' || 'CUSTOMER_ID',
8 'trx_time');
9 t_name VARCHAR (100) := UPPER ('test');
10 table_space VARCHAR (100) := 'users';
11 col VARCHAR (50);
12 ix_name VARCHAR (50);
13 l_str VARCHAR2 (1000);
14 BEGIN
15 FOR j IN 1 .. cols.COUNT
16 LOOP
17 col := cols (j);
18 ix_name := col || '123_ix';
19
20 l_str :=
21 'create index '
22 || ix_name
23 || ' ON '
24 || t_name
25 || '('
26 || col
27 || ') local unusable';
28
29 DBMS_OUTPUT.put_line (l_str);
30
31 FOR i IN (SELECT partition_name
32 FROM user_tab_partitions
33 WHERE table_name = t_name)
34 LOOP
35 l_str :=
36 'alter index '
37 || ix_name
38 || ' rebuild partition '
39 || i.partition_name
40 || ' tablespace '
41 || table_space
42 || ' parallel(degree 2)';
43 DBMS_OUTPUT.put_line (l_str);
44 END LOOP;
45 END LOOP;
46 END;
47 /
which results in
create index time_stamp123_ix ON TEST(time_stamp) local unusable
create index trx_date123_ix ON TEST(trx_date) local unusable
create index TRX_DATE-CUSTOMER_ID123_ix ON TEST(TRX_DATE-CUSTOMER_ID) local unusable
create index trx_time123_ix ON TEST(trx_time) local unusable
PL/SQL procedure successfully completed.
SQL>
See anything suspicious? I do, a minus sign in index name. I'd substitute it with an underline.
Also, CONCAT accepts only two parameters - use double pipe instead (just like I did).