1

I want to extract DDL of XMLINDEX but when I run the below code I got incorrect or incomplete code.

execute dbms_output.put_line(DBMS_METADATA.GET_DDL('INDEX','IDX_XML_AD','SCHEMAXX'));

This is the output coming from the above command:

 CREATE INDEX "IDX_XML_AD" ON "AD_TABLE" ("AD_COL1") 
   INDEXTYPE IS "XDB"."XMLINDEX"

I want output as below:

CREATE INDEX IDX_XML_AD ON AD_TABLE ( "AD_COL1" )
  INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS 
  (
  q'
  [

          PATH TABLE "X_AD_PATHTAB_FULLDATA"     

          GROUP GRP_1  
            XMLTABLE X_AD_1 '/ABC' 
                columns 
                    "COL1"        VARCHAR2(30)   PATH 'col1', 
                    "COL2" VARCHAR2(30)   PATH 'col2'   

          GROUP GRP_2 
            XMLTABLE X_AD_2 '/ABC/def/ghi/COL3'  
                columns 
                    "COL3" VARCHAR2(30)   PATH '/'   
  ]'
  );

I tried DBMS_METADATA package. Also I tried SQL Developer export tool. But not getting expected code in output.

swet
  • 207
  • 4
  • 15
  • 1
    Have you done `set long 32767`, and possibly `set longchunk 32767` too, from SQL\*Plus or SQL Developer (running your query as a statement or script)? What is wrong with the version from the SQL Developer export tool? – Alex Poole Mar 25 '19 at 09:30
  • I did check with `set long 32767` and `set longchunk 32767` in sql developer. Nothing wrong with SQL developer version or export tool. I think DBMS_METADATA is incapable of extracting XMLINDEX DDL. If yes then how to do it? – swet Mar 25 '19 at 10:07
  • I'm not sure what you mean, you seem to be saying it does work and that it doesn't work at the same time. I just tried creating a dummy table and your index, then retrieving it with `dbms_metadata.get_ddl`, and it was fine (SQL Developer 18.4, Oracle 11gR2, as a simple query or via `execute`). The only difference I can see is that it comes back with escaped single quotes rather than the `q'[...]'` alternate syntax. You can see the same thing in [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=c22a76438d2c5791befbd6cf6f8fbc5a). – Alex Poole Mar 25 '19 at 10:11
  • what you have given in the attachment, I tried and got the result as expected, thank. But the actual `XMLINDEX` is >1000 chars in length and I think this is the problem because of which sqlplus/sql developer are not printing the whole code. Can you find some way to print `XMLINDEX` code which has `PARAMETERS` >1000 chars? – swet Mar 25 '19 at 10:34
  • How are you creating it in the first place - if I try I get `ORA-29896: Length of PARAMETER string longer than 1000 characters`. Which version of Oracle are you using? ([It fails in 18c too...](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9ccab924c89c0a83008d1c53be1d0de7)) – Alex Poole Mar 25 '19 at 10:38
  • 11g R2, I am creating the `PARAMETERS` using PLSQL block which calls DBMS_XMLINDEX.REGISTERPARAMETER and then I create the INDEX. – swet Mar 25 '19 at 10:44
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190615/discussion-between-swet-and-alex-poole). – swet Mar 25 '19 at 10:48
  • Then please create [an MCVE](https://stackoverflow.com/help/mcve). – Alex Poole Mar 25 '19 at 10:48
  • I just created `XMLINDEX` with 'PARAMETERS' >1000 char and `DBMS_METADATA` is not able to provide full DDL. Do you want the code (how to create long XMLINDEX)? I cannot do chat now, I can after 5-6 hours. – swet Mar 25 '19 at 10:49
  • Exactly, so we can see what you're doing and code that causes the error, rather than something that works. The body of your question doesn't really explain the situation at the moment. (I suspect you might be stuck though, I can't see any way to modify the `dbms_metadata` behaviour for this. Does `expdp` error too?) – Alex Poole Mar 25 '19 at 10:56

0 Answers0