Create Directory same path as other directory

 

Begin

 For Cur_Rec In (select directory_path from DBA_DIRECTORIES where DIRECTORY_NAME='LDOUTPUT') Loop

    Begin

      Execute Immediate 'create or replace directory XMLDIR as '''|| Cur_Rec.directory_path ||'''';

      Execute Immediate 'create or replace directory DOCUMENT as '''|| Cur_Rec.directory_path ||'''';

    End;

  End Loop;

End;

/

When create index | ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "WMSYS.NO_VM_CREATE_PROC", line 147

 SYMPTOMS

When attempting to creating a unique index, the following error occurs.

Create Index BillVoucher on Faleddrcr(Firmnumber,nFinancialyear,Exchange,Booktype,Vallan,Special,Entrycode) tablespace Indx storage (initial 60M Next 30M ); 

Create Index BilldescVoucher on Faledgerdescription(Firmnumber,nFinancialyear,Exchange,Booktype,Vallan,Special,Entrycode) tablespace Indx storage (initial 60M Next 30M ); 

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "WMSYS.NO_VM_CREATE_PROC", line 147

ORA-06512: at line 26

CAUSE

The cause of this problem has been identified in Bug 11061801.

SOLUTION

download Patch 11061801 to resolve this issue or use below workaround.

Workaround:

To workaround the problem change the Index DDL and add a <space> between the table name "Faleddrcr" and the open parenthesis prior to the column list.

after beautify issue got resolved.

CREATE INDEX BillVoucher ON Faleddrcr

  (

    Firmnumber,

    nFinancialyear,

    Exchange,

    Booktype,

    Vallan,

    Special,

    Entrycode

  )

  TABLESPACE Indx STORAGE

  (

    INITIAL 60M NEXT 30M

  );

CREATE INDEX BilldescVoucher ON Faledgerdescription

  (

    Firmnumber,

    nFinancialyear,

    Exchange,

    Booktype,

    Vallan,

    Special,

    Entrycode

  )

  TABLESPACE Indx STORAGE

  (

    INITIAL 60M NEXT 30M

  );