Error report - ORA-00969: missing ON keyword While creating Index-CodePudding

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.

CodePudding user response：

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).