How to create index with concatenating two columns in Procedure/Function using pl/sql

Question

I want to create index of concatenating two columns, using this query:

   declare 
   t_name varchar(100) := upper('test');
   table_space varchar(100) := 'users';
   begin
   execute immediate 
           'create index ' || upper('trx_cst_indx') || ' ON ' || t_name || '(' || trx_date||'_'||customer_id || ') local unusable';
   end;

Running that query gives me this error:

 PLS-00201: identifier 'TRX_DATE' must be declared
 ORA-06550: line 5, column 16:
 PL/SQL: Statement ignored
 06550. 00000 -  "line %s, column %s:\n%s"
 *Cause:    Usually a PL/SQL compilation error.

Then I use single quotation for the column names like this:

 declare 
 t_name varchar(100) := upper('test_etl');
 table_space varchar(100) := 'users';
 begin
 execute immediate 
           'create index ' || upper('trx_cst_indx') || ' ON ' || t_name || '(' || 'trx_date'||'_'||'customer_id' || ') local unusable';
 end; 

While running the above query, I receive this error:

 Error report - ORA-00904: "TRX_DATE_CUSTOMER_ID": invalid identifier
 ORA-06512: at line 5
 00904. 00000 -  "%s: invalid identifier"
 *Cause:   

I don't know what is wrong with these queries. Would you please guide me how to create index with concatenating two columns in this mode?

Any help is really appreciated.

Answer 1

Try this:

'create index trx_cst_indx ON '| | t_name || ' (trx_date,customer_id) local unusable';